4 ms·

I actually disagree with "Don't use timestamp (without time zone)". The Java class library (and before it, Joda time) distinguishes "instants" and "locals" whic

by moring 7y ago

I actually disagree with "Don't use timestamp (without time zone)". The Java class library (and before it, Joda time) distinguishes "instants" and "locals" which is pretty much the same as PostgreSQL's "with time zone" and "without time zone". They are just different data types with different use cases, and saying that one is preferred is misleading. I like to compare them to bytes and characters. You would use bytes if you need bytes, and you would use characters if you need characters. They are unrelated data types unless a character encoding is specified. Likewise, instants and locals are unrelated data types unless a time zone is specified. BTW, the manual page links to https://it.toolbox.com/blogs/josh-berkus/zone-of-misunderstanding-092811 https://it.toolbox.com/blogs/josh-berkus/zone-of-misundersta... which explains that "with time zone" doesn't store a time zone.

dullgiulio 7y ago
I cannot but disagree: there is no such a thing as time without a timezone. It is just an undefined time. The "local" timezone could be anything and could change any time. Not recording it is just data loss. Java Instant is a timestamp in UTC, but it's all too easy to make mistakes in serialization as the timezone information is just not present. I have spent a few hours fixing exactly that yesterday in my day job. The page you link says that with timezone makes sure to convert to and from UTC, which is just a transparent optimizing implementation detail. If you don't do timezone conversions at DB level, you have to make sure your applications do, which is a recipe for disaster.
- afiori 7y ago
  I am not saying it is the best way to handle things, but for cases where physical locations are well defined they make sense. I can imagine Walmart or Ikea saying "We open at 8 am worldwide".
  - dullgiulio 7y ago
    Your example is a time without a date, which of course does not need a timezone. Any use of the word time above actually meant datetime.
    - yellowapple 7y ago
      "We open at 4AM on Black Friday this year" There, better?
    - nearbuy 7y ago
      A date doesn't really change anything. Someone could say, "We open on October 1st, 8 am local time worldwide".
- scatters 7y ago
  There absolutely is such a thing as a time without timezone. If I schedule an event for a time in the future, I want it to occur when a wall clock in that location reads the time that I specified, even if local government alters time zone rules for that location in the interim. If I set an alarm on my phone for 6am, I want it to go off at 6am in whatever time zone I happen to be in, even if I travel after setting the alarm.
  - perlgeek 7y ago
    But you are tying it to a timezone: the timezone that will be valid at the place and date that the meeting is at. Basically, a timestamp should be tied to a timezone or a location, or at worst an indirection (like you being at a specific location). And since we don't tend to have practical support for tying timestamps to locations, we tie them to timezones instead.
    - fphilipe 7y ago
      I've written a post about timestamp vs timestamptz and the only valid reason for timestamp I could come up with is exactly this, scheduling events in the future: https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/#a-valid-use-case-for-timestamp-without-time-zone https://phili.pe/posts/timestamps-and-time-zones-in-postgres...
    - stickfigure 7y ago
      I have implemented an event ticketing system. One of the things I (fortunately) realized early was that storing times with timezones would be a problem: Events get moved, occasionally across timezone boundaries. If you store epoch-instants, then the logic for "set location" would also have to update every timetsamp in the database associated with the event. IMO, in a database you should store the "natural" representation of date-time as your users think of it. Sometimes (like your alarm clock), it's timezone-less concept. Sometimes (like a sporting event) it's a combination of datetime + location. Sometimes (like historical events) that's a fixed instant in time. Recognize which is which and model appropriately.
  - masklinn 7y ago
- skybrian 7y ago
  The question is what do you do if you really don't know the original timezone because it wasn't recorded? You don't want to guess at data that wasn't there. Obviously a new system shouldn't do this, but old data is messy.
- deleted 7y ago
  [deleted]
the_duke 7y ago
It's important to note that "with time zone" doesn't actually store the time zone, but converts to UTC. If you actually need the original time zone preserved, you need to store it separately.
- highhedgehog 7y ago
  This is the real thing
Sharlin 7y ago
The big problem with TIMESTAMP WITHOUT TIME ZONE is that if you do try to insert a datetime with a timezone included, the timezone is simply silently ignored. The confusing thing about TIMESTAMP WITH TIME ZONE is that it doesn't actually store a timezone! It simply converts input with timezone info to a global instant of time.
- mjevans 7y ago
  Since when using a database I want the same data out that I put in I would default to using the WITHOUT TIME ZONE as the database isn't storing a timezone, it's converting. I want that conversion deferred until the last moment, until whatever locale aware software facing the user gets the data and does the right thing THERE.
fphilipe 7y ago
I've written about this topic before. Maybe it convinces you that you should (almost) always use timestamptz. https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/ https://phili.pe/posts/timestamps-and-time-zones-in-postgres...
ben509 7y ago
If used as a single source of truth, the DBMS should store true propositions about the world, and that generally means that events are recorded as happening in the correct time zone. Also, datetimes that predict forward or recall back far enough may be a case where a timezone is incorrect. The deciding factor is whether you're able to make a true statement. If you're using a DBMS as a dumb data container, and there are plenty of good reasons to do so, then it may also make sense not to use timezones. If you're doing intermediate calculations or sharing with systems that don't support timezones, those values often shouldn't have timezones. That's also why all datetime libraries support times sans timezone.

by moring 7y ago

dullgiulio 7y ago

I cannot but disagree: there is no such a thing as time without a timezone. It is just an undefined time. The "local" timezone could be anything and could change any time. Not recording it is just data loss. Java Instant is a timestamp in UTC, but it's all too easy to make mistakes in serialization as the timezone information is just not present. I have spent a few hours fixing exactly that yesterday in my day job. The page you link says that with timezone makes sure to convert to and from UTC, which is just a transparent optimizing implementation detail. If you don't do timezone conversions at DB level, you have to make sure your applications do, which is a recipe for disaster.

afiori 7y ago
I am not saying it is the best way to handle things, but for cases where physical locations are well defined they make sense. I can imagine Walmart or Ikea saying "We open at 8 am worldwide".
- dullgiulio 7y ago
  Your example is a time without a date, which of course does not need a timezone. Any use of the word time above actually meant datetime.
  - yellowapple 7y ago
    "We open at 4AM on Black Friday this year" There, better?
  - nearbuy 7y ago
    A date doesn't really change anything. Someone could say, "We open on October 1st, 8 am local time worldwide".
scatters 7y ago
There absolutely is such a thing as a time without timezone. If I schedule an event for a time in the future, I want it to occur when a wall clock in that location reads the time that I specified, even if local government alters time zone rules for that location in the interim. If I set an alarm on my phone for 6am, I want it to go off at 6am in whatever time zone I happen to be in, even if I travel after setting the alarm.
- perlgeek 7y ago
  But you are tying it to a timezone: the timezone that will be valid at the place and date that the meeting is at. Basically, a timestamp should be tied to a timezone or a location, or at worst an indirection (like you being at a specific location). And since we don't tend to have practical support for tying timestamps to locations, we tie them to timezones instead.
  - fphilipe 7y ago
    I've written a post about timestamp vs timestamptz and the only valid reason for timestamp I could come up with is exactly this, scheduling events in the future: https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/#a-valid-use-case-for-timestamp-without-time-zone https://phili.pe/posts/timestamps-and-time-zones-in-postgres...
  - stickfigure 7y ago
    I have implemented an event ticketing system. One of the things I (fortunately) realized early was that storing times with timezones would be a problem: Events get moved, occasionally across timezone boundaries. If you store epoch-instants, then the logic for "set location" would also have to update every timetsamp in the database associated with the event. IMO, in a database you should store the "natural" representation of date-time as your users think of it. Sometimes (like your alarm clock), it's timezone-less concept. Sometimes (like a sporting event) it's a combination of datetime + location. Sometimes (like historical events) that's a fixed instant in time. Recognize which is which and model appropriately.
- masklinn 7y ago
skybrian 7y ago
The question is what do you do if you really don't know the original timezone because it wasn't recorded? You don't want to guess at data that wasn't there. Obviously a new system shouldn't do this, but old data is messy.
deleted 7y ago
[deleted]

the_duke 7y ago

It's important to note that "with time zone" doesn't actually store the time zone, but converts to UTC. If you actually need the original time zone preserved, you need to store it separately.

highhedgehog 7y ago
This is the real thing

Sharlin 7y ago

The big problem with TIMESTAMP WITHOUT TIME ZONE is that if you do try to insert a datetime with a timezone included, the timezone is simply silently ignored. The confusing thing about TIMESTAMP WITH TIME ZONE is that it doesn't actually store a timezone! It simply converts input with timezone info to a global instant of time.

mjevans 7y ago
Since when using a database I want the same data out that I put in I would default to using the WITHOUT TIME ZONE as the database isn't storing a timezone, it's converting. I want that conversion deferred until the last moment, until whatever locale aware software facing the user gets the data and does the right thing THERE.

fphilipe 7y ago

I've written about this topic before. Maybe it convinces you that you should (almost) always use timestamptz. https://phili.pe/posts/timestamps-and-time-zones-in-postgresql/ https://phili.pe/posts/timestamps-and-time-zones-in-postgres...

ben509 7y ago

If used as a single source of truth, the DBMS should store true propositions about the world, and that generally means that events are recorded as happening in the correct time zone. Also, datetimes that predict forward or recall back far enough may be a case where a timezone is incorrect. The deciding factor is whether you're able to make a true statement. If you're using a DBMS as a dumb data container, and there are plenty of good reasons to do so, then it may also make sense not to use timezones. If you're doing intermediate calculations or sharing with systems that don't support timezones, those values often shouldn't have timezones. That's also why all datetime libraries support times sans timezone.