Back to cookbooks list Articles Cookbook

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

  • CURRENT_TIMESTAMP
  • NOW()

Problem:

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

Download SQL for Data Analysis Cheat Sheet

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.
Discover the best interactive PostgreSQL courses

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.

Recommended courses:

Recommended articles:

See also: