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

Daniel Fisher lennybacon · Accepted Answer · 2024-03-07 14:06:24Z

465

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

edited Mar 7, 2024 at 14:06

Daniel Fisher lennybacon

4,2741 gold badge35 silver badges42 bronze badges

answered May 17, 2013 at 13:30

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

misaxi Over a year ago

I can see a funciton like now_utc() really shines when writing queries

Clay Lenhart Over a year ago

This even works when the time goes back an hour -- now() returns a timestamp that knows the offset from UTC.

code_dredd Over a year ago

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 Over a year ago

Correction: The 'utc' string must also be single-quoted, not double-quoted.

Ivor Scott Over a year ago

For expiration dates you can add to the current time: timestamp without time zone default ((now() + '5 days') at time zone 'utc')

martti · Accepted Answer · 2018-01-05 19:46:29Z

161

Still another solution:

timezone('utc', now())

answered Jan 5, 2018 at 19:46

martti

2,0851 gold badge15 silver badges10 bronze badges

Comments

Denis de Bernardy · Accepted Answer · 2013-05-17 13:04:15Z

41

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

answered May 17, 2013 at 13:04

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Comments

Risadinha · Accepted Answer · 2019-03-22 12:11:02Z

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

edited Mar 22, 2019 at 12:11

answered Jul 16, 2014 at 15:23

Risadinha

16.8k2 gold badges97 silver badges99 bronze badges

5 Comments

Frank V Over a year ago

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

VelikiiNehochuha Over a year ago

in my option it generates local datetime into db

Michael Ekoka Over a year ago

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 Over a year ago

@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!

Tom Over a year ago

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

Community · Accepted Answer · 2020-06-20 09:12:55Z

16

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 11, 2018 at 7:40

Evgenia Karunus

11.3k6 gold badges59 silver badges74 bronze badges

1 Comment

Geradlus_RU Over a year ago

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

user10259440 · Accepted Answer · 2018-08-22 11:26:42Z

4

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

answered Aug 22, 2018 at 11:26

user10259440

491 bronze badge

1 Comment

Akaisteph7 Over a year ago

Answer already exists

Collectives™ on Stack Overflow

Using current time in UTC as default value in PostgreSQL

6 Answers 6

5 Comments

Comments

Comments

5 Comments

These are 2 equivalent solutions:

Explanation:

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

Comments

Comments

5 Comments

These are 2 equivalent solutions:

Explanation:

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related