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.