Page 1 of 1

date time displayed are minus one day

Posted: Sun May 17, 2009 1:26 am
by daiyan
when i insert some info into my mysql using a form on my website the date and time that are auto inserted into my database are wrong. the date is minus one day and the time is also many hours behind.
iam using a form to get the info and a simlpe php script does the inserting into my database.
can any one point me in the right direction thanks.

Re: date time displayed are minus one day

Posted: Sun May 17, 2009 1:50 am
by John Cartwright
Your server likely is in a different timezone from where you reside.

Take a look at http://dev.mysql.com/doc/refman/5.1/en/ ... pport.html to set the timezone for mysql

Re: date time displayed are minus one day

Posted: Tue May 19, 2009 8:07 am
by daiyan
ok well i figured it out i need either to pay for my hosting so i can change server time zone or use a free host based in the uk. thank for yr help tho

Re: date time displayed are minus one day

Posted: Fri Aug 10, 2012 2:04 pm
by tr0gd0rr
The ideal is to store all dates in the database as UTC! In your a PHP include file, do `date_default_timezone_set('UTC');` and it won't matter what time zone your database is in. Then when you do `$now = date('Y-m-d H:i:s');`, for example, the date will be in UTC. When you get a date input from the user, convert it to UTC before saving it to the database. Whenever you output a date to the user, convert it from UTC to their timezone before you display it.

That gives you some big advantages:
  1. It doesn't matter the timezone of your server or database.
  2. You can move to another server anytime without converting dates.
  3. You don't have do anything special to handle daylight savings time changes.
  4. You can show users dates according to their own time zones.
Most apps don't bother to handle timezones this way, but for large apps I don't know how you can live without it.
As far as getting the user's time zone, you can use JavaScript `Date#getTimezoneOffset()` and cookies or you can make time zone a configuration option in the user's profile.

Re: date time displayed are minus one day

Posted: Fri Aug 10, 2012 9:17 pm
by califdon
@tr0gd0rr: Excellent advice and explanation!

Re: date time displayed are minus one day

Posted: Mon Aug 13, 2012 1:11 pm
by tr0gd0rr
Also note that there are not 24 time zones, there are over 400! When I say time zone, I mean "America/Denver" or "Europe/Oslo". This is because different countries and cities have different start and end date for daylight savings changes. If you store a user's time zone as -04:00, for example, your app can't reflect changes from daylight savings.

So your date conversion library (e.g. DateTime) needs to accept string values like "America/New_York". Then when a city decides to change their daylight savings dates (like the U.S. did a few years ago), updates to the operating system enable PHP to pick up the change.