How to Use UTC Current Time as Default in PostgreSQL

1. Standard Method of Getting UTC Time in PostgreSQL
2. Create Tables With TIMESTAMP Column in UTC Format
3. Use TIMEZONE() to Get TIME in UTC Format for Table Column in PostgreSQL
4. Wrap UTC Time Format Into a Function for Better Efficiency

Today, in PostgreSQL, we will be learning how to use UTC current time as the default time in our tables in PostgreSQL. UTC stands for COORDINATED UNIVERSAL TIME.

It is the UNIVERSAL method of regulating clocks and time worldwide. Local Time Zones have time specified only for a certain location and are mostly used to better understand commoners.

We will be looking at the different solutions of having the current time set in our PostgreSQL server as UTC.

Standard Method of Getting UTC Time in PostgreSQL

We can call a simple query to get time in UTC format for our current PostgreSQL session.

select now() at time zone ('utc');

This will return us a table as follows.

Output:

timezone (timestamp without timezone)
"2022-04-27 16:38:29.354155"

So how does this work? First, we call NOW() to get the current transaction’s start time.

This is obtained from the TIME ZONE defined in the UTC format. So this simple query helps us get the current time in the zone we require.

If you tend to call this query without defining the TIMEZONE clause, you will notice that your session will return a GMT specified time value as follows.

select now();

Output:

timezone (timestamp with timezone)
2022-04-27 21:55:14.098115+05

Hence, it is essential to specify UTC when getting the TIME value. In many cases, while using TIMESTAMP, the system TIME values will be stored in UTC, else specified differently.

Also, there may be an implicit conversion either from local to UTC dates or vice versa.

Create Tables With TIMESTAMP Column in UTC Format

A popular way of INSERTING data into a table where the record information must contain the valid time is to add an automatic time handler, a column that will consist of updated current DATE TIME and store it once the record is INSERTED.

We can use TIMESTAMP WITHOUT TIME ZONE, an alias for UTC, to specify a column that contains UTC values on INSERTION. To do this, let’s write a query as follows.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default (now() at time zone('utc')), rec int);

Running the above query will create a temporary table that contains a DEFAULT TIMESTAMP column. When we call INSERT on the table above, every record inserted will contain an automatic TIMESTAMP generated in UTC and appended.

Running a query as follows.

insert into DELFTSTACK_TUT (rec) VALUES(1), (2), (3);

It will return us a table as follows in the output.

timestamp without time zone    rec
"2022-04-27 17:13:31.159356"	1
"2022-04-27 17:13:31.159356"	2
"2022-04-27 17:13:31.159356"	3

Hence, we can see that using the NOW() expression with UTC in the DEFAULT clause works perfectly for making tables where record keeping is needed.

Make sure not to miss out brackets around the DEFAULT clause, as it has to take in the whole expression as a single item and then use it to fill the values in the respective records.

You may run into different issues in different versions of PostgreSQL. Sometimes, there may be an error defining the TIME ZONE.

Writing UTC rather than utc may cause issues due to different CASE. Hence, it is important to look out for CASE SENSITIVE EXPRESSIONS and CONSTRAINTS while writing such expressions in PostgreSQL.

Use TIMEZONE() to Get TIME in UTC Format for Table Column in PostgreSQL

Another simple alternative to get the value of TIME DATE in UTC format for our column is to call a query.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default ('utc', now()), rec int);

But this will return an error as follows upon CREATING.

Output:

ERROR:  column "time_stamp" is of type timestamp without time zone but default expression is of type record
HINT:  You will need to rewrite or cast the expression.
SQL state: 42804

The error is caused due to the mismatch of types. You can either CAST or rewrite the above expression to remove this error.

However, we will CAST our query in two different ways for you to understand better. The first one below converts this TIMESTAMP to another TIMEZONE provided with the UTC parameter.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default timezone('utc', now()), rec int);

This will provide us with a table as follows.

Output:

timestamp without time zone    rec
"2022-04-27 17:45:34.257072"	1
"2022-04-27 17:45:34.257072"	2
"2022-04-27 17:45:34.257072"	3

And our second workaround, CASTS, this expression to TEXT and then take the output, in case there may be variations across different PostgreSQL versions.

create temporary table DELFTSTACK_TUT(time_stamp  timestamp without time zone default now()::timestamp, rec int);

Running the above and then INSERTING 3 records into the table will provide us with output as follows.

timestamp without time zone    rec
"2022-04-27 22:49:51.654846"	1
"2022-04-27 22:49:51.654846"	2
"2022-04-27 22:49:51.654846"	3

Hence, you can notice that casting our NOW() to a TIMESTAMP and then inserting it into a UTC specified column gives us the correct results. However, this latter solution does not consider UTC offsets and only keeps a naive timestamp as a record.

Use INTERVAL -8:00 Rather Than UTC

You can write your query as follows if you don’t want to get stuck in CASE issues of writing UTC in your session.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default timezone(INTERVAL '+00:00', now()), rec int);

Running this query, making the table, and calling INSERT will return the correct results in UTC format. INTERVAL with +00:00 is defined under the PostgreSQL documentation as belonging to the UTC or ZULU (Military Abbreviation) time zone with an offset of +00.00.

A result will be generated as follows.

 timestamp without time zone   rec
"2022-04-27 17:57:36.054746"	1
"2022-04-27 17:57:36.054746"	2
"2022-04-27 17:57:36.054746"	3

Wrap UTC Time Format Into a Function for Better Efficiency

We can create a simple function to get the current time in UTC and then use it with our record INSERTIONS.

create function get_UTC_time()
returns timestamp as
$$
  select now() at time zone 'utc';
$$
language sql;

And then call the SELECT query to get the current time as follows.

select * from get_UTC_time();

This will output the following result.

timestamp without time zone
"2022-04-27 18:01:35.42119"

Also, we can use this inside our table as:

create temporary table DELFTSTACK_TUT(time_stamp  timestamp without time zone default get_UTC_time());

Hence, we have looked at the various ways to get the time in UTC format for our PostgreSQL sessions. Make sure to modify these according to your PostgreSQL installations or versions.

We, however, try our level best to cover each query and its conditions and constraints in as much detail as possible.