Using current time in UTC as default value in PostgreSQL

Question

I have a column of the TIMESTAMP WITHOUT TIME ZONE type and would like to have that default to the current time in UTC  Getting the current time in UTC is easy   postgres   select now   at time zone  utc             timezone           ----------------------------  2013-05-17 12 52 51 337466  1 row    As is using the current timestamp for a column   postgres   create temporary table test id int  ts timestamp without time zone default current timestamp   CREATE TABLE postgres   insert into test values  1  returning ts               ts              ----------------------------  2013-05-17 14 54 33 072725  1 row    But that uses local time  Trying to force that to UTC results in a syntax error   postgres   create temporary table test id int  ts timestamp without time zone default now   at time zone  utc    ERROR   syntax error at or near  at  LINE 1     int  ts timestamp without time zone default now   at time zo

User · Answer

Wrap it in a function   create function now utc   returns timestamp as      select now   at time zone  utc      language sql   create temporary table test    id int    ts timestamp without time zone default now utc

User · Answer

A function is not even needed  Just put parentheses around the default expression   create temporary table test      id int       ts timestamp without time zone default  now   at time zone  utc

User · Answer

Still another solution    timezone  utc   now

User · Answer

Function already exists  timezone  UTC   text  now

User · Answer

These are 2 equivalent solutions   in the following code  you should substitute  UTC  for zone and now   for timestamp   timestamp AT TIME ZONE zone - SQL-standard-conforming timezone zone  timestamp  - arguably more readable   The function timezone zone  timestamp  is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone    Explanation   zone can be specified either as a text string  e g    UTC   or as an interval  e g   INTERVAL  -08 00   - here is a list of all available time zones timestamp can be any value of type timestamp now   returns a value of type timestamp  just what we need  with your database s default time zone attached  e g  2018-11-11T12 07 22 3 05 00   timezone  UTC   now    turns our current time  of type timestamp with time zone  into the timezonless equivalent in UTC  E g   SELECT timestamp with time zone  2020-03-16 15 00 00-05  AT TIME ZONE  UTC  will return 2020-03-16T20 00 00Z   Docs  timezone

User · Answer

What about  now    timestamp   If your other timestamp are without time zone then this cast will yield the matching type  timestamp without time zone  for the current time   I would like to read what others think about that option  though  I still don t trust in my understanding of this  with without  time zone stuff   EDIT  Adding Michael Ekoka s comment here because it clarifies an important point      Caveat  The question is about generating default timestamp in UTC for   a timestamp column that happens to not store the time zone  perhaps   because there s no need to store the time zone if you know that all   your timestamps share the same   What your solution does is to   generate a local timestamp  which for most people will not necessarily   be set to UTC  and store it as a naive timestamp  one that does not   specify its time zone

[postgresql] Using current time in UTC as default value in PostgreSQL

Examples related to postgresql

Examples related to timezone

Examples related to timestamp