SQL Pocket Guide, 3rd Edition

Datetime Functions: PostgreSQL

The following subsections demonstrate some of PostgreSQL’s more useful datetime functions.

Getting Current Date and Time

PostgreSQL implements the following functions to return the current date and time:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME [(precision)]
CURRENT_TIMESTAMP [(precision)]

LOCALTIME
LOCALTIMESTAMP
LOCALTIME [(precision)]
LOCALTIMESTAMP [(precision)]

NOW()

The function NOW() is equivalent to CURRENT_TIMESTAMP. The CURRENT functions return values with a time zone. The LOCAL functions return values without a time zone.

For example:

SELECT
    TO_CHAR(CURRENT_TIMESTAMP, 'HH:MI:SS tz'),
    TO_CHAR(LOCALTIMESTAMP, 'HH:MI:SS tz');

 05:02:00 est | 05:02:00

Some functions accept an optional precision argument. You can omit the argument to receive the fullest possible precision. Alternatively, you can use the argument to round to precision digits to the right of the decimal. For example:

SELECT CURRENT_TIME, CURRENT_TIME(1);

 17:10:07.490077-05 | 17:10:07.50-05

None of the previously listed functions advance their return values during a transaction. You will always get the date and time at which the current transaction began. The function TIMEOFDAY() is an exception to this rule:

SELECT TIMEOFDAY();

 Sun Feb 05 17:11:39.659280 2006 EST

TIMEOFDAY() returns wall-clock time, advances during a transaction, and returns a character-string result.

Rounding and Truncating

PostgreSQL does not support the rounding of datetime values; however, it does provide ...