0

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

2
Contributors
18
Replies
20
Views
9 Years
Discussion Span
Last Post by Leandro-AL
0

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.

0

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.

0

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.

0

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.

0

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.

0

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?

0

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

0

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!

0

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

0

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?

0

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

0

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!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.