how do you deal with timezone issues?

views:

229

answers:

+6 Q:

how do you deal with timezone issues?

Hello,

For a scheduling system, what's the best way to save the timezone of client/event in a central server database coming from multiple sources mobile,web,client app.

How do you store the dates? Alarms, reminders etc...
How do you handle the DST setting?
How do you handle the events if client has traveled to a different location?

I need the solution to work with all databases in tags.

+7 A:

Everything in UTC, and another column for the offset.

Ignacio Vazquez-Abrams 2010-01-27 15:44:20

the offset can change when DST changes, how do you when you have to change the Offset?

Pentium10 2010-01-27 15:45:40

Absolutely the right answer. If you don't use one specific timezone to base all your timestamps, etc, on, you're going to have a world of problems. Just use an offset lookup table with date ranges, and then link your user to that. Don't store the offset as an actual number.

Satanicpuppy 2010-01-27 15:46:11

The offset is stored as e.g. `EST5EDT`.

Ignacio Vazquez-Abrams 2010-01-27 15:52:28

but that can change with DST, for example I am on `GMT+2` in winter and `GMT+3` in the summer

Pentium10 2010-01-27 15:55:52

That makes you `EET2EEDT`. Or whatever the designation is.

Ignacio Vazquez-Abrams 2010-01-27 15:59:58

I usually use zone names from ZoneInfo (http://en.wikipedia.org/wiki/Zoneinfo) that are available in most of the system I use (java/postgresql/unix) and that do not change when DST occur

chburd 2010-01-27 16:08:53

Check CONVERT_TZ in mysql. Check Steve g answer

Carlos Gutiérrez 2010-01-27 16:16:38

PostgreSQL can convert timezone on-the-fly: "SELECT timestamp_utc_field AT TIME ZONE 'America/Chicago'", for example, converts the data in the field to the appropriate value, including adjustments for DST.

Matthew Wood 2010-01-27 22:27:42

what about MySQL and SQLITE?

Pentium10 2010-01-28 09:58:47

+2 A:

Store dates as UTC timestamps - convert to local time when displaying data to the user.

clownbaby 2010-01-27 15:45:34

Safe to store in one specific time zone ideally in UTC
If you store the time with a date, you are safe in DST.
Suggesting you to use a web service to find the time difference.

Chathuranga Chandrasekara 2010-01-27 15:46:37

+4 A:

These are the key points to the strategy I tend to use.

Every date is UTC internally to the system.
Users are shown dates in the their local timezone.
We allow users to override the timezone they are using in case they travel and they want to keep the timezone consistent.
Make the timezone easily visible. Either display it with the time or as part of the column header.

Steve g 2010-01-27 15:49:31

ansaurus

tags:

views:

answers:

how do you deal with timezone issues?

related questions