Some Notes About Time

True story: when I was at ITA Software, Orbitz ran hundreds of instances of QPX, our low fare search software, on their own servers. We had an ops fire drill one weekend because customers were complaining that the site was showing incorrect prices. The root cause? A single machine in their server farm had the time wrong, so advance purchase was computed incorrectly for any query sent to that particular machine. That was fun to debug.

Even the meaning of, say, a minium stay requirement is hard to precisely define. If you fly across the international date line and back on a Friday night, did you have a Saturday stay or not? There are so many flights going so many places, questions like this actually come up in practice.

Don't do this in your database. Use your datetime types. Please. You might save some work with your timezone but you're not going to be able to use intervals etc in a smart way.

This would be even dumber with PostgreSQL, which has much more robust date/time functionality (being able to use the - operator with datetimes and intervals, for example) and completely sane timezone support.

If your software has business logic that cares about, say, what day a specific person perceived an event as happening, it needs to think about the timezone they have set.

So, in such cases, you'd better keep a history of all the timezones they've ever chosen, and the time (UTC) they changed them.

(Hopefully this lets someone else avoid my past mistakes.)

This post advises to use Unix timestamps. That's already what I'm doing mostly everywhere. And it seems to me like most people do and like this is somewhat accepted as the best available option - but still not perfect.

Then I am wondering again: Isn't there really a perfect solution? Esp., the problems with Unix timestamps are the non-monotony and that e.g. two equal timestamps can actually represent two different times (seconds). This could be bad in cases where seconds matter (e.g. some log-files which need to be very precise about time).

Maybe TAI is and we all should store TAI timestamps instead of Unix timestamps? But is there an easy way to get TAI timestamps? Also, I haven't really seen other projects doing this - why? Maybe it is just too little gain over Unix timestamps and too less tools available to work with them... Btw., I just checked, there is http://cr.yp.to/libtai.html and http://pypi.python.org/pypi/tai64n, maybe I should just start using that.

This of course still doesn't solve anything about synchronization or inaccurate system clocks but it would be better than Unix times.

Edit: Getting the TAI timestamp is probably not easy or maybe even not possible... I just saw this: https://github.com/stoni/libtai/blob/master/tai_now.c ...

The only true problem with storing unix time is leap seconds and relativistic effects, which can both be safely ignored for most time keeping usages.