时间戳，时区和夏令时

Question

I've got quite a large central MySQL database. Up till now, I've been using unix timestamps to store dates / times.

This all worked fine up until a few months back, when all of a sudden lots of data which is retrieved from the database went missing. I checked the database, and it was all there, unaltered.

The applications I created are precise down to the second, and when daylights savings took over, it through my application off by 3599 seconds (3600 seconds in an hour)

I'm trying to get my head around timezones / daylights savings etc and struggling to understand the concepts slightly!!

All my PHP files are set to the Europe/London timezone. All dates, times etc in my database are stored as unix timestamps (integers).

Should I convert all the timestamps to the datetime() field type? Or is there a way to avoid daylights savings? If I switch to UTC, will I need to adjust any of the timestamps?

Any advice is greatly appreciated.

Answer 1

You can keep your unix timestamps in your database unaltered.

It is your application which is changing the retrieved times to server time, Europe/London in your case, which implements DST as you are aware.

Set your server to UTC and start using the DateTime classes to avoid these problems in the future.

Also make sure to set your default timezone to UTC in php.ini