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

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

Still another solution:

timezone('utc', now())

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

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

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

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