graulund/tweetnest — pull request #36 by TheConstructor (2012-05-06 23:58:50+00:00)
Title: Correct DB_OFFSET-Calculation

Hi,

with me DB_OFFSET-calculation tend to be off arround 3 days, when using Tweetnest at night, because MySQL calculated the difference wrong.
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_now tells you, that YYYYMMDDHHMMSS.uuuuuu format will be used in "numerical context" which results in this:

NOW(): 2012-05-07 01:46:57
UTC_TIMESTAMP(): 2012-05-06 23:46:57
NOW()+0: 20120507014657.000000
UTC_TIMESTAMP()+0: 20120506234657.000000
NOW() - UTC_TIMESTAMP(): 780000.000000

which really is a senseless number as the day/hour boarder gets crossed. `TIME_FORMAT(NOW() - UTC_TIMESTAMP(), '%H%i')` returns 7800.

Correct query is `TIMESTAMPDIFF(SECOND, UTC_TIMESTAMP(), NOW())` which returns the offset in seconds just as `date('Z')`.

Accepting this pull-request should solve the problem once and for all.

Regards

TC

Note: the described error only happens when the dates of the timestamps don't match. If both times are on the same date you could calculate the seconds from there.