CODEWITHSUNDEEP
postgresqltimezonetimestamp
Wichert Akkerman
Wichert Akkerman

Reputation: 5318

Using current time in UTC as default value in PostgreSQL

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...

Upvotes: 289

Views: 380232

Answers (6)

Daniel Vérité
Daniel Vérité

Reputation: 61506

A function is not even needed. Just put parentheses around the default expression:

create temporary table test(
    id int, 
    ts1 timestamp default (now() at time zone 'utc')
    -- alternative syntax
    ts2 timestamp default (timezone('utc', now())),
);

NOTE: The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior. timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension. https://www.postgresql.org/docs/current/datatype-datetime.html

Upvotes: 460

Risadinha
Risadinha

Reputation: 16666

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).

Upvotes: 22

Evgenia Karunus
Evgenia Karunus

Reputation: 11202

These are 2 equivalent solutions:

(in the following code, you should substitute 'UTC' for zone and now() for timestamp)

  1. timestamp AT TIME ZONE zone - SQL-standard-conforming
  2. 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()

Upvotes: 16

user10259440
user10259440

Reputation: 49

Function already exists: timezone('UTC'::text, now())

Upvotes: 4

martti
martti

Reputation: 2067

Still another solution: 

timezone('utc', now())

Upvotes: 160

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

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()
);

Upvotes: 41

Related Questions