Storing microseconds in MySQL: which workaround?

mysql performance datetime time-precision
dpq picture dpq · Feb 13, 2010 · Viewed 11.6k times · Source

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:

  • Using a decimal() column type, with integer part corresponding to seconds since some point in time (I doubt that UNIX epoch will do, since we have to store measurements taken in 60's and 50's).
  • Using two integer columns, one for seconds, the other one for microseconds.

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)?

Answer

Adriaan Stander picture Adriaan Stander · Feb 13, 2010

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

Related questions

MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More
MySQL vs MongoDB 1000 reads

I have been very excited about MongoDb and have been testing it lately. I had a table called posts in MySQL with about 20 million records indexed only on a field called 'id'. I wanted to compare speed with MongoDB and …

Read More