Hi there,
I'm developing my web site and I was looking for the best way to handle time zones and its conversions. After two weeks looking and trying different alternatives, I got the good way, so I want share the problems I found and how I solved it.
Some points:
- Users around the world can access to your web site, the time is not the same for them all.
- Timezone conversion is not a trivial issue i.e. daylight saving time.
One of the first point I noticed, is that the best way is save all dates in the same time zone: I decided UTC. In that way I can sort all items of my website by date easily.
Next the question was how to translate all dates to the user date, at first I thought to make an script with javascript that tells me which time zone offset the user is using, but there is a problem: because winter dates (can) use a different time zone offset from summer time zone dates (you cannot apply same time zone offset).
So, javascript is out. Next, I thought to use php (or your favourite language to program your 2.0 website), to make the translation. The first point, is that you must ask to the user which time zone name is using. And then you store its time zone name into you database, and when presenting some dates to the user make its conversion. Here, there is no problem because the conversion date API are acceptable. But the point is that if you manage all dates directly from your SQL server things get better.
So, you ask to the user which time zone name is using and store it into your database. Next, when you select a date, you can use SQL to convert it to the user's time zone (in Postgresql):
Quote:
|
SELECT current_time AT TIME ZONE 'Europe/Madrid';
|
And this simple solution is what I was looking during two weeks U_U, I hope it will be useful to someone.
