How to calculate the time difference in SQL with DATEDIFF?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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