Hacker News Reader

Comment by moring | Hacker News Reader

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-misundersta... which explains that "with time zone" doesn't store a time zone.

View on HN

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.

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".

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.

"We open at 4AM on Black Friday this year"

There, better?

A date doesn't really change anything. Someone could say, "We open on October 1st, 8 am local time worldwide".

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.

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.

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-postgres...

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.

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

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.

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.

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.

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.

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.

This is the real thing

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-postgres...

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.

Reply on news.ycombinator.com