←back to thread

Timestamps and Time Zones in PostgreSQL (2016)

(phili.pe)
189 points GordonS | 3 comments | 30 Mar 19 21:47 UTC | HN request time: 0.001s | source
Show context
zeroimpl ◴[31 Mar 19 01:28 UTC] No.19533112[source]▶
I sometimes wish there was an actual "TIMESTAMP WITH TIME ZONE" type that stored the input timestamp as well. Basically, a version that didn't canonicalize the input by converting to UTC. This way, I can easily show timestamps to the user in the time zone they used to input them.
replies(6): >>19533287 #>>19534038 #>>19534057 #>>19534316 #>>19535066 #>>19541872 #
1. Figs ◴[31 Mar 19 05:26 UTC] No.19534057[source]▶
We needed exactly that functionality at work a few years ago, so, of course, "TIMESTAMP WITH TIME ZONE" got picked for the column in the DB. After a few weeks of people entering data, DST switched over. Eventually someone noticed all the old dates were all off by an hour...

It really should have been called "TIMESTAMP IN UTC" or "NORMALIZED TIMESTAMP" or something else instead. It's especially dangerous because the results you get back from using it look correct with casual testing and you might not notice that your database is subtly wrong for a long time.

replies(2): >>19534353 #>>19535060 #
2. StreamBright ◴[31 Mar 19 07:29 UTC] No.19534353[source]▶
Yep, I was wondering if you could create a postgres type that does exactly this. timestamp_utc and timestamp_local. That would make it easier to understand what is going on.
3. GordonS ◴[31 Mar 19 11:35 UTC] No.19535060[source]▶
Totally agree, it's one of the reasons I came across and posted this article - I too had assumed the timestampz type stored the time zone. I'm using Postgres with Ngpsql with supports using .NET DateTimeOffset types as parameters, and as you say depending on your DB config everything can actually look as you expect in the DB...