Comparison/conversion of unix timestamps and literal time valuess affected by MySQL leap seconds

==Steps to reproduce==

1. Create an article with a posted date of 2010-12-15 11:24:45
2. The contents of the Posted column is 2010-12-15 11:24:21, a timestamp vlue 
of 1292408685.
3. Execute SQL: select ID, Posted, unix_timestamp(Posted) from textpattern 
where unix_timestamp(Posted) = 1292408685;

==Expected output==

Posted = '2010-12-15 11:24:45' as entered in step 1

==Actual output==

Posted = '2010-12-15 11:24:21', i.e. 24 seconds off

==Reason==

MySQL 5.1.31+ accounts for leap seconds on capable O/Ss: 
http://dev.mysql.com/doc/refman/5.1/en/time-zone-leap-seconds.html. A diret 
comparison or conversion between timestamps and time literals may lead to 
unexpected effects.

==Impact==

We convert from time literals to timestamps and vice versa on a whole lot of 
places. e.g. as we save an article.

We compare with results from MysQLs 'now()', 'unix_timestamp()', timestamps and 
DATETIME columns at various locations (getNeighbour() 
[http://code.google.com/p/textpattern/source/browse/development/4.x/textpattern/
publish.php?r=3470#1012], list_list() 
[http://code.google.com/p/textpattern/source/browse/development/4.x/textpattern/
include/txp_list.php?r=3470#292] et cetera). 

I assume most of these are off  by some seconds.

==Additional reading==

MySQL forum post: http://forums.mysql.com/read.php?10,388911,388911

Please use labels and text to provide additional information.

Original issue reported on code.google.com by r.wetzlmayr on 23 Dec 2010 at 10:16

kaffa / textpattern

Comparison/conversion of unix timestamps and literal time valuess affected by MySQL leap seconds #96