Jump to content


PHP gmdate() equivalent function in MySQL

  • Please log in to reply
1 reply to this topic

#1 kevin7

  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 06 January 2010 - 12:40 AM

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!

#2 PFMaBiSmAd

  • Staff Alumni
  • Advanced Member
  • 16,767 posts
  • LocationColorado, U.S.A.

Posted 06 January 2010 - 02:29 AM

Use this - http://dev.mysql.com...tion_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;

Signature: (not a comment about anything you posted unless specifically indicated)
Debugging step #1: To get past the garbage-out equals garbage-in stage in your code, you must check that the inputs to your code are what you expect.

Programming is just problem solving, but it is done in another language. You must learn enough of the programming language you are using to be able to read and write code.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users