4 ms·

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 locat

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
JelteF 7y ago
I think what the parent tried to say is that there's no such thing as timeSTAMP without a timezone. There's like you said a time without a timestamp, but it does not contain a date as well.

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

JelteF 7y ago

I think what the parent tried to say is that there's no such thing as timeSTAMP without a timezone. There's like you said a time without a timestamp, but it does not contain a date as well.