0

Since daylight savings happened a little over a week ago my database has been messed up and everything is still ahead 1 hour so i'm trying to figure out best way to resolve this and prevent this issue moving forward.

Right now its 11/8/2017 at 2:16 PM EST and when I submit something using datetime=NOW() the output comes out as 11/8/2017 at 3:16 PM EST

Before daylight savings hit, of course it was working fine, however now its off an hour.

I've tried using

date_default_timezone_set('America/Chicago');

and then also tried date_default_timezone_set('UTC'); but both end up displaying the exact same date/time with the output using: 

echo date( 'm/d/y g:i A', strtotime($row['ticket_assigned_date']));

I saw other methods like using CONVERT_TZ('2010-01-01 12:00','+00:00','-07:00') however this i really want to avoid because it seems terrible to use over a ton of select statements, also I have to make it so my site is full US friendly, so them setting a timezone in their settings would be best and having it set in PHP would be a way better option if possible.

Also moving forward I want to make sure that if the time saved is really at 2:16 PM EST and then daylight savings hit that the stored value will of course still return 2:16 PM EST in the future no matter what.

eqiz
  • 1,521
  • 5
  • 29
  • 51
  • I'd recommend `date_default_timezone_set`. Remember that you have to call it **before** inserting the time into the database. Also don't forget that you'll need to call it again when outputting **from** the database, which may be what is tripping you up! You can also change the `date.timezone` variable in `php.ini` as an alternative. – Obsidian Age Nov 08 '17 at 19:20
  • I thought `date_default_timezone_set` is a PHP only function and doesn't affect the use of `INSERT datetime=NOW()` when doing MySQL insert? – eqiz Nov 08 '17 at 19:26
  • Correct, but you could insert something like PHP's `date("Y-m-d H:i:s")`, which would give you the same effect, and work with `date_default_timezone_set` :) – Obsidian Age Nov 08 '17 at 19:28
  • And you can convert timestamp to timezone date https://stackoverflow.com/questions/4186868/convert-timestamp-to-timezones – Buce Nov 08 '17 at 20:18

2 Answers2

1

There is a better way in php:

You can use time() function and insert time as bigint timestamp value always UTC (value in seconds)!

$tm = time();
echo date('Y-m-d H:i:s', $tm);

And simple if you need select time < current time:

SELECT from data WHERE time < $tm;

Last day (time is second always):

$tm = time() - 60 * 60 * 24;
SELECT from data WHERE time > $tm;
Smerfik
  • 11
  • 1
0

The solution is quite simple. Either store all dates as Unix time or in the UTC time zone along with user’s time zone name. Using stored time zone you can without any problems present to the user what was the local time at the given moment.

Mike Doe
  • 16,349
  • 11
  • 65
  • 88