How to Get the Current Date and Time (Without Time Zone) in PostgreSQL

Problem:

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

Solution:

We’ll use the function LOCALTIMESTAMP to get the current date and time without any time zone information:

SELECT LOCALTIMESTAMP ;

Here’s the result of the query:

2019-09-24 20:10:58.977914

Discussion:

The PostgreSQL function LOCALTIMESTAMP returns the current date and time (of the machine running that instance of PostgreSQL) as a timestamp value. It uses the 'YYYY-MM-DD hh:mm:ss.nnnnnnn' format, where:

• YYYY is a 4-digit year.
• MM is a 2-digit month.
• DD is a 2-digit day.
• 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 date and time with a specific precision, put an integer from 0 to 6 as an argument in the brackets. This will return the date and time with your desired number of fractional seconds. For example, LOCALTIMESTAMP(1) denotes only one fractional second (i.e. one place after the decimal); 2 will return two places, etc. The default precision is 6, which is also the maximum number of fractional seconds; this is what you get if you don’t use brackets at all. Look at the next example:

SELECT LOCALTIMESTAMP(0) ;

Here’s the result of the query:

2019-09-24 20:10:58

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

This function returns the time at which the current transaction started. The difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP is that the LOCALTIMESTAMP doesn’t include the time zone offset.