With PHP to MySQL datetime formatting

Question

I am trying to convert $dt = "Tue May 15 00:09:06 UTC+0100 2012" into a datetime field of MySQL database server, but the entered value is always 6 hours behind.

My server is in Dallas, US and I am in UK.

Any ideas how to resolve this?

Answer 1

use

 date_default_timezone_set("America/New_York");

replacing "America/New_York" to the code for London to shift your servers timezone to the proper GMT timezone. You can look up the proper code on php.net if you look for timezone stuff.

Just place it at the top of each page that you use dates and then use php DateTime objects with the ->format('c') function.

You can also set timezone information in your php.ini file if you have access to it.

-Jordan

Answer 2

Without more information (some code, for example), it's difficult to debug, but given the variable you did provide, this snippet may work:

<?php
$dt_for_db = gmdate("Y-m-d H:i:s", strtotime($dt));

Then, every time you pull from the database, be sure to append GMT (there's a space at the beginning) to every date.

<?php
$dt_from_db = $dt_from_db.' GMT';

Basically, dates in MySQL datetime columns do not store timezone information, so you have to be sure to always know what timezone you're using (UTC/GMT). Then, append that timezone to the end of each date you pull from the database before using any date functions on it (as date functions assume local time for dates without a timezone specified).