3 ms·
Another "valid" use of `timestamp` as opposed to `timestamptz` is for local times that don't all share the same time zone. For example, if a user can create an
by Arch485 3y ago
Another "valid" use of `timestamp` as opposed to `timestamptz` is for local times that don't all share the same time zone.
For example, if a user can create an event scheduled in their timezone, you probably would want to use a `timestamp` and not `timestamptz`. This way, if the user schedules the event for e.g. 5:00pm, and then the time zone changes (think DST or similar), the event is still at 5:00pm (it won't get shifted like a `timestamptz` would).
- xprn 3y agoThe article, although doesn’t mention the shift caused by TZ, does mention that the main difference between the two are that they’re a “point in time” vs “a photo of a calendar and clock”. A (local) event’s time does sound like a photo rather than a point
- nirimda 3y agoI think the time of a local event sounds much more important, and a photo of a calendar and a clock sounds much more frivolous. Also, you can't take photos of events that haven't happened yet, and it's precisely with events that haven't happened yet that you most need to store a timestamp and a timezone rather than a postgres "timestamp with timezone" (which does not involve a timezone at all). So the analogy doesn't really tell you how to use it well.
- mrrubberburner 3y agoFuture events are trivially handled by timestamptz. PG takes your future timestamp and your client connections timezone converts to a utc constant and likewise casts the utc time to your timezone on reads.
- mrrubberburner 3y agoIf a user doesn't want to follow dst then they can say so and follow standard time by configuring their client connection. I’m struggling to think of a case where I would want an alarm clock setting or a dinner reservation to not be adjusted for the locally observed time.