PostgreSQL – Don't Do This | Hacker News Reader

PostgreSQL – Don't Do This

wiki.postgresql.org

75 pointslobo_tuerto34 comments

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 a timestampz to timestamp? Why would I even involve the server timezone into this equation?

I want to store timestamps in a uniform way, I store them in UTC as timestamps. If I stored them in a timestampz column, if the server timezone were to adjust, I'd get literally different values.

If I want to store timezone values per user (of my service), or per operation (that my users perform), then again, surely I have to handle that users move and change their timezones. Again, how does timestampz with its reliance on the connection timezone serve me?

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 handle UTC timestamps, there probably isn't much practical difference between `timestamp` and `timestamptz`, however.

Also if you're worried about the server having a different timestamp and possibly not having configured the connection timestamp correctly, as the following test shows Postgres always specifies the timezone of the timestamp it's giving you

server=# CREATE TABLE test1 (date TIMESTAMPTZ NOT NULL);

CREATE TABLE

server=# INSERT INTO test1 VALUES (NOW());

INSERT 0 1

server=# SELECT * FROM test1;

             date

------------------------------

2023-06-01 08:00:30.40968+02

(1 row)

server=# SET timezone = 'UTC';

server=# SELECT * FROM test1;

             date

------------------------------

2023-06-01 06:00:30.40968+00

(1 row)

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

Internally they are with Pg. In practice non-TZ columns can go sideways. For example your app or ORM starts trying to store local time (Pg cannot know to save equivalent UTC), so now you've no way to correct and Pg cannot either. Or the DB config gets switched to non-UTC, again you may have no recourse.

I've seen both these things happen at companies. Often users don't care or notice for a long time, until suddenly they do and you're painted into a corner.

> 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?

If the value has a TZ offset it'll know. If it doesn't but the session TZ differs from UTC then it'll know. With non-TZ timestamp both cases could store the wrong timestamp and it might be impossible to catch or correct without the original data or more context.

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.

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.

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.

Eh, not really. If I try and store a local (time zone naive) datetime from EST, it will be "converted" (read: interpreted as) UTC. Well that's bad, because:

- It doesn't know I was using EST, so it's off by 5 hours now.

- If I ever want to do math across the DST switch, I can't, because my original tzinfo was lost.

It's not wholly unreasonable to want to avoid time zones, but personally I think you should always be time zone aware and build it into your app in a reasonable way, though I recognize that's easier said than done. Mostly I guess my feeling is "get over it" though haha.

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?

Yes.

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.

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.

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.

A naive CRUD may accept local times to make things easier for users, not realizing the DB needs UTC. Juniors often commit this error unless told ahead of time. Then you may have weeks or months of data that is wrong and possibly values from many zones.

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

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 really tell you how to use it well.

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.

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.

My favorite section is https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storag... that is using text instead of char and varchar.

Of course the default of the PostgreSQL driver for several ORMs of several languages is varchar(255).

As a counterpoint: https://brandur.org/text

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.

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

Reply on news.ycombinator.com