How to get the current date and time from PostgreSQL and precautions

Time:2019-12-14

When developing database applications or debugging code, you often need to get the current date and time of the system. Let’s take a look at the related functions provided in PostgreSQL.

current date

CURRENT_DATE

The current date function gets the current date of the database server:


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

The function name does not need to be followed by parentheses when it is called. The date is the server’s date, not the client’s date.

Current transaction start time

The following functions 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 time stamp; current “time” and current “timestamp contain the time zone information, while Localtime and localtimestamp do not contain the time zone information. Precision is used to specify the number of decimal seconds. The value is 0 – 6. The default value is 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)

Be careful:All the above functions, including current date, return the start time of the current transaction. During the same transaction, multiple calls to the same function will return the same value, and the result will not increase over time. This may be different from the implementation of other databases.

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

It takes the same time to get two times in a transaction.

Current statement start time

PostgreSQL also provides other functions to get time:


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

transaction_timestamp()

Transaction_timestamp() is equivalent to current_timestamp, but it is more specific.

statement_timestamp()

Statement [timestamp() returns the start time of the current statement. More precisely, it should be the time when the latest command from the client is received. Statement \ u timestamp() and transaction \ u timestamp() return the same results for the first command in the transaction, but subsequent execution of statement \ u timestamp() will return different values.


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 stored procedure, we usually need to print the time consumed by different statements; in this case, we need to use 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. Even in the same SQL statement, it may return different values:


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 statement returned 10 records in one second, but each record was generated at a different time.

timeofday()

Timeofday() is a historical 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 equivalent to transaction_timestamp() in PostgreSQL. The result in the same transaction will not change:


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 the use of literal ‘now’ to specify the current date and time (current transaction start time). Therefore, the following statements have the same effect:

SELECT CURRENT_TIMESTAMP;
SELECT now();
Select timestamp 'now'; -- do not use default value for field

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


-- SELECT timestamp 'epoch', timestamp 'today', timestamp 'tomorrow', timestamp 'yesterday', TIME 'allballs';
postgres=# SELECT DATE 'epoch', DATE 'today',DATE 'tomorrow', DATE 'yesterday', TIME 'allballs';
  date  |  date  |  date  |  date  |  time  
------------+------------+------------+------------+----------
 1970-01-01 | 2019-09-28 | 2019-09-29 | 2019-09-27 | 00:00:00
(1 row)

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

Delayed execution

The following functions can be used to delay operations by the server:


pg_sleep(seconds)
pg_sleep_for(interval)
pg_sleep_until(timestamp with time zone)

PG sleep pauses the current session for the specified number of seconds. The type of seconds is double precision, so decimal seconds are supported. We used this function in front of us.

PG ﹣ sleep ﹣ for the execution of a delay interval, usually used to specify a large delay.

PG sleep until can be used to specify the wake-up time of a process.

The following examples pause for 1.5 seconds, 5 minutes, and until 3 o’clock tomorrow:


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

The accuracy of pause time depends on the implementation of different platforms, which can usually reach 0.01 seconds. The delay effect will at least meet the specified value, but it may be longer due to other factors, such as high server load. Especially for PG sleep until, it is not guaranteed to wake up the process at a completely accurate time, but it will not wake up in advance.

Note: when using these delay functions, make sure that the current session does not lock too many resources; otherwise, other sessions will wait all the time, resulting in a decrease in system performance.

The above is the whole content of this article. I hope it will help you in your study, and I hope you can support developepaer more.