5 ms·

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 chang

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
  That's very much a time with a timezone (the timezone being the place where the event happens). However it's not a postgres "timestamp with timezone", which (as both TFA and fphilipe's article note) doesn't store a timezone. The problem of "future event in a local place" is why the "store everything as UTC" trend is wrong, incidentally: if the timezone's offset changes (which happens regularly, sometimes with very little warning) the stored time is now completely off, by minutes to days[0] depending on the change. "Store everything as UTC" is only suitable if you're only ever storing past events (timezones don't normally get retroactively altered). [0] https://www.timeanddate.com/news/time/samoa-dateline.html https://www.timeanddate.com/news/time/samoa-dateline.html
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]

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
That's very much a time with a timezone (the timezone being the place where the event happens). However it's not a postgres "timestamp with timezone", which (as both TFA and fphilipe's article note) doesn't store a timezone. The problem of "future event in a local place" is why the "store everything as UTC" trend is wrong, incidentally: if the timezone's offset changes (which happens regularly, sometimes with very little warning) the stored time is now completely off, by minutes to days[0] depending on the change. "Store everything as UTC" is only suitable if you're only ever storing past events (timezones don't normally get retroactively altered). [0] https://www.timeanddate.com/news/time/samoa-dateline.html https://www.timeanddate.com/news/time/samoa-dateline.html

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]