How to Get the Current Date and Time with Time Zone Offset in PostgreSQL

Problem:

You’d like to get the current date and time with time zone information from a PostgreSQL database.

Solution:

We’ll use either CURRENT_TIMESTAMP or NOW() to get the current date and time with the time zone offset.

SELECT CURRENT_TIMESTAMP ;

Here’s the result of the query:

2019-09-15 13:13:12.118432+02

Discussion:

CURRENT_TIMESTAMP returns the current date, time, and time zone offset (using the date, time, and time zone of the machine on which PostgreSQL is running). This is returned as a value in the 'YYYY-MM-DD hh:mm:ss.nnnnnn+/-tz' format. In this format:

• YYYY is a 4-digit year.
• MM is a 2-digit month.
• DD is a 2-digit day of the month.
• 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.

As you notice, this function has no brackets. However, if you want to display a date and time with a specific precision, you can use the optional integer argument. It returns a date and time with fractional seconds and the time zone offset. This argument must be in the range of 0 to 6; 0 is no fractional seconds, 1 is one fractional second (e.g. one place behind the decimal), etc. Look at the next example:

SELECT CURRENT_TIMESTAMP(3) ;

Here’s the result of the query:

2019-09-15 13:01:51.142+02

This result contains a 3-digit fractional second because we put 3 as the argument in the CURRENT_TIMESTAMP function. The time zone offset still appears at the end.

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

NOW() is similar to the CURRENT_TIMESTAMP function and returns the same result. The difference is that CURRENT_TIMESTAMP is the SQL standard function, while NOW() is specific to PostgreSQL.

SELECT NOW() ;

Here’s the result of the query:

2019-08-27 12:18:55.324145+02

Note that the NOW() function requires brackets. However, you can leave them empty and get the default value.

CURRENT_TIMESTAMP and NOW() return the timestamptz data type.