CODEWITHSUNDEEP
sqlpostgresqltimestamp
fadedbee
fadedbee

Reputation: 44745

Postgres UTC timestamps?

I want a UTC timestamp "created" field in several tables.

ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), gives me the local-time with time zone:

foo=# select ts from messages;
              ts               
-------------------------------
 2017-05-26 11:54:07.532796+01
 2017-05-26 11:54:08.536241+01
 2017-05-26 11:54:09.538119+01
 2017-05-26 11:54:10.541089+01
 2017-05-26 11:54:11.543262+01

ts TIMESTAMP NOT NULL DEFAULT now(), gives me local time without timezone information (dangerous):

foo=# select ts from messages;
             ts             
----------------------------
 2017-05-26 11:56:00.134596
 2017-05-26 11:56:01.13798
 2017-05-26 11:56:02.140586
 2017-05-26 11:56:03.143076
 2017-05-26 11:56:04.14565

What field definition will give me the following, when the code is run on a server in a non-UTC time zone (e.g. BST, British Summer Time):

foo=# select ts from messages;
             ts             
----------------------------
 2017-05-26 10:56:00.134596+00
 2017-05-26 10:56:01.13798+00
 2017-05-26 10:56:02.140586+00
 2017-05-26 10:56:03.143076+00
 2017-05-26 10:56:04.14565+00

even:

foo=# select ts from messages;
             ts             
----------------------------
 2017-05-26 10:56:00.134596
 2017-05-26 10:56:01.13798
 2017-05-26 10:56:02.140586
 2017-05-26 10:56:03.143076
 2017-05-26 10:56:04.14565

would be fine, if nothing else is possible (note: times are 10am UTC, not 11am BST in both examples above).

Upvotes: 0

Views: 3180

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51456

timestamp with time zone is your choice:

t=# select now();
              now
-------------------------------
 2017-05-26 11:04:19.240294+00
(1 row)

t=# set timezone to 'EET';
SET
t=# select now();
              now
-------------------------------
 2017-05-26 14:04:45.749123+03
(1 row)

but if you insert into select now() it will save time at UTC which you can check:

t=# select now() at time zone 'utc';
          timezone
----------------------------
 2017-05-26 11:05:01.045544
(1 row)

https://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-TIMEZONES

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

Upvotes: 7

Related Questions