Back to cookbooks list Articles Cookbook

How to Get the Current Time in PostgreSQL

  • CURRENT_TIME

Problem:

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

Download SQL for Data Analysis Cheat Sheet

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

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.

Recommended courses:

Recommended articles:

See also: