Need help with storing dates in Mysql (timezones)

I'm trying to learn/re-learn PHP and write a big script, I used to know a little but its been over 8 years and a lot has changed.

Right now I'm trying to perfect storing and altering universal dates to display in individual users timezones without a lot of unnecessary code.

What is the best method to store date/time information (past, current, future dates) in mysql... UTC timestamps in datetime columns, unix timestamps in some other column, or something else?

What is the best way to return the correct date/time to the end user (and alter as needed)... add/subtract an offset direct from the mysql query, or use a php class or function?

The script I'm going to write will be largely focussed on dates with many dates displayed and/or manipulated per page load, so I dont want to use a lot of unnecessary php code that will eat ram when run in a loop, but I also want to keep the PHP and MySQL pretty standard for use on the average web server.

I've been reading up on this as much as I can for a few days and trying to work the newer php date/timezone functions in my head, but it seems like everybody does it different and it's a lot to take in without the experience of knowing it.

I would appreciate any advice on how you handle dates so I know where to start.

Thanks.

BTW, I'm new here, so Hello.

You absolutely should use MySQL's Datetime data type. It's a fool's errand to not. You have a vast array of handy functions available to you with the Date and Datetime types.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

None of that functionality is available if you store a datetime as an integer (gasp!).

As for storage, I prefer to store all datetimes as UTC, and convert from there. I wrote my own converter a while back (it's a pretty basic function) but PHP5 has a DateTime object (which I have used much at all) that looks like it will be pretty handy to use.

Yes I agree about the mysql functions (which I would like to be able to use), but then my concern is if all the times are in UTC in the datetime column will the built-in mysql date/time functions even be relevant since the user will be in a different timezone?

Could/should I use this in mysql to retrieve data...

SET SESSION time_zone = '+0:00' (set to the users offset)

...or would it cause problems since the time was created in UTC; I'm not sure if the datetime columns supports timezones.

The way I look at it, you're going to store all Datetime values in your database in a single timezone, right? You may as well make it the UTC timezone.

....And of course, I contract myself... I just looked back at some code I did last year and it turns out I am storing all Datetimes in the server's timezone. Every time I select a datetime out of a table I run it through a PHP function that adjusts it back to UTC then applies the user's timezone offset. I think I remember deciding to do that because I was adding timezone support after I had data out there and I didn't feel like converting the existing data.

I've never used SET SESSION so I don't have any input on that.

I want to store dates in UTC (no question there), but I am trying to figure out the best way to do so.

I made a test script and put a mysql formatted timestamp YYYYMMDDHHMMSS (in UTC time) into a datetime column, but when I return the data using "UNIX_TIMESTAMP(columnname)" mysql must assume the date was added in the servers timezone and adds an offset before returning a unix timestamp which throws it off several hours, so I must be going about it wrong.

Using "SET SESSION time_zone = '+00:00'" in the script corrects the problem, but I am not sure how server friendly this command is; I want the script to work on shared hosting.

For just displaying the formatted date in the users timezone, the following works fine...

DATE_FORMAT(dtutc, '%Y-%m-%d %H:%i:%s') - INTERVAL 5 HOUR

So I guess I just need to figure out how to return a unix timestamp without mysql altering it, for when I need to manipulate the date in PHP.
I either have to use the mysql "session time_zone" function, or use something like...

date('U',strtotime(mysqldatetime)-18000); // also included the offset here.


I'm going to play around some more until I figure it out, but still would appreciate any additional experience on the issue.