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
.uuuuuupart 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
SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04';SELECT AVG(value) FROM numerics WHERE timestamp BETWEEN '2007-08-23 19:48:09-04' AND '2007-08-23 19:48:09-04';
SELECT AVG(value) FROM numerics WHERE timestamp > '2007-08-23 19:48:09-04'::timestamp;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)
