PostgreSQL Equivalent of SYSDATE
Shihab Sikder
May 06, 2022
This article discusses the PostgreSQL equivalent of Oracle’s SYSDATE.
PostgreSQL System Time
In PostgreSQL, there’s a list of functions that returns system time. Some function returns the time with a timestamp, precision, etc.
Here’s a list of the functions that provide system date and time.
| Function | Description |
|---|---|
CURRENT_DATE |
Returns only date in YYYY-MM-DD format |
CURRENT_TIME |
Returns system time in HH:MM:SS.SSS format |
CURRENT_TIMESTAMP |
Returns date and time combined |
CURRENT_TIME(precision) |
Same as CURRENT_TIME with defined precision |
CURRENT_TIMESTAMP(precision) |
Same as CURRENT_TIMESTAMP with defined precision |
LOCALTIME |
Returns only time without any timezone |
LOCALTIMESTAMP |
Returns date and time without timezone |
LOCALTIME(precision) |
Same as LOCALTIME with defined precision |
LOCALTIMESTAMP(precision) |
Same as LOCALTIME with defined precision |
clock_timestamp() |
Returns date and time from system clock |
timeofday() |
Returns date and time |
now() |
Returns date and time |
Here’s what each of these commands will look like.
CURRENT_DATE
postgres=# select CURRENT_DATE;
current_date
--------------
2022-04-21
(1 row)
CURRENT_TIME
postgres=# select CURRENT_TIME;
current_time
--------------------
10:00:11.047684+06
(1 row)
CURRENT_TIMESTAMP
postgres=# select CURRENT_TIMESTAMP;
current_timestamp
------------------------------
2022-04-21 10:01:01.67809+06
(1 row)
CURRENT_TIME(precision)
postgres=# select CURRENT_TIME(3);
current_time
-----------------
10:01:29.618+06
(1 row)
CURRENT_TIMESTAMP(precision)
postgres=# select CURRENT_TIMESTAMP(3);
current_timestamp
----------------------------
2022-04-21 10:02:18.001+06
(1 row)
LOCALTIME
postgres=# SELECT LOCALTIME;
localtime
-----------------
10:02:34.459566
(1 row)
LOCALTIMESTAMP
postgres=# SELECT LOCALTIMESTAMP;
localtimestamp
----------------------------
2022-04-21 10:03:04.678772
(1 row)
clock_timestamp()
postgres=# SELECT clock_timestamp();
clock_timestamp
------------------------------
2022-04-21 10:06:30.42038+06
(1 row)
timeofday()
postgres=# SELECT timeofday();
timeofday
-------------------------------------
Thu Apr 21 10:07:13.660494 2022 +06
(1 row)
now()
postgres=# SELECT now();
now
-------------------------------
2022-04-21 10:07:50.340605+06
(1 row)
To know more about DATETIME and other functionality of the date-time, visit the official documentation.
Author: Shihab Sikder
