6 ms·

> Properly storing timezone-aware times I'd just like to point out that a lot of RDBMSs support storing date and time alongside timezone information directly w

> Properly storing timezone-aware times I'd just like to point out that a lot of RDBMSs support storing date and time alongside timezone information directly without using two separate fields. SQL Server has datetimeoffset, PostgreSQL has timestamp with time zone, and Oracle has timestamp with time zone. I think MySQL does as well, but I seem to recall something strange about it. Or maybe that's just me expecting MySQL to do something weird. Similarly, most programming languages have support for datetimes with time zones. It's still all a huge pain in the butt, but you don't need to use two fields and ensure that you use both all the time. Also, you can meaningfully compare datetimes within the same database without translating the timezone if its all in one field. Also, of course, remember that a even a time with timezone without a date attached to it is inherently ambiguous.

jeremyevans 8y ago
> I'd just like to point out that a lot of RDBMSs support storing date and time alongside timezone information directly without using two separate fields. SQL Server has datetimeoffset, PostgreSQL has timestamp with time zone, and Oracle has timestamp with time zone. PostgreSQL's "timestamp with time zone" doesn't store timezone, it converts the time to UTC and stores that, and on retrieval converts the value to the connection's time zone.
- ssijak 8y ago
  That sounds dumb (if column type is called like that text in the quotes)
  - rspeele 8y ago
    It is, and it is.
  - Erwin 8y ago
    SQL data types have some verbose names, like "NATIONAL CHARACTER VARYING (20)". PG adds a "TIMESTAMPTZ" as alias for "TIMESTAMP WITH TIME ZONE" (which I'm not sure whether is a sql ansi standard type or just convention).
  - da_chicken 8y ago
    Why? If you're doing things correctly, you don't need to specify data types particularly often in SQL. You specify it once when the table is created and that's it. All you care about on the programming interface is that your application knows what data type of your language to use with each data column. What's wrong with a verbose and descriptive name for a data type?
    - kbrackbill 8y ago
      I don't think they're saying that having verbose name is dumb, I think it's that a type called "timestamp with time zone" doesn't actually store a time zone.
      - da_chicken 8y ago
        Yeah, that's what I initially thought, too, but some of the other responses have made me question that.
    - deleted 8y ago
      [deleted]
  - fphilipe 8y ago
    Might sound "dumb" or confusing (which it is), but makes a lot of sense. I've written a post explaining the difference between "timestamp with/without time zone": http://phili.pe/posts/timestamps-and-time-zones-in-postgresql/ http://phili.pe/posts/timestamps-and-time-zones-in-postgresq...
- da_chicken 8y ago
  Yeah, that's true. That's closer to Oracle's TIMESTAMP WITH LOCAL TIME ZONE data type. In PostgreSQL you have to use the AT TIME ZONE to override the output time zone, so the client has to know which time zone they want. And I'm sure there are weird corner cases where information is lost.
- erpellan 8y ago
  Part of the problem is the ANSI SQL standard specifies that 'TIMESTAMP' does not have any offset/timezone applied. The postgres docs pretty much admit this is a bad idea and recommend all timestamps be stored as 'TIMESTAMPTZ' (aka TIMESTAMP WITH TIME ZONE). It's problematic when moving data between systems with different Locale settings. Because ANSI timestamps are stored as 'local' time, timestamps will shift if you read from a DB in New York and write to one in San Francisco. Both will interpret an ANSI SQL timestamp as being in their locale unless told otherwise.
dwc 8y ago
> time with timezone without a date attached to it is inherently ambiguous I'd view that as incomplete (rather than ambiguous) until applied to a specific date. "You people in the Hawaii office feel free to join the Boston 2PM call whenever you want" is a fine thing to say even though you don't know what the Hawaii time will be until you know the date.
jameshart 8y ago
Erp. This sentence made me wince. Times don't have timezones - places have timezones. The idea that time handling is made easier by attaching timezones to times is the cause of so many headaches. Correct time handling involves understanding place - the place where things happen, the place where the user is observing them from, the place where a clock displays a particular time.
- nsxwolf 8y ago
  As well as when these observations happened or will happen in and from these places, as the rules themselves will change over time.
- flarg 8y ago
  I guess it depends on if the event concerned only happens at one place, such as a clinical site visit, or many places, such as a global call
- da_chicken 8y ago
  Yeah, that's kind of an issue with the ANSI standard. It specifically allows time with time zone. Some RDBMSs only support datetimes (timestamps) with time zone, but if you want to aim for complete ANSI compliance you're supposed to allow time with time zone. PostgreSQL, which allows that data type, specifically points out in the doc that you shouldn't use it: > The type `time with time zone` is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of `date`, `time`, `timestamp without time zone`, and `timestamp with time zone` should provide a complete range of date/time functionality required by any application. https://www.postgresql.org/docs/current/static/datatype-datetime.html https://www.postgresql.org/docs/current/static/datatype-date...
- fanf2 8y ago
  Yes! I have an old rant about iCalendar being wrong for not modelling place: https://fanf.livejournal.com/104586.html https://fanf.livejournal.com/104586.html
jsd1982 8y ago
You're confusing time zone with UTC offset. The two are not the same concept. You can determine a UTC offset given a time zone ID and a date/time but the opposite is not necessarily true. A time zone ID cannot be reliably discovered given only its UTC offset; the mapping is not unique.
- brongondwana 8y ago
  Yeah! We work around that in our logic at FastMail by choosing the largest population area which exactly matches the tzmapping data (generally it's from an icalendar file with some weird name and some rules which show the next couple of timezone transtions) It's ugly and horrible, but that's life.
dcosson 8y ago
> Similarly, most programming languages have support for datetimes with time zones They do, but in many languages they're easy to misuse, and I think this is an underappreciated part of the problem. I'd even argue that timezones really aren't that hard, they're hard when you use the wrong abstractions or try to tack them on as an afterthought. Ultimately there's a big lookup table that someone else manages for you (the Olsen database) that gives the current definitions of timezones and how they relate to UTC at various times of the year due to daylight savings changes. Given a local wall-clock time and a timezone name you can use this to unambiguously get the global instant in time it corresponds to. But a lot of libraries let you ignore or mix up these underlying types, or implicitly convert between them in a way that you may not realize is happening. The biggest problem I've seen by far is that a timezone like America/Los_Angeles is not the same as an offset like -07:00 or a named offset like PDT (the latter is the most confusing and really just needs to stop being used. It has the definition of a static offset in that it means 7 hours behind UTC, but also corresponds to a place which is sometimes on PDT and sometimes PST, and also is usually referred to as just a timezone with nothing to distinguish it from timezones from the Olson database like America/Los_Angeles. And I think it also has a name collision with a timezone in Asia). As an example of the problems this can cause, say you start with a time like 0:00 March 11 2018 in America/Los_Angeles. If you call the "to_iso861" method in any given time library you may or may not be implicitly converting the timezone to an offset as it'll return "-07:00" or maybe PST (ISO8601 doesn't have anything to say about what a timezone should be, so these are all valid). But now when you parse that again and add 4 hours to it, you'll still have a time with -7 hour offset even though the place you were originally referring to has changed and now has a -8 hour offset. The javascript Date type is notoriously bad on its own as well, since it implicitly converts everything to the browser's local timezone offset. And moment isn't a whole lot better, the timezone support is tacked on as a separate library and it will happily let you mix up offsets and timezones as well, or let you go back and forth to a Date object which can have that same implicit conversion problem. The only library I've seen that actually requires you to treat these concepts separately is Joda, which has port as js-joda. The syntax can be a little confusing and the js port is missing good builtin support for locale-aware formatting though, but those are all minor annoyances and well worth it for the better abstractions it gives you. The good news is there's a new builtin time library on the horizon for JS as well which seems to be borrowing from both joda and moment, as well as browser support for Olsen timezones and locale-aware time formatting, so I'm hopeful it will be easier to just "do the right thing" by default in the not too distant future.
pimlottc 8y ago
Implementations of time-and-time-zone different enough between databases, and languages, and libraries, etc that I believe it's worth storing them separately as the author suggests, both for simple clarity and to force the developer dealing with them to actually think about timezones. Even if the database gets it right in a single value, it's way too easy for the developer to read the field and ignore the timezone information without consideration.

jeremyevans 8y ago

> I'd just like to point out that a lot of RDBMSs support storing date and time alongside timezone information directly without using two separate fields. SQL Server has datetimeoffset, PostgreSQL has timestamp with time zone, and Oracle has timestamp with time zone. PostgreSQL's "timestamp with time zone" doesn't store timezone, it converts the time to UTC and stores that, and on retrieval converts the value to the connection's time zone.

ssijak 8y ago
That sounds dumb (if column type is called like that text in the quotes)
- rspeele 8y ago
  It is, and it is.
- Erwin 8y ago
  SQL data types have some verbose names, like "NATIONAL CHARACTER VARYING (20)". PG adds a "TIMESTAMPTZ" as alias for "TIMESTAMP WITH TIME ZONE" (which I'm not sure whether is a sql ansi standard type or just convention).
- da_chicken 8y ago
  Why? If you're doing things correctly, you don't need to specify data types particularly often in SQL. You specify it once when the table is created and that's it. All you care about on the programming interface is that your application knows what data type of your language to use with each data column. What's wrong with a verbose and descriptive name for a data type?
  - kbrackbill 8y ago
    I don't think they're saying that having verbose name is dumb, I think it's that a type called "timestamp with time zone" doesn't actually store a time zone.
    - da_chicken 8y ago
      Yeah, that's what I initially thought, too, but some of the other responses have made me question that.
  - deleted 8y ago
    [deleted]
- fphilipe 8y ago
  Might sound "dumb" or confusing (which it is), but makes a lot of sense. I've written a post explaining the difference between "timestamp with/without time zone": http://phili.pe/posts/timestamps-and-time-zones-in-postgresql/ http://phili.pe/posts/timestamps-and-time-zones-in-postgresq...
da_chicken 8y ago
Yeah, that's true. That's closer to Oracle's TIMESTAMP WITH LOCAL TIME ZONE data type. In PostgreSQL you have to use the AT TIME ZONE to override the output time zone, so the client has to know which time zone they want. And I'm sure there are weird corner cases where information is lost.
erpellan 8y ago
Part of the problem is the ANSI SQL standard specifies that 'TIMESTAMP' does not have any offset/timezone applied. The postgres docs pretty much admit this is a bad idea and recommend all timestamps be stored as 'TIMESTAMPTZ' (aka TIMESTAMP WITH TIME ZONE). It's problematic when moving data between systems with different Locale settings. Because ANSI timestamps are stored as 'local' time, timestamps will shift if you read from a DB in New York and write to one in San Francisco. Both will interpret an ANSI SQL timestamp as being in their locale unless told otherwise.

dwc 8y ago

> time with timezone without a date attached to it is inherently ambiguous I'd view that as incomplete (rather than ambiguous) until applied to a specific date. "You people in the Hawaii office feel free to join the Boston 2PM call whenever you want" is a fine thing to say even though you don't know what the Hawaii time will be until you know the date.

jameshart 8y ago

Erp. This sentence made me wince. Times don't have timezones - places have timezones. The idea that time handling is made easier by attaching timezones to times is the cause of so many headaches. Correct time handling involves understanding place - the place where things happen, the place where the user is observing them from, the place where a clock displays a particular time.

nsxwolf 8y ago
As well as when these observations happened or will happen in and from these places, as the rules themselves will change over time.
flarg 8y ago
I guess it depends on if the event concerned only happens at one place, such as a clinical site visit, or many places, such as a global call
da_chicken 8y ago
Yeah, that's kind of an issue with the ANSI standard. It specifically allows time with time zone. Some RDBMSs only support datetimes (timestamps) with time zone, but if you want to aim for complete ANSI compliance you're supposed to allow time with time zone. PostgreSQL, which allows that data type, specifically points out in the doc that you shouldn't use it: > The type `time with time zone` is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of `date`, `time`, `timestamp without time zone`, and `timestamp with time zone` should provide a complete range of date/time functionality required by any application. https://www.postgresql.org/docs/current/static/datatype-datetime.html https://www.postgresql.org/docs/current/static/datatype-date...
fanf2 8y ago
Yes! I have an old rant about iCalendar being wrong for not modelling place: https://fanf.livejournal.com/104586.html https://fanf.livejournal.com/104586.html

jsd1982 8y ago

You're confusing time zone with UTC offset. The two are not the same concept. You can determine a UTC offset given a time zone ID and a date/time but the opposite is not necessarily true. A time zone ID cannot be reliably discovered given only its UTC offset; the mapping is not unique.

brongondwana 8y ago
Yeah! We work around that in our logic at FastMail by choosing the largest population area which exactly matches the tzmapping data (generally it's from an icalendar file with some weird name and some rules which show the next couple of timezone transtions) It's ugly and horrible, but that's life.

dcosson 8y ago

> Similarly, most programming languages have support for datetimes with time zones They do, but in many languages they're easy to misuse, and I think this is an underappreciated part of the problem. I'd even argue that timezones really aren't that hard, they're hard when you use the wrong abstractions or try to tack them on as an afterthought. Ultimately there's a big lookup table that someone else manages for you (the Olsen database) that gives the current definitions of timezones and how they relate to UTC at various times of the year due to daylight savings changes. Given a local wall-clock time and a timezone name you can use this to unambiguously get the global instant in time it corresponds to. But a lot of libraries let you ignore or mix up these underlying types, or implicitly convert between them in a way that you may not realize is happening. The biggest problem I've seen by far is that a timezone like America/Los_Angeles is not the same as an offset like -07:00 or a named offset like PDT (the latter is the most confusing and really just needs to stop being used. It has the definition of a static offset in that it means 7 hours behind UTC, but also corresponds to a place which is sometimes on PDT and sometimes PST, and also is usually referred to as just a timezone with nothing to distinguish it from timezones from the Olson database like America/Los_Angeles. And I think it also has a name collision with a timezone in Asia). As an example of the problems this can cause, say you start with a time like 0:00 March 11 2018 in America/Los_Angeles. If you call the "to_iso861" method in any given time library you may or may not be implicitly converting the timezone to an offset as it'll return "-07:00" or maybe PST (ISO8601 doesn't have anything to say about what a timezone should be, so these are all valid). But now when you parse that again and add 4 hours to it, you'll still have a time with -7 hour offset even though the place you were originally referring to has changed and now has a -8 hour offset. The javascript Date type is notoriously bad on its own as well, since it implicitly converts everything to the browser's local timezone offset. And moment isn't a whole lot better, the timezone support is tacked on as a separate library and it will happily let you mix up offsets and timezones as well, or let you go back and forth to a Date object which can have that same implicit conversion problem. The only library I've seen that actually requires you to treat these concepts separately is Joda, which has port as js-joda. The syntax can be a little confusing and the js port is missing good builtin support for locale-aware formatting though, but those are all minor annoyances and well worth it for the better abstractions it gives you. The good news is there's a new builtin time library on the horizon for JS as well which seems to be borrowing from both joda and moment, as well as browser support for Olsen timezones and locale-aware time formatting, so I'm hopeful it will be easier to just "do the right thing" by default in the not too distant future.

pimlottc 8y ago

Implementations of time-and-time-zone different enough between databases, and languages, and libraries, etc that I believe it's worth storing them separately as the author suggests, both for simple clarity and to force the developer dealing with them to actually think about timezones. Even if the database gets it right in a single value, it's way too easy for the developer to read the field and ignore the timezone information without consideration.