April 3, 2012

Date between MySQL and PHP

Question by Chris Hardaker

I have a table with a field of type date within a MySQL database. My user places a date into a field (format dd-mm-yyyy) which I convert into yyyy-mm-dd for insertion into the database. This works fine. I can see the date in there as (for example) 2012-04-04.

My issue is that I then select this record, convert the date to the format I wish to display (dd-mm-yyyy) and get 03-04-2012. I understand why, in that my database is set to UTC, however the user is on Berlin time, therefore 04-04-2012 00:00 in Berlin is 03-04-2012 23:00 UTC.

The issue means that if I then save the displayed date (03-04-2012), the next time I see it, it displays as 02-04-2012 because I saved only the date and therefore the system is assuming a 00:00 time again.

I cannot see a way around this other than setting this as a datetime type rather than a date type, however I would rather not do that as time (for various reasons) is stored in a separate field. Any suggestions?

Answer by h4cky

When you inserting a record you add as datetime current UTC time, after that every user in their profile may want to/or set his timezone.

If you know the timezone of the user u can easy convert the datetime to user locale time. Because you know the differences in hours/minutes between the time.

P.S. You can store the datetime as varchar and save the unix timestamp in this field. Unix timestamp is based on current timezone I think.

UPDATE:
I think that might help

$date = time();
dump(date('d-m-Y H:i:s', $date)); // 03-04-2012 08:43:38

date_default_timezone_set('Europe/London');
dump('London: '. date('d-m-Y H:i:s', $date)); // London: 03-04-2012 11:43:38

date_default_timezone_set('Europe/Berlin');
dump('Berlin: '. date('d-m-Y H:i:s', $date)); // Berlin: 03-04-2012 12:43:38

date_default_timezone_set('Europe/Sofia');
dump('Sofia: '. date('d-m-Y H:i:s', $date)); // Sofia: 03-04-2012 13:43:38

dump function returns '<pre>'. $something .'</pre>';

Answer by Starx

First, make sure both time zones are same. Then, don’t store in datatime format, use integer. Convert the date to timestamps and then store. Like

$time = time(); //get the current time stamp
//Now insert $time

Now, both places are in common ground, You may do as you like. Changing date among different timezone is rather easy.

echo gmdate("M d Y H:i:s", $time);

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!