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 can 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:

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

The time returned by this function doesn’t change during the execution of a query or a transaction. 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:

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

Note that unlike CURRENT_TIMESTAMP function, 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.