Search code examples
mysqldatetimedatesqliteunix-timestamp

Storing dates and datetimes using the unix epoch

I need a universal way to store dates and datetimes in several databases (for now Sqlite, MySql and PostgreSql). I need datetimes to have microseconds precision.

  • Sqlite does not have date and datimes built in.
  • MySql datetime does not have microseconds precision.

So i thought to keep dates as integers (4 bytes) - days since the unix epoch, and datetimes as integers (8 bytes) - microseconds since the unix epoch.

Questions:

  1. What's the right way to convert a date to days since unix epoch? I found this: time.time()/86400 (python).
  2. Is it robust to keep datetimes as timestamps? What about the leap seconds - will this influence the accuracy? What if at some point in time a future date was stored as timestamp but later a leap second appeared?
  3. Any other issues?
Solution

    1. time() / 86400 is fine. I've done this before; you should not anticipate any problems. You could also store time() truncated down to the nearest multiple of 86400, which might be a little nicer because you can pass it to various functions that already accept a time_t and have it come out as midnight UTC on the date in question.

    2. Don't worry about leap seconds. Only specialized software takes them into account. The system date&time on all general purpose operating systems, and all general purpose date&time libraries pretend they don't exist. Your NTP server implements them by just fudging the system clock ahead by one second whenever they happen.

    3. C's gettimeofday returns the time as two separate 4-byte integer values: seconds since the epoch, and microseconds since the start of the second (struct timeval). Similarily, C's clock_gettime returns the time as two separate 4-byte integer values: seconds since the epoch, and nanoseconds since the start of the second (struct timespec). If you care about compatibility with existing representations of time, you might choose one of those two formats instead of an 8-byte integer counting microseconds since the epoch. On the other hand both of those have the y2.038k bug (unless the first integer is extended to 8 bytes, for a total of 12 bytes) and they are less convenient to use in a database. So I think your idea of microseconds since the epoch is just fine.