So far, it\’s RoR

Ruby on Rails, PostgreSQL, SVN, etc….

Timestamp support for Rails and MySQL/PostgreSQL/Oracle

Posted by Chirag Patel on August 13, 2007

Troel has written a great summary of the SQL standard for the timestamp data type along with its implementation in the big 5 databases (MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and DB2).

MySQL

It turns out that MySQL does not support sub-second time or date formats. There is a MICROSECOND function in MySQL allowed to run on temporal values, but sub-second precision (including millisecond) is not stored in the database.

A microseconds part is allowable in temporal values in some contexts, such as in literal values, and in the arguments to or return values from some temporal functions. Microseconds are specified as a trailing .uuuuuu part in the value. …. However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.

There is also a May 26, 2007 post on Kevin Burton’s blog in which a MySQL developer appears to confirm that MySQL will not store milliseconds (which obviously means microseconds can’t be stored either). It’s odd considering that MySQL’s DATETIME data type is 8 bytes and milliseconds can’t be stored. Interestingly, PostgreSQL’s timestamp datatype is also 8 bytes and can support microsecond resolution.

Here’s the mapping of the Rails data types (the symbols used in Rails migrations) to MySQL data types. This mapping can be found in mysql_adapter.rb in Rail’s ActiveRecord:

      def native_database_types #:nodoc:
        {
          :primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
          :string      => { :name => "varchar", :limit => 255 },
          :text        => { :name => "text" },
          :integer     => { :name => "int", :limit => 11 },
          :float       => { :name => "float" },
          :decimal     => { :name => "decimal" },
          :datetime    => { :name => "datetime" },
          :timestamp   => { :name => "datetime" },
          :time        => { :name => "time" },
          :date        => { :name => "date" },
          :binary      => { :name => "blob" },
          :boolean     => { :name => "tinyint", :limit => 1 }
        }

      end

A potential solution to the MySQL sub-second limitation is to use force Rails to use the bigint type in MySQL (along with creating your own custom timestamp format to fit in the bigint type). This is an alternative to modifying mysql_adapter.rb in Rail’s ActiveRecord, which could pose problems later on. There is a plug-in MySQL Bigint Rails Plugin that supports multiple integer types. Not only is bigint supported, it also allows tinyint and mediumint. After installing, it magically transforms the :limit parameter so that it specifies the number of bytes for the column stored instead of specifying the maximum value expected for that column. For example, the following statement will be mapped to MySQL’s mediumint instead of a regular int.

    t.column "col2", :integer, :limit => 3, :null=> false

Run 'DESCRIBE table;' in the MySQL command line to verify that it worked.

PostgreSQL

PostgreSQL, on the other hand, has 1 microsecond resolution on all of its time and timestamp types. Here’s the mapping in of Rails data types to PostgreSQL data types (line 92 of postgresql_adapter.rb in Rail’s ActiveRecord)

      def native_database_types
        {
          :primary_key => "serial primary key",
          :string      => { :name => "character varying", :limit => 255 },
          :text        => { :name => "text" },
          :integer     => { :name => "integer" },
          :float       => { :name => "float" },
          :decimal     => { :name => "decimal" },
          :datetime    => { :name => "timestamp" },
          :timestamp   => { :name => "timestamp" },
          :time        => { :name => "time" },
          :date        => { :name => "date" },
          :binary      => { :name => "bytea" },
          :boolean     => { :name => "boolean" }
        }
      end


In PostgreSQL, the timestamp data type is referred to as the "timestamp without time zone". It will use this by default, unless you explicitly specify the timestamp with time zone. In either case, the timestamp is accepted and queried in string format: (e.g. '2003-07-29 13:19:30.532').

Also, PostgreSQL’s timestamp data type accepts a parameter p indicating the the number of fractional digits retained in the seconds field. To my understanding, p does not have any influence on the total bytes stored, which is always 8. Internally, it’s an 8-byte int or an 8-byte float depending on whether or not you compiled with enable-integer-datetimes. The default is 8-byte float.

If server was compiled with enable-integer-datetimes, PostgreSQL stores the timestamp as the total number of microseconds from midnight 2000-01-01 (in an int8). If not, it’s stored (in a float8) and the precision degrades as the date moves away from midnight 2000-01-01.

To make it all work, I added a :timestamp_with_time_zone data type by overriding PostgreSQL adapter class found in postgresql_adapter.rb

To override this class, include the preceding in a file such as lib\postgre_extensions.rb and add “require ‘postgre_extensions’” to the top of each migration file. Or you can add require ‘postgre_extensions’ to the bottom of config\environment.rb

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
  def native_database_types
    {
      :primary_key => "serial primary key",
      :string      => { :name => "character varying", :limit => 255 },
      :text        => { :name => "text" },
      :integer     => { :name => "integer" },
      :float       => { :name => "float" },
      :decimal     => { :name => "decimal" },
      :datetime    => { :name => "timestamp" },
      :timestamp   => { :name => "timestamp" },
      :timestamp_with_time_zone => { :name => "timestamp with time zone" },
      :time        => { :name => "time" },
      :date        => { :name => "date" },
      :binary      => { :name => "bytea" },
      :boolean     => { :name => "boolean" }
    }
  end
end
  

While I was at it, I tried to create a smallint type in PostgreSQL with the following line of Migrations code and the MySQL Bigint Rails Plugin (described above):

t.column :heartrate, :integer, :limit => 1, :null=> false

The author claims that it should work in other databases outside of MySQL and it did for this example! In this particular case, a smallint (2 bytes) was used instead of an integer (4 bytes). The tinyint data type (1 byte) is not available in PostgreSQL like it is in MySQL unfortunately.

Oracle

Oracle, too, appears to supports millisecond precision, but the Rails mapping goes to DATE instead of TIMESTAMP as Paul Greer pointed out in ActiveRecord’s oracle.rb:

        def native_database_types #:nodoc:
          {
            :primary_key => "NUMBER(38) NOT NULL PRIMARY KEY",
            :string      => { :name => "VARCHAR2", :limit => 255 },
            :text        => { :name => "CLOB" },
            :integer     => { :name => "NUMBER", :limit => 38 },
            :float       => { :name => "NUMBER" },
            :decimal     => { :name => "DECIMAL" },
            :datetime    => { :name => "DATE" },
            :timestamp   => { :name => "DATE" },
            :time        => { :name => "DATE" },
            :date        => { :name => "DATE" },
            :binary      => { :name => "BLOB" },
            :boolean     => { :name => "NUMBER", :limit => 1 }
          }
        end

Timestamp SQL queries

Use single quotes '' for timestamp! He are are some examples

  1. SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04';
  2. SELECT AVG(value) FROM numerics WHERE timestamp BETWEEN '2007-08-23 19:48:09-04' AND '2007-08-23 19:48:09-04';
  3. SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04'::timestamp;
  4. EXPLAIN SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04';
    Traditional practice for EXPLAIN is to measure the costs in units of disk page fetches;

P. S. It’s a bad idea to title a column ‘timestamp’, because this is a datatype-identifier – but it works.

Useful Rails timestamp-related code

# First and last records from a model ordered by a specified column (e.g. timestamp)
ending = Users.find(:first, :order => 'timestamp DESC')
beginning = Users.find(:first, :order => 'timestamp ASC')

#resolve Ruby variable name (e.g. start_time) in string using the #{...} string interpolation mechanism
average = User.average(:age, :conditions => "timestamp > #{start_time} AND timestamp < #{end_time}")

#converts string to time object
Time.parse(self)

 

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: