256

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...
lospejos
  • 1,976
  • 3
  • 19
  • 35
Wichert Akkerman
  • 4,918
  • 2
  • 23
  • 30

6 Answers6

419

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')
);
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • 3
    I can see a funciton like now_utc() really shines when writing queries – misaxi Nov 01 '15 at 07:04
  • This even works when the time goes back an hour -- now() returns a timestamp that knows the offset from UTC. – Clay Lenhart Aug 22 '16 at 03:25
  • 4
    FWIW, running this query in PostgreSQL 11.5: `ALTER TABLE testcase_result ADD COLUMN date_created TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE "UTC") NOT NULL;` fails with: `ERROR: column "UTC" does not exist`. ***Make sure `'utc'` is all lower-case.*** – code_dredd Nov 22 '19 at 01:00
  • 4
    Correction: The `'utc'` string must also be single-quoted, not double-quoted. – code_dredd Nov 22 '19 at 01:11
  • For expiration dates you can add to the current time: `timestamp without time zone default ((now() + '5 days') at time zone 'utc')` – Ivor Scott Apr 25 '21 at 08:05
134

Still another solution: 

timezone('utc', now())
martti
  • 1,758
  • 1
  • 12
  • 10
38

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()
);
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
22

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

Risadinha
  • 16,058
  • 2
  • 88
  • 91
  • It's an interesting trick but it can lead to confusion unless you are aware of this behavior. The accepted answer is crystal clear on the action and result desired. Same with the function but I stay away from functions in DBs.... – Frank V Feb 09 '17 at 06:07
  • in my option it generates local datetime into db – VelikiiNehochuha Oct 09 '18 at 17:24
  • 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). – Michael Ekoka Mar 22 '19 at 11:20
  • @MichaelEkoka I have added your comment into the answer - please go ahead and edit it if you want. You're explaining it very clearly. Thanks! – Risadinha Mar 22 '19 at 12:12
  • 1
    **Warning**: a timestamp with time zone field does NOT store the time zone, contrary to reasonable assumption. See the below link and search the page for UTC. On input, a timestamp with time zone converts the incoming value to utc and stores that value **WITH NO TIME ZONE OR OFFSET INFORMATION**. On output, the stored utc value is converted to a local time using time zone of the client if available. This type is all about value conversion at query time. You should test this by storing from one time zone and selecting from a different one. postgresql.org/docs/11/datatype-datetime.html – Tom Sep 25 '19 at 16:40
15

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

Community
  • 1
  • 1
Evgenia Karunus
  • 10,715
  • 5
  • 56
  • 70
  • 1
    Thank's for summarizing these things. I used lowercase version `at timezone 'utc'` and that doesn't worked with my PostgreSQL setup. Using uppercase letters solved issue – Geradlus_RU Aug 31 '19 at 09:58
4

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

user10259440
  • 41
  • 1