←back to thread

Timestamps and Time Zones in PostgreSQL (2016)

(phili.pe)
189 points GordonS | 1 comments | 30 Mar 19 21:47 UTC | HN request time: 0.206s | 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. samgaw ◴[31 Mar 19 11:37 UTC] No.19535066[source]▶
It sounds like what you're asking for falls under a 'composite type'. So what you could do is something along the lines of:

  CREATE TYPE datetimetz AS (
    dt  timestamptz,
    tz  varchar
  );
Then in your table def you can call your new type to avoid multiple columns:

  CREATE TABLE example (
    id          integer,
    created_at  datetimetz
  );