PostgreSQL Equivalent of SYSDATE

Shihab Sikder May 06, 2022
PostgreSQL Equivalent of SYSDATE

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.

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website

Related Article - PostgreSQL Date