'How to calculate the time difference in SQL with DATEDIFF?

I am using the DATEDIFF function to calculate the difference between my two timestamps.

payment_time = 2021-10-29 07:06:32.097332
trigger_time = 2021-10-10 14:11:13

What I have written is : date_diff('minute',payment_time,trigger_time) <= 15 I basically want the count of users who paid within 15 mins of the triggered time thus I have also done count(s.user_id) as count However it returns count as 1 even in the above case since the minutes are within 15 but the dates 10th October and 29th October are 19 days apart and hence it should return 0 or not count this row in my query.

How do I compare the dates in my both columns and then count users who have paid within 15 mins?

sqlpostgresqldatetimedatediff


Solution 1:[1]

In PostgreSQL, I prefer to subtract the two timestamps from each other, and extract the epoch from the resulting interval:

Like here:

WITH                                                                                                                                                 
indata(payment_time,trigger_time) AS (
          SELECT TIMESTAMP '2021-10-29 07:06:32.097332',TIMESTAMP '2021-10-10 14:11:13'
UNION ALL SELECT TIMESTAMP '2021-10-29 00:00:14'       ,TIMESTAMP '2021-10-29 00:00:00'
)
SELECT 
  EXTRACT(EPOCH FROM payment_time-trigger_time) AS epdiff 
, (EXTRACT(EPOCH FROM payment_time-trigger_time) <= 15) AS filter_matches
FROM indata;
-- out      epdiff     | filter_matches 
-- out ----------------+----------------
-- out  1616119.097332 | false
-- out       14.000000 | true

Solution 2:[2]

This also works to calculate minutes between to timestamps (it first finds the interval (subtraction), and then converts that to seconds (extracting EPOCH), and divides by 60:

 extract(epoch from (payment_time-trigger_time))/60

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 marcothesane
Solution 2 tinazmu

Related Questions