we're writing a scientific tool with MySQL support. The problem is, we need microsecond precision for our datetime fields, which MySQL doesn't currently support. I see at least two workarounds here:
The most popular query is selecting columns corresponding to a time interval (i.e. dt_record > time1 and dt_record < time2).
Which one of these methods (or perhaps another one) is likely to provide better performance in the case of large tables (millions of rows)?
Learn MySQL from scratch for Data Science and Analytics But now MySQL supports millisecond/ microsecond precision with timestamp, datetime, and time. The official statement. “MySQL now supports fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microsecond precision”.
If you want a Unix timestamp, like the first integer value you mentioned as an example, you can use UNIX_TIMESTAMP() . If you want the number of seconds of just today, starting with 0 at 12:00am, you could store (HOUR(CURTIME())*60*60)+(MINUTE(CURTIME())*60)+SECOND(CURTIME()) .
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME, which is stored “as is”.) By default, the current time zone for each connection is the server's time.
MySQL permits fractional seconds for TIME , DATETIME , and TIMESTAMP values, with up to microseconds (6 digits) precision.
If you say that the most popular queries are time base, I would recomend going with a single column that stores the time as in your first option.
You could pick your own epoch for the application, and work from there.
This should simplify the queries that needs to be written when searching for the time intervals.
Also have a look at 10.3.1. The DATETIME, DATE, and TIMESTAMP Types
However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded. Conversion of TIME or DATETIME values to numeric form (for example, by adding +0) results in a double value with a microseconds part of .000000
MySQL will support microseconds, see MySQL 5.6.4 changelog:
Fractional Seconds Handling
Incompatible Change: MySQL now permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:
CREATE TABLE t1 (t TIME(3), dt DATETIME(6)); The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)
The following items summarize the implications of this change. See also Section 10.3.5, “Fractional Seconds in Time Values”.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With