How to Get the Current Time in PostgreSQL

Problem:

You’d like to get the current time with its time zone offset in a PostgreSQL database.

Solution:

We’ll use the CURRENT_TIME function to get the current time and time zone information. Here’s the query you’d write:

SELECT CURRENT_TIME;

Here’s the result of the query:

16:10:11.232455-05

Discussion:

The PostgreSQL function CURRENT_TIME returns the current time and time zone offset of the machine on which PostgreSQL is running. It is given as a value in the 'hh:mm:ss.nnnnnn+/-tz' format. In this format:

• hh is a 2-digit hour.
• mm is a 2-digit minute.
• ss is a 2-digit second.
• nnnnnn defines the number of fractional seconds (i.e. the precision) from 0 to 6.
• +tz or -tz is the time zone offset, either plus or minus from UTC.

CURRENT_TIME has no brackets. However, if you want to display the time with a specific precision, you can add brackets and enclose an integer from 0 to 6. (A “0” argument will return no fractional seconds, “1” will return one fractional second (e.g. one place behind the decimal), etc. This will return the time with your declared number of fractional seconds and the time zone offset. Look at the next example:

SELECT CURRENT_TIME(2) ;

Here’s the result of the query:

16:10:11.23-05

This result contains a 2-digit fractional second because we put 2 as the argument. The time zone offset appears as either positive or negative (+ or -) depending on whether the time is ahead of or behind UTC.

The time returned by this function doesn’t change during transactions or a single query. It is always the time when the transaction started.