Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL – Don't Do This

wiki.postgresql.org

Post reply on HN
Print view View original

21–30 of 36 posts

Re: PostgreSQL – Don't Do This

#21

Post by foverzar »

misterio7 wrote: post #15
thermin wrote: post #4

I'm baffled by the insistence that "users" (backend services, really) should avoid timestamp in favor of timestampz. I literally DO NOT understand, how is this good advice. My backend service exists in some abstraction of a Linux environment, with a fixed timezone, most commonly UTC. It maintains a pool of connections to Postgres, all sharing the same Postgres user, and the same timezone. Why on earth would I prefer…

The server's timezone is not even relevant. By using timestamptz, you include the timezone together with the timestamp, meaning you can be absolutely sure that the time stored is indeed UTC! You avoid the mistake of mixing up local times and UTC and forgetting to convert them.

Do people actually "timestamp" in local time?

This seems counterintuitive, aren't timestamps essentially number of seconds passed since 1 jan 1970 00:00 utc?

Re: PostgreSQL – Don't Do This

#22

Post by m_0x »

Arch485 wrote: post #7
thermin wrote: post #4

I'm baffled by the insistence that "users" (backend services, really) should avoid timestamp in favor of timestampz. I literally DO NOT understand, how is this good advice. My backend service exists in some abstraction of a Linux environment, with a fixed timezone, most commonly UTC. It maintains a pool of connections to Postgres, all sharing the same Postgres user, and the same timezone. Why on earth would I prefer…

This is actually incorrect, as per Postgres docs `timestamptz` is always stored as a UTC timestamp. The benefit to this vs a regular timestamp is that when you insert/update a timestamp value, Postgres can then convert that timestamp to UTC if necessary before storing it, and if you select a timestamp value Postgres can convert it from UTC to the timezone you want. If your connection is set to use UTC, and you always…

> if you select a timestamp value Postgres can convert it from UTC to the timezone you want

I dread timestamp issues. Hard to understand what happens and how to fix them.

You mention "if necessary". How does postgres knows if a conversion is necessary?

Re: PostgreSQL – Don't Do This

#23

Post by m_0x »

foverzar wrote: post #21
misterio7 wrote: post #15

Earlier quoted context omitted.

The server's timezone is not even relevant. By using timestamptz, you include the timezone together with the timestamp, meaning you can be absolutely sure that the time stored is indeed UTC! You avoid the mistake of mixing up local times and UTC and forgetting to convert them.

Do people actually "timestamp" in local time? This seems counterintuitive, aren't timestamps essentially number of seconds passed since 1 jan 1970 00:00 utc?

Yes, people will do anything if their systems and tools allow them to do it, either by ignorance or to facilitate them their current task. Both create tech debt and timestamp tech debt is one that is hard to pay.

Re: PostgreSQL – Don't Do This

#24

Post by remram »

foverzar wrote: post #20
Arch485 wrote: post #7

Earlier quoted context omitted.

This is actually incorrect, as per Postgres docs `timestamptz` is always stored as a UTC timestamp. The benefit to this vs a regular timestamp is that when you insert/update a timestamp value, Postgres can then convert that timestamp to UTC if necessary before storing it, and if you select a timestamp value Postgres can convert it from UTC to the timezone you want. If your connection is set to use UTC, and you always…

> If your connection is set to use UTC, and you always handle UTC timestamps Aren't timestamps always supposed to be utc?

I understood timestamps to be "seconds since 0:00 UTC January 1 1970", so absolute points in time that are not timezone-dependent. Wikipedia agrees with us too. Of course there is no official definition, and the term has evolved from an older concept (actual rubber stamps) so it's not surprising that others use it differently...

Re: PostgreSQL – Don't Do This

#25

Post by mrrubberburner »

thermin wrote: post #16
misterio7 wrote: post #15

Earlier quoted context omitted.

The server's timezone is not even relevant. By using timestamptz, you include the timezone together with the timestamp, meaning you can be absolutely sure that the time stored is indeed UTC! You avoid the mistake of mixing up local times and UTC and forgetting to convert them.

the server timezone is absolutely relevant, it comes into play when a timestamp value is being read from a timestampz column.

Afaik Databases(as opposed to server) have a default timezone setting, but that is only used if the client hasn’t provided one.

Re: PostgreSQL – Don't Do This

#26

Post by mrrubberburner »

Arch485 wrote: post #5

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…

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

Re: PostgreSQL – Don't Do This

#27

Post by mrrubberburner »

nirimda wrote: post #10
xprn wrote: post #6

Earlier quoted context omitted.

The 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

I 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…

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

Re: PostgreSQL – Don't Do This

#28

Post by remram »

mjw1007 wrote: post #12
thermin wrote: post #4

I'm baffled by the insistence that "users" (backend services, really) should avoid timestamp in favor of timestampz. I literally DO NOT understand, how is this good advice. My backend service exists in some abstraction of a Linux environment, with a fixed timezone, most commonly UTC. It maintains a pool of connections to Postgres, all sharing the same Postgres user, and the same timezone. Why on earth would I prefer…

Storing a timestamp (rather than a timestampz) and documenting that it represents a time in UTC is as sensible as storing a numeric type and documenting that it represents a distance in metres.

So... sensible?

Re: PostgreSQL – Don't Do This

#29

Post by remram »

revskill wrote: post #2

No, i suggest reverse these kinds of advice and DO THIS instead. Why ? Because IF it's said not to do this, it should not be implemented in first place. This is a trap. For example, "Don't use table inheritance", you should always use this to reuse the table definition. If not, what's the alternative ?

> it should not be implemented in first place

Some of it exists for important reasons, that are documented on the page. For example, respecting the SQL standard, or maintaining compatibility with database from a time/system without the replacement feature, or for special circumstances that don't apply to you.

Re: PostgreSQL – Don't Do This

#30

Post by mrrubberburner »

camgunz wrote: post #18
thermin wrote: post #17

Earlier quoted context omitted.

I absolutely agree, but timestampz as a column type is of little help here, because no actual timezone info is being persisted, ever. > I think best practice is to have PostgreSQL run in UTC and always store a time zone, that way you're always aware of time zone info and aren't restricted in the future.

Yeah the tradeoffs here are: - you're time zone aware but you have to deal with time zones - you're time zone naive but you can't do anything you need time zones for, like converting between time zones or using time zones (and DST) in time zone math I think the recommendation is "figure out how to get time zone info and store it, otherwise you foreclose time zone functionality and managing DST", but yeah up to you if…

Timezonetz is explicitly designed for simplified arithmetic. By storing local values that have been converted to the utc constant, you get around the problem entirely. I think there must be some fundamental misunderstanding in these threads that information is somehow being lost by storing in utc when that isn’t at all true.
Post reply on HN