Comment by thermin | Hacker News Reader

Comment by thermin | Hacker News Reader

Parent Full thread

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.

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

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?

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.

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

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?

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.

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.

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.

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.

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.

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.

Reply on news.ycombinator.com