What is PostgreSQL equivalent of SYSDATE from Orac

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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

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

Answer 4

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