Articles Cookbook
Back to list

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





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


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


Here’s the result of the query:

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


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:


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.


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.

Recommended courses:

Recommended articles:

See also:

go to top