Is timezone just an offset number or "more information"?

3
votes

I live in a country where they change the time twice a year. That is: there is a period in the year when the offset from UTC is -3 hours (-180 mins) and other period where the offset is -4 hours (-240 mins)

Grafically:

       |------- (offset = -3) -------|------- (offset is -4) -------|
start of year                      mid                            end of year

My question is:
the "timezone" is just the number representing the offset? that is: my country has two timezones? or the timezone includes this information?

This is important because I save every date in UTC timezone (offset = 0) in my database.

Should I, instead, be saving the dates with local timezone and saving their offset (at the moment of saving) too?

Here is an example of a problem I see by saving the dates with timezone UTC:
Lets say I have a system where people send messages.
I want to have a statistics section where I plot "messages sent v/s hour" (ie: "Messages sent by hour in a regular day")

Lets say there are just two messages in the whole database:

  1. Message 1, sent in march 1, at UTC time 5 pm (local time 2 pm)
  2. Message 2, sent in august 1, at UTC time 5 pm (local time 1 pm)

Then, if I create the plot on august 2, converting those UTC dates to local would give me: "2 messages where sent at 1 pm", which is erratic information!

3
timezoneutctimezone-offset
offsets are handy, but can't capture the political realities. e.g. I'm in Saskatchewan, which is CST, but stays UTC-6 all the time (we do not follow daylight savings). – Marc B
Store everything as UTC and convert it to locale specific forms for human eyes only. – msw
Another way to think of it is that clocks lie during Daylight times. Your example is an issue of semantics for which there is no correct answer, only what you think is meaningful to your users. – msw

3 Answers

5
votes

From the tag wiki here on StackOverflow:

TimeZone != Offset

A time zone can not be represented solely by an offset from UTC. Many time zones have more than one offset due to "daylight savings time" or "summer time" rules. The dates that offsets change are also part of the rules for the time zone, as are any historical offset changes. Many software programs, libraries, and web services disregard this important detail, and erroneously call the standard or current offset the "zone". This can lead to confusion, and misuse of the data. Please use the correct terminology whenever possible.

There are two commonly used database, the Microsoft Windows time zone db, and the IANA/Olson time zone db. See the wiki for more detail.

Your specific questions:

the "timezone" is just the number representing the offset? that is: my country has two timezones? or the timezone includes this information?

You have one "time zone". It includes two "offsets".

Should I, instead, be saving the dates with local timezone and saving their offset (at the moment of saving) too?

If you are recording the precise moment an event occurred or will occur, then you should store the offset of that particular time with it. In .Net and SQL Server, this is represented using a DateTimeOffset. There are similar datatypes in other platforms. It only contains the offset information - not the time zone that the offset originated from. Commonly, it is serialized in ISO8601 format, such as:

2013-05-09T13:29:00-04:00

If you might need to edit that time, then you cannot just store the offset. Somewhere in your system, you also need to have the time zone identifier. Otherwise, you have no way to determine what the new offset should be after the edit is made. If you desire, you can store this with the value itself. Some platforms have objects for exactly this purpose - such as ZonedDateTime in NodaTime. Example:

2013-05-09T13:29:00-04:00  America/New_York

Even when storing the zone id, you still need to record the offset. This is to resolve ambiguity during a "fall-back" transition from a daylight offset to a standard offset.

Alternatively, you could store the time at UTC with the time zone name:

2013-05-09T17:29:00Z  America/New_York

This would work just as well, but you'd have to apply the time zone before displaying the value to anyone. TIMESTAMP WITH TIME ZONE in Oracle and PostgreSQL work this way.

You can read more about this in this post, while .Net focused - the idea is applicable to other platforms as well. The example problem you gave is what I call "maintaining the perspective of the observer" - which is discussed in the same article.

2
votes

that is: my country has two timezones? or the timezone includes this information?

The term "timezone" usually includes that information. For example, in Java, "TimeZone represents a time zone offset, and also figures out daylight savings" (link), and on Unix-like systems, the tz database contains DST information.

However, for a single timestamp, I think it's more common to give just a UTC offset than a complete time-zone identifier.

[…] in my database.

Naturally, you should consult your database's documentation, or at least indicate what database you're using, and what tools (e.g., what drivers, what languages) you're using to access it.

1
votes

Here's an example of a very popular format for describing timezones (though not what Windows uses).

You can see that it's more than a simple offset. More along the lines of offsets and the set of rules (changing over time) for when to use which offset.