Selecting based on time difference in SQL-CodePudding

I am using a timer task to execute a subset of tasks including a query for my PostgreSQL database. The query that I have is as follows:

SELECT idntfr 
FROM mmny 
WHERE DATEDIFF(HOUR, begin_dt, $1) >= 8

In this query I am trying to get the idntfr values where the difference between the begin_dt and the value that I am passing in as $1, is greater than 8 hours.

The error I get is:

ERROR: column "hour" does not exist

It would be great if anyone can tell me what I am doing wrong.

CodePudding user response：

In Postgresql, I guess I need to use date_part instead of datediff

CodePudding user response：

As documented in the manual there is no datediff() function in Postgres. Assuming that $1 is a timestamp value and you want to check if the difference between that timestamp and begin_dt is greater than 8 hours, you simply subract the two and compare the result:

where begin_dt - $1 > interval '8 hours'