Earlier quoted context omitted.
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.
PostgreSQL – Don't Do This
31–36 of 36 posts
Re: PostgreSQL – Don't Do This
#32Earlier quoted context omitted.
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.
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.
Re: PostgreSQL – Don't Do This
#33My 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
Re: PostgreSQL – Don't Do This
#34Earlier 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 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.
Re: PostgreSQL – Don't Do This
#35Earlier 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?
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.
Re: PostgreSQL – Don't Do This
#36Earlier 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?
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.