Timezones and DST

[Leandro-AL]

Hi all!

I'm trying to write a "last login" message in my login script. The plan is to store dates in the database with UTC_TIMESTAMP and then show them back to the user with CONVERT_TZ, using each user's specified timezone (they have set that on registration).

However here lies the problem of DST (daylight savings time). Somewhere i read that if you use named timezones eg. Europe/Rome, you need not worry about daylight savings. Is that true?

If not, is there a recommended way of doing this without having to worry about DST?

Thanks,
Leandro

[dickersonka]

I would suggest to do this in code rather than the database.

Always store your dates in utc and don't worry about the timezone, then in the code do the conversion necessary.

[Leandro-AL]

But that's what i'm planning to do, store dates in UTC and convert them for viewing with CONVERT_TZ().

My only concern is how will this affect DST, need i worry or is it set up automatically?

Visitors to my site come from all around the world so i want to do this properly.

[dickersonka]

I would suggest not to use convert_tz in mysql, do it in your application code.

UTC is not subject to time zones or dst. When you get your utc value to the application code, apply your timezone and your dst at that time.

Not sure what language you are using on code side, here is how to do everything in php
http://whatstheplot.com/blog/2008/03/13/dealing-with-timezones-in-php/

[Leandro-AL]

I see so you are recommending doing this all in PHP. I am using PHP by the way.

The problem is that i don't know when each DST starts and ends and how many minutes each DST is (in some areas it's 30 minutes). And even if i did, coding this seems overkill.

Thanks for that link, the concept there is interesting and maybe it can help me.

I'm on shared hosting by the way.

It just seems, according to this link http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html, that there is some table in MySQL with the necessary information, which enables you to simply call CONVERT_TZ() using a named timezone, and the time will be converted to that timezone, including DST.

I have emailed my host about this asking if this thing is possible, and currently awaiting a response.

If it's not then probably i will resort to using PHP.

[dickersonka]

As long as you are on mysql 5 you can do it that, and pretty sure in 4 as well. Just ensure the zone table is out there with entries.

SELECT COUNT(*) FROM mysql.time_zone_name;

The reason why I would say to do it in the app code is that you always know your datetimes are coming across as utc, and not already timezone formatted. It is one of those things you can go either way on, but from experience I have found it most consistent to let the database simply store and retrieve the data, and reside your formatting (timezone and dst in this instance) in the app code.

[Leandro-AL]

I'm on MySQL 5. Thanks for your help, the tech support must have become confused with my question, he directed me to a time functions tutorial for PHP :D.

Well it seems easier and less painful to code it this way, with SQL, because in any case i will use UTC_TIMESTAMP when inserting dates in the database.

INSERT INTO table (dates) VALUES (UTC_TIMESTAMP)
SELECT CONVERT_TZ(dates, 'UTC', '$user_timezone')

I know the code is full of mistakes, just to give you an idea. So as you can see, dates will be in UTC. Of course, more formatting will occur to make the dates more readable but that's not an issue.

[Leandro-AL]

Oh by the way your site looks messed up with Opera. =/

[dickersonka]

Lol thanks. It was good with the last version of firefox and never tried opera. Thanks for your foresight.

[Leandro-AL]

Oh by the way let me ask you another thing, what's the difference between America/Argentina/Buenos_Aires and America/Buenos_Aires? There are several such cases in http://www.php.net/timezones and i'm wondering what's the difference between them. Also, there are several named timezones in the "other" category, what's the advantage of using any of those?

I'm thinking to use only the Continent/City format, unless if there's some advantage to the ones listed in "other" or the Continent/Country/City format.

Do you have any idea?

[dickersonka]

America/Buenos_Aires is deprecated. Still works, but the full continent/country/city is the new calling convention.

Same thing for the other category, it is deprecated but still exists just for backwards compatibility.

Use the new calling convention of Continent/Country/City to ensure compatibility for future releases

[Leandro-AL]

Where can i find a list of such conventions? php.net/timezones doesn't say which is best to use and continent/country/city exists only for few countries.

Thanks for your help!

[dickersonka]

http://us.php.net/manual/en/timezones.others.php

has a warning at bottom of the page

the continent/cities are deprecated, you need to use country with them

you can see the time zone info's here
http://www.timezoneconverter.com/cgi-bin/zoneinfo.tzc

[Leandro-AL]

Thanks. I guess i'll have to ask my host about what named time zones they have registered.

[dickersonka]

use the query from before

SELECT * FROM mysql.time_zone_name;

[Leandro-AL]

Ah, yea obviously. :D

I can't seem to find out information about deprecated time zone names. All i can find is deprecated abbreviations. Where did u get this info from?

[Leandro-AL]

I'm asking cause now i am making a drop down box containing timezones for the user to pick, and because this list is quite large i would rather do it right now than have to do it again at some point in the future. :P

[dickersonka]

http://dev.mysql.com/downloads/timezones.html

That is the mysql list

[Leandro-AL]

Hm, i'm not sure on how to use those. I think i'll just run that select query on my host's timezone table and use what i get from there.

Thanks for your help!