How to Get Current Time (No Time Zone) in PostgreSQL

Problem

You’d like to get the current time in a PostgreSQL database. You don’t want the time zone offset.

Solution

We’ll use the function LOCALTIME to get the current time without the time zone offset.

SELECT LOCALTIME;

Here’s the result of the query:

22:15:51.987914

Discussion

The PostgreSQL function LOCALTIME returns the current time on the machine running PostgreSQL. It returns it as a time data type in the hh:mm:ss.nnnnnn format. In this format:

• hh is a 2-digit hour.
• mm is a 2-digit minute.
• ss is a 2-digit second.
• nnnnnn are fractional seconds (from zero to 6-digit precision).

As you notice, this function has no brackets. However, if you want to display the time with a specific precision, use brackets with an integer from 0 to 6 as the argument. This will return the time with your desired number of fractional seconds. For example, LOCALTIME(1) denotes only one fractional second (i.e. one place after the decimal); 2 will return two places, etc. The default value (no brackets or empty brackets) is six, which is also the maximum number of fractional seconds. Look at the next example:

SELECT LOCALTIME(0) ;

Here’s the result of the query:

21:12:06

This result doesn’t contain fractional seconds because we put 0 as the argument.

LOCALTIME returns the time at which the current transaction starts. The difference between LOCALTIME and CURRENT_TIME is that LOCALTIME doesn’t include the time zone offset.