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.

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - PostgreSQL Date

  • Cast String to Date DD/MM/YYYY in PostgreSQL
  • Subtract a Day From a Timestamp Date in PostgreSQL
  • Extract Day of Week From Date Field in PostgreSQL