having a problem with UNIX_TIMESTAMP function in mysql.
The setup:
I am doing a wordpress site on my local windows machine and upload my updates to an ubuntu server(page.ly) periodically. There is a piece of code that retrieves a datetime column from a mysql db. The problem is the time retrieved is right on my local windows machine, but wrong on the ubuntu server.
What i know so far
I ran several queries on both places to try to reproduce the problem.
$wpdb->get_row("SELECT UNIX_TIMESTAMP('2012-07-27 22:15:00')
FROM {$wpdb->posts}"))
this produces the right time my local machine(1343427300), but wrong on ubuntu(1343445300 5 hours ahead. my local timezone America/New_York.).
$wpdb->get_row("SELECT UNIX_TIMESTAMP('2012-07-27 22:15:00')"))
this produces the right time on both machines. it’s like as soon as i associate my select with a table, any table in the database, the time gets the timezone offset + daylight savings offset. i checked the timezone offset on both machines. both of them are 0. im kinda stuck here. appreciate any help.
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
UNIX_TIMESTAMP returns the UTC time, so that explains the 5 hours difference you are getting
You might want to use CONVERT_TZ on the time with a offset of -05.00 (Eastern) to get the right TIMESTAMP.