PHP gmdate() equivalent function in MySQL

[kevin7]

Hi all, I have been messing up with timezone lately. I solved most of the problems in PHP side, but I have a small issue with mySQL.

 

I store created date in timestamp format using this php function:

 

$date = explode('-', '11-01-2010');
$start_date = gmmktime(0, 0, 0, $date[1], $date[0], $date[2]);

//then i store it into a column called start_time, varchar 255

 

and then, I retrieve it using this mySQL command:

 

select * from event where active=1 and date_format(from_unixtime(start_time), "%d-%m-%Y")='11-01-2010';

 

but apparently, mySQL convert the date to 12-01-2010, not 11-01-2010.. it's the timezone problem. Normally, I would use for-loop, loop through the array and convert the timestamp stored in database using gmdate() function with php. But in this case, I have to do it with mySQL command. Does mySQL have gmdate() equivalent function?

 

Many Thanks!

[PFMaBiSmAd]

Use this - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_convert-tz between the from_unixtime() and the date_format() functions.

 

You could also set the mysql timezone to GMT using a query at that start of your code -

SET time_zone = timezone;