When developing database applications or debugging code, you often need to obtain the current date and time of the system. Let’s take a look at the relevant 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)
There is no need to add brackets after the function name when calling this function. This date is the date of the server, not the date of the client.
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 4 functions are used to get time, and the next 4 functions are used to get time stamps; CURRENT_TIME and CURRENT_TIMESTAMP contain time zone information, while LOCALTIME and LOCALTIMESTAMP do not contain time zone information. precision is used to specify the number of digits in a fraction of seconds, with the value 0 - 6, and the default 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)
Notice:All the functions above, including CURRENT_DATE, return the time when the current transaction starts. 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 pauses 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 time of two acquisitions in the transaction is the same.
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 its function is more clear.
statement_timestamp()
statement_timestamp() returns the start time of the current statement, and more accurately, it should be the time when the client's latest command is received. statement_timestamp() and transaction_timestamp() return the same result for the first command in the transaction, but then executing statement_timestamp() will return 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 results of the two executions is about 3 seconds.
When we debug in a stored procedure (Stored Procedure), we usually need to print the time consumed by different statements; at this time, 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, and may return different values even in the same SQL statement:
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 returns 10 records in 1 second, but the time each record is generated is different.
timeofday()
timeofday() is a historical legacy function in PostgreSQL. It returns the current actual time just like 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 that is equivalent to transaction_timestamp(), and the results 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
Additionally, all date/time data types support specifying the current date and time (current transaction start time) with the literal 'now'. Therefore, the following statements have the same effect:
SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now'; -- Do not use for fields DEFAULT value
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 to UTC on January 1, 1970, midnight today, midnight tomorrow, midnight yesterday, and midnight UTC respectively.
Delayed execution
The following functions can be used to delay operations performed 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 it supports decimal seconds. We used this function in front of us.
pg_sleep_for executes a delayed interval, usually used to specify a larger 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 the pause time depends on the implementation of different platforms and can usually reach 0.01 seconds. The delay effect will satisfy the specified value at least, but it may result in longer due to other factors, such as excessive server load. Especially for pg_sleep_until, it is not guaranteed to wake up the process at a specified time at a completely accurate specified 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, resulting in a degradation in system performance.
The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.