How to subtract two timestamps in SQL and then count?

Question

I want to basically find out how many users paid within 15 mins, 30 mins and 60 mins of my payment_time and trigger_time I have the following query

with redshift_direct() as conn:
            trigger_time_1 = pd.read_sql(f"""
            with new_data as
            (
            select
            cycle_end_date
            , prime_tagging_by_issuer_and_product
            , u.user_id
            , settled_status
            , delay,
            ots_created_at + interval '5:30 hours' as payment_time
            ,case when to_char(cycle_end_date,'DD') = '15' then 'Odd' else 'Even' end as cycle_order

            from
            settlement_summary_from_snapshot s
            left join (select distinct user_phone_number, user_id from user_events where event_name = 'UserCreatedEvent') u
            on u.user_id = s.user_id
            and cycle_type = 'end_cycle'
            and cycle_end_date > '2021-11-30' and cycle_end_date < '2022-01-15'
            )
            select
            bucket_id
            , cycle_end_date, d.cycle_order
            , date(cycle_end_date) as t_cycle_end_date
            ,d.prime_tagging_by_issuer_and_product
            ,source
            ,status as cause
            ,split_part(campaign_name ,'|', 1) as campaign
            ,split_part(campaign_name ,'|', 2) as sms_cycle_end_date
            ,split_part(campaign_name ,'|', 3) as day
            ,split_part(campaign_name ,'|', 4) as type
            ,to_char(to_date(split_part(campaign_name ,'|', 2) , 'DD/MM/YYYY'), 'YYYY-MM-DD') as campaign_date,
            d.payment_time, payload_event_timestamp  + interval '5:30 hours' as trigger_time
           ,count( s.user_id) as count
            from sms_callback_events s
            inner join new_data d
            on s.user_id = d.user_id
            where bucket_id > 'date_2021_11_30' and bucket_id < 'date_2022_01_15'
            and campaign_name like '%RC%'
            and event_name = 'SmsStatusUpdatedEvent'
            group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14
            """,conn)

How do i achieve making 3 columns with number of users who paid within 15mins, 30 mins and 60 mins after trigger_time in this query? I was doing it with Pandas but I want to find a way to do it here itself. Can someone help?

Please edit your pots and ddd table description, sample datas and expected result — Philippe, Feb 15 '22 at 16:09

score 0 · Answer 1 · answered Feb 24 '22 at 03:34

I wrote my own DATEDIFF function, which returns an integer value of differencing between two dates, difference by day, by month, by year, by hour, by minute and etc. You can use this function on your queries.

DATEDIFF Function SQL Code on GitHub

Sample Query about using our DATEDIFF function:

select 
    datediff('minute', mm.start_date, mm.end_date) as diff_minute
from 
    (
        select 
            '2022-02-24 09:00:00.100'::timestamp as start_date, 
            '2022-02-24 09:15:21.359'::timestamp as end_date 
    ) mm;



Result: 
---------------
  diff_minute 
---------------
 15
---------------

How to subtract two timestamps in SQL and then count?

1 Answers1