9

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.

Pentium10
  • 204,586
  • 122
  • 423
  • 502
  • Note that the answers listed below all suggest a pure-UTC approach, which is *not* ideal for the case of scheduling systems. See accepted answer in dup post. Thanks. – Matt Johnson-Pint Jul 28 '17 at 21:46

4 Answers4

9

Everything in UTC, and another column for the offset.

Ignacio Vazquez-Abrams
  • 776,304
  • 153
  • 1,341
  • 1,358
  • the offset can change when DST changes, how do you when you have to change the Offset? – Pentium10 Jan 27 '10 at 15:45
  • 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 Jan 27 '10 at 15:46
  • The offset is stored as e.g. `EST5EDT`. – Ignacio Vazquez-Abrams Jan 27 '10 at 15:52
  • but that can change with DST, for example I am on `GMT+2` in winter and `GMT+3` in the summer – Pentium10 Jan 27 '10 at 15:55
  • That makes you `EET2EEDT`. Or whatever the designation is. – Ignacio Vazquez-Abrams Jan 27 '10 at 15:59
  • 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 Jan 27 '10 at 16:08
  • Check CONVERT_TZ in mysql. Check Steve g answer – Carlos Gutiérrez Jan 27 '10 at 16:16
  • 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 Jan 27 '10 at 22:27
5

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
  • 2,471
  • 17
  • 16
3

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

danjarvis
  • 10,040
  • 3
  • 22
  • 21
0
  1. Safe to store in one specific time zone ideally in UTC
  2. If you store the time with a date, you are safe in DST.
  3. Suggesting you to use a web service to find the time difference.
Chathuranga Chandrasekara
  • 20,548
  • 30
  • 97
  • 138