How can PostgreSQL get the current date time and notes

  • 2020-05-06 11:55:31
  • OfStack

When developing a database application or debugging code, it is often necessary to get the current date and time of the system. Let's take a look at the functions provided in PostgreSQL.

current date

CURRENT_DATE

The CURRENT_DATE function is used to get the current date of the database server:


postgres=# SELECT CURRENT_DATE;
 current_date 
--------------
 2019-09-28
(1 row)

This function is called without parentheses after the function name. This date is the server's date, not the client's date.

current transaction start time

The following function can be used to get the current time of the database server:


CURRENT_TIME
CURRENT_TIME(precision)
LOCALTIME
LOCALTIME(precision)

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP(precision)
LOCALTIMESTAMP
LOCALTIMESTAMP(precision)

CURRENT_TIME, LOCALTIME, CURRENT_TIMESTAMP, LOCALTIMESTAMP

The first four functions are used to get the time, and the last four functions are used to get the timestamp. CURRENT_TIME and CURRENT_TIMESTAMP contain time zone information, LOCALTIME and LOCALTIMESTAMP do not. precision is used to specify the number of digits in decimal seconds, with a value of 0-6, which defaults to 6.


postgres=# SELECT CURRENT_TIME, LOCALTIME, CURRENT_TIMESTAMP, LOCALTIMESTAMP;
  current_time  |  localtime  |    current_timestamp    |    localtimestamp    
--------------------+-----------------+-------------------------------+----------------------------
 12:20:50.602412+08 | 12:20:50.602412 | 2019-09-28 12:20:50.602412+08 | 2019-09-28 12:20:50.602412
(1 row)

postgres=# SELECT CURRENT_TIME(3), LOCALTIME(3), CURRENT_TIMESTAMP(3), LOCALTIMESTAMP(3);
 current_time  | localtime  |   current_timestamp   |   localtimestamp   
-----------------+--------------+----------------------------+-------------------------
 12:28:03.547+08 | 12:28:03.547 | 2019-09-28 12:28:03.547+08 | 2019-09-28 12:28:03.547
(1 row)

Note: all of the functions above , including CURRENT_DATE, return the time the current transaction started. Calling the same function multiple times during the same transaction will return the same value, and the result will not increase over time. This may differ from other database implementations.

The following example USES the pg_sleep function to pause for 3 seconds to get the current time again:


postgres=# BEGIN;
BEGIN
postgres=# SELECT CURRENT_TIMESTAMP;
    current_timestamp    
-------------------------------
 2019-09-28 12:43:57.075609+08
(1 row)

postgres=# SELECT pg_sleep(3);
 pg_sleep 
----------
(1 row)

postgres=# SELECT CURRENT_TIMESTAMP;
    current_timestamp    
-------------------------------
 2019-09-28 12:43:57.075609+08
(1 row)

postgres=# COMMIT;
COMMIT

The two fetches in the transaction take the same time.

current statement start time

PostgreSQL also provides another fetch time function:


transaction_timestamp()
statement_timestamp()
clock_timestamp()
timeofday()
now()

transaction_timestamp()

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but has a more explicit effect.

statement_timestamp()

statement_timestamp() returns the start time of the current statement or, more accurately, the time the client received the latest command. statement_timestamp() and transaction_timestamp() return the same result for the first command in the transaction, but a subsequent execution of statement_timestamp() returns a different value.


postgres=# BEGIN;
BEGIN
postgres=# SELECT statement_timestamp();
   statement_timestamp   
-------------------------------
 2019-09-28 13:11:14.497135+08
(1 row)

postgres=# SELECT pg_sleep(3);
 pg_sleep 
----------
(1 row)

postgres=# SELECT statement_timestamp();
   statement_timestamp   
-----------------------------
 2019-09-28 13:11:17.5141+08
(1 row)

postgres=# COMMIT;
COMMIT

The difference between the two execution results is about 3 seconds.

When we debug in a stored procedure (Stored Procedure), we usually need to print the time of different statements. statement_timestamp() instead of CURRENT_TIMESTAMP or transaction_timestamp() :


CREATE OR REPLACE sp_test
...
DECLARE
 lts_systimestamp timestamp;
BEGIN;
 lts_systimestamp := statement_timestamp();
 ...
 RAISE NOTICE 'Step 1 take time: %', statement_timestamp() - lts_systimestamp;
 ...
END;

clock_timestamp()

clock_timestamp() returns the current actual time, and even within the same SQL statement may return a different value:


postgres=# SELECT clock_timestamp() FROM generate_series(1,10);
    clock_timestamp    
-------------------------------
 2019-09-28 13:18:55.659778+08
 2019-09-28 13:18:55.659786+08
 2019-09-28 13:18:55.659788+08
 2019-09-28 13:18:55.65979+08
 2019-09-28 13:18:55.659791+08
 2019-09-28 13:18:55.659793+08
 2019-09-28 13:18:55.659795+08
 2019-09-28 13:18:55.659797+08
 2019-09-28 13:18:55.659799+08
 2019-09-28 13:18:55.659801+08
(10 rows)

The query returned 10 records in 1 second, but each record took a different time to produce.

timeofday()

timeofday() is a legacy function in PostgreSQL. It returns the current actual time as clock_timestamp(), but the return type is a formatted string, not timestamp with time zone:


postgres=# SELECT timeofday() FROM generate_series(1,10);
       timeofday       
-------------------------------------
 Sat Sep 28 13:23:05.068541 2019 CST
 Sat Sep 28 13:23:05.068570 2019 CST
 Sat Sep 28 13:23:05.068577 2019 CST
 Sat Sep 28 13:23:05.068584 2019 CST
 Sat Sep 28 13:23:05.068591 2019 CST
 Sat Sep 28 13:23:05.068598 2019 CST
 Sat Sep 28 13:23:05.068605 2019 CST
 Sat Sep 28 13:23:05.068612 2019 CST
 Sat Sep 28 13:23:05.068619 2019 CST
 Sat Sep 28 13:23:05.068626 2019 CST
(10 rows)

now()

now() is a traditional function in PostgreSQL equivalent to transaction_timestamp() that does not change the result in the same transaction:


postgres=# BEGIN;
BEGIN
postgres=# SELECT now();
       now       
-------------------------------
 2019-09-28 13:27:26.831492+08
(1 row)

postgres=# SELECT pg_sleep(3);
 pg_sleep 
----------
(1 row)

postgres=# SELECT now();
       now       
-------------------------------
 2019-09-28 13:27:26.831492+08
(1 row)

postgres=# COMMIT;
COMMIT

In addition, all date/time data types support specifying the current date and time (the current transaction start time) using the literal 'now'. Therefore, the following statement has the same effect:


CURRENT_TIME
CURRENT_TIME(precision)
LOCALTIME
LOCALTIME(precision)

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP(precision)
LOCALTIMESTAMP
LOCALTIMESTAMP(precision)
0

By the way, PostgreSQL also provides several other special date and time literals:


CURRENT_TIME
CURRENT_TIME(precision)
LOCALTIME
LOCALTIME(precision)

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP(precision)
LOCALTIMESTAMP
LOCALTIMESTAMP(precision)
1

The above functions return UTC zero on January 1, 1970, midnight today, midnight tomorrow, midnight yesterday, and zero on UTC, respectively.

delays

The following function can be used to delay the operation of the server:


CURRENT_TIME
CURRENT_TIME(precision)
LOCALTIME
LOCALTIME(precision)

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP(precision)
LOCALTIMESTAMP
LOCALTIMESTAMP(precision)
2

pg_sleep suspends the current session for a specified number of seconds. seconds is of type double precision, so it supports fractional seconds. We used this function in front of us.

pg_sleep_for performs a delay interval, usually used to specify a large delay.

pg_sleep_until can be used to specify the wake time of a process.

The following examples pause for 1.5 seconds, 5 minutes, and until 3:00 tomorrow:


SELECT pg_sleep(1.5);
SELECT pg_sleep_for('5 minutes');
SELECT pg_sleep_until('tomorrow 03:00');

The precision of the pause time depends on the implementation of different platforms and can usually reach 0.01 seconds. The delay effect will at least satisfy the specified value, but may be longer due to other factors, such as excessive server load. In particular, for pg_sleep_until, there is no guarantee that the process will wake up at exactly the specified time, but it will not wake up earlier.

Note: when using these delay functions, ensure that the current session does not lock too many resources; Otherwise, the rest of the sessions will wait, resulting in a drop in system performance.


Related articles: