Hi everyone, During the Monthly Meet - Aug 8-2015 - I raised an open question regarding leap seconds and how are they handled in DB's. Everyone had our opinions and most of the attendees suggested it is taken care by the OS.
Interestingly looks like Leap second is handled by DB as well because of the usage of *now()* function. Found this link interesting https://dev.mysql.com/doc/refman/5.5/en/time-zone-leap-seconds.html. In recent versions of MySQL leap second 59:60 is reverted to 59:59 and because of this decrement there can be ambiguities in our where queries. For accurate query results MySQL suggests using *UNIX_TIMESTAMP(timestamp_column).* Eg: => mysql> SELECT a, ts, UNIX_TIMESTAMP(ts) FROM t1; +------+---------------------+--------------------+ | a | ts | UNIX_TIMESTAMP(ts) | +------+---------------------+--------------------+ | | 1 | 2008-12-31 23:59:59 |1230767999 | | 2 | 2008-12-31 23:59:59 |1230768000 | +------+---------------------+--------------------+ 2 rows in set (0.00 sec) Querying for the leap second - => SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:60';* Empty set, 2 warnings (0.00 sec) Returns empty set, because it is stored as 23:59:59 as shown in first query. Using UNIX_TIMESTAMP in the condition we get - => mysql> *SELECT * FROM t1 WHERE UNIX_TIMESTAMP(ts) = 1230768000; +------+---------------------+ | a | ts | +------+---------------------+ | 2 | 2008-12-31 23:59:59 | +------+---------------------+ 1 row in set (0.00 sec) Interesting part is that for 59:59 we get only one result and not the expected two rows. => mysql> *SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:59';* +------+---------------------+ | a | ts | +------+---------------------+ | 1 | 2008-12-31 23:59:59 | +------+---------------------+ 1 row in set (0.00 sec) I Took this Leap Second example from MySQL doc and when I tried it on my machine, this leap second behaviour was not working - very recently someone have commented the same in the doc. Please let me know if the example actually works for anyone else ? Also http://www.postgresql.org/docs/9.1/static/functions-datetime.html says "PostgreSQL uses UT1 because leap seconds are not handled." Interested to know if there are any such MySQL like decrements for leap seconds in PostgreSQL too. Thanks, Bala. _______________________________________________ ILUGC Mailing List: http://www.ae.iitm.ac.in/mailman/listinfo/ilugc ILUGC Mailing List Guidelines: http://ilugc.in/mailinglist-guidelines