[oracle] What is PostgreSQL equivalent of SYSDATE from Oracle?

I want to perform a query using sysdate like:

select up_time from exam where up_time like sysdate

which is possible in Oracle.

However, it seems that PostgreSQL doesn't support sysdate. I couldn't find sysdate in postgres documentation. What is the replacement for sysdate in PostgreSQL?

This question is related to oracle postgresql

The answer is


SYSDATE is an Oracle only function.

The ANSI standard defines current_date or current_timestamp which is supported by Postgres and documented in the manual:
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

(Btw: Oracle supports CURRENT_TIMESTAMP as well)

You should pay attention to the difference between current_timestamp, statement_timestamp() and clock_timestamp() (which is explained in the manual, see the above link)


This statement:

select up_time from exam where up_time like sysdate

Does not make any sense at all. Neither in Oracle nor in Postgres. If you want to get rows from "today", you need something like:

select up_time 
from exam 
where up_time = current_date

Note that in Oracle you would probably want trunc(up_time) = trunc(sysdate) to get rid of the time part that is always included in Oracle.


You may want to use statement_timestamp(). This give the timestamp when the statement was executed. Whereas NOW() and CURRENT_TIMESTAMP give the timestamp when the transaction started.

More details in the manual


NOW() is the replacement of Oracle Sysdate in Postgres.

Try "Select now()", it will give you the system timestamp.


The following functions are available to obtain the current date and/or time in PostgreSQL:

CURRENT_TIME

CURRENT_DATE

CURRENT_TIMESTAMP

Example

SELECT CURRENT_TIME;
08:05:18.864750+05:30

SELECT CURRENT_DATE;
2020-05-14

SELECT CURRENT_TIMESTAMP;
2020-05-14 08:04:51.290498+05:30

postgresql docs