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

11–20 of 36 posts

Re: PostgreSQL – Don't Do This

#11

Post by camgunz »

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…

Not an expert but I think the issue is datetime math. Ex:

SELECT ('2023-03-12 03:00:00 America/New_York'::TIMESTAMP WITH TIME ZONE - '30m'::INTERVAL) AT TIME ZONE 'America/New_York'; -- Do some math over US DST switch

You need your initial datetime to have a time zone if you want to do math that's aware of time zones. And sadly if you don't store time zones (all times are "local"!) then you can't get that information back.

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.

The problem here is actually knowing what time zone to use. You can get quagmired pretty quickly in like, do we surface this to users, do we use browser location, is there an address/location we can key off of, how do we update it as they move, blah blah. I understand the appeal of being time zone naive. But it's not cut and dry IME.

Re: PostgreSQL – Don't Do This

#12

Post by mjw1007 »

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.

Re: PostgreSQL – Don't Do This

#13

Post by CogitoCogito »

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…

I use timestamp instead of timestampz in a database I set up. My reasoning was essentially the same as yours. I wish had used timestampz. The storage size is the same. It's more explicit that the timestamp actually is UTC. And if you want to want to display times in local timezones (when things happen in my life to me, it's easier to use my current timezone as a reference point), then you don't have to do casts and instead just set your session time zone to what you want.

Then again, I think the most important thing overall is just that you should store your timestamps in UTC no matter how you do it. Worst is coming into databases and finding they are storing PST just because they happen to be there.

Re: PostgreSQL – Don't Do This

#14

Post by mattashii »

laerus wrote: post #3
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 ?

The alternative is to just copy paste the table definition or do composition with foreign keys. Seems like field inheritance was invented only because we are too lazy, I have not seen yet valid cases for inheritance in Postgres or any OOP language.

Until the implementation of declarative partitioning, inheritance was the only way to do table partitioning. Although the partitioning use case has mostly been replaced, that doesn't mean it can just be removed as there are existing systems using inheritance to manage their data.

Re: PostgreSQL – Don't Do This

#15

Post by misterio7 »

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.

Re: PostgreSQL – Don't Do This

#16

Post by thermin »

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.

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

Re: PostgreSQL – Don't Do This

#17

Post by thermin »

camgunz wrote: post #11
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…

Not an expert but I think the issue is datetime math. Ex: SELECT ('2023-03-12 03:00:00 America/New_York'::TIMESTAMP WITH TIME ZONE - '30m'::INTERVAL) AT TIME ZONE 'America/New_York'; -- Do some math over US DST switch You need your initial datetime to have a time zone if you want to do math that's aware of time zones. And sadly if you don't store time zones (all times are "local"!) then you can't get that information…

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.

Re: PostgreSQL – Don't Do This

#18

Post by camgunz »

thermin wrote: post #17
camgunz wrote: post #11

Earlier quoted context omitted.

Not an expert but I think the issue is datetime math. Ex: SELECT ('2023-03-12 03:00:00 America/New_York'::TIMESTAMP WITH TIME ZONE - '30m'::INTERVAL) AT TIME ZONE 'America/New_York'; -- Do some math over US DST switch You need your initial datetime to have a time zone if you want to do math that's aware of time zones. And sadly if you don't store time zones (all times are "local"!) then you can't get that information…

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 you're willing to accept that risk.

Re: PostgreSQL – Don't Do This

#20

Post by foverzar »

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 your connection is set to use UTC, and you always handle UTC timestamps

Aren't timestamps always supposed to be utc?

Post reply on HN