CODEWITHSUNDEEP
sqltimestampdata-analysispresto
Chris90
Chris90

Reputation: 1998

SQL - I want to get the timestamps in query in 30 minute intervals

I have a query such as below, 

    SELECT DISTINCT table_1.id,
       table_1.time_utc
       table_1.city_uuid,
       cast(table_2.score_rate as decimal(5,3)) as score_rate
FROM integrated_delivery.trip_table_1_fact table_1,
     integrated_product.driver_score_v2 table_2
WHERE table_1.id = table_2.id
  AND table_1.city_uuid = table_2.city_id
  AND table_1.day = date '2019-04-01'
  AND table_2.extract_dt = 20190331
  AND EXISTS
    (SELECT NULL
     FROM table_3
     WHERE table_1.id = table_3.id
       AND table_1.time_utc >= table_3.start_time_utc
       AND table_1.time_utc <= table_3.end_time_utc)

I want to alter this query where it returns table_1.offer_time_utc in 30 min intervals.

Table_1 looks sample row looks like

       id               time_utc    
b7-19b36a410ab0  2019-04-16 22:00:09.415
53-9127667e288e  2019-04-17 01:06:16.590
6b-a96c3ea196c4  2019-04-16 22:00:09.908

Table_3 looks like 

           id          start_time_utc       end_time_utc    
35-e512d080e5d3 2019-01-29 02:00:00.000 2019-01-29 03:30:00.000
94-07e7036c1e4b 2019-01-29 01:30:00.000 2019-01-29 02:30:00.000
7d-20736d277064 2019-01-29 01:00:00.000 2019-01-29 03:30:00.000

Where I want to tweak the above query so it pulls all records or rows in 30 minute intervals and also has a column that represents that interval?

Something like 

  interval       
-------------------    
2010-11-16 10:30:00  
2010-11-16 10:35:00
2010-11-16 10:40:00   
2010-11-16 10:45:00
2010-11-16 10:50:00   
2010-11-16 10:55:00

Expected output would basically be what I already have in table_1 sample but in intervals like:

Id               Interval     ( time_utc)
b7-19b36a410ab0  2010-11-16 10:30:00  
53-9127667e288e  2010-11-16 11:00:00
6b-a96c3ea196c4  2010-11-16 11:30:00

Thanks!

Upvotes: 0

Views: 2044

Answers (2)

Ancoron
Ancoron

Reputation: 2733

TL;DR

The following construct generates the lower 30-minute boundary for any timestamp:

date_trunc('hour', table_1.time_utc) + (
    CASE
        WHEN (extract(minute from table_1.time_utc) >= 30) THEN
            '30 minutes'::interval
        ELSE
            '0'::interval
    END
)

Long Version

Applied to your case:

SELECT DISTINCT table_1.id,
       table_1.time_utc,
       date_trunc('hour', table_1.time_utc) + CASE
           WHEN (extract(minute from table_1.time_utc) >= 30) THEN '30 minutes'::interval
           ELSE '0'::interval
       END AS time_utc_aligned,
       table_1.city_uuid,
       cast(table_2.score_rate as decimal(5,3)) as score_rate
FROM integrated_delivery.trip_table_1_fact table_1,
     integrated_product.driver_score_v2 table_2
WHERE table_1.id = table_2.id
      AND table_1.city_uuid = table_2.city_id
      AND table_1.day = date '2019-04-01'
      AND table_2.extract_dt = 20190331
      AND EXISTS (
        SELECT NULL
        FROM table_3
        WHERE table_1.id = table_3.id
              AND table_1.time_utc >= table_3.start_time_utc
              AND table_1.time_utc <= table_3.end_time_utc
      )
;

...which would produce (with test data):

       id        |        time_utc         |  time_utc_aligned   |              city_uuid               | score_rate 
-----------------+-------------------------+---------------------+--------------------------------------+------------
 53-9127667e288e | 2019-04-17 01:06:16.59  | 2019-04-17 01:00:00 | 909153dc-c1ff-4e65-a32e-c9194ddfbec9 |      4.662
 6b-a96c3ea196c4 | 2019-04-16 22:00:09.908 | 2019-04-16 22:00:00 | b2d402a2-ba2d-483b-a4c0-fae95ee1700c |      2.250
 b7-19b36a410ab0 | 2019-04-16 22:00:09.415 | 2019-04-16 22:00:00 | 889f9aed-f399-4059-b97b-d67b0af0096d |      1.744

If you have the timescale extension, it becomes much more readable with their time_bucket C function:

SELECT DISTINCT table_1.id,
       table_1.time_utc,
       time_bucket('30 minutes', table_1.time_utc) AS time_utc_aligned,
       table_1.city_uuid,
       cast(table_2.score_rate as decimal(5,3)) as score_rate
FROM integrated_delivery.trip_table_1_fact table_1,
     integrated_product.driver_score_v2 table_2
WHERE table_1.id = table_2.id
      AND table_1.city_uuid = table_2.city_id
      AND table_1.day = date '2019-04-01'
      AND table_2.extract_dt = 20190331
      AND EXISTS (
        SELECT NULL
        FROM table_3
        WHERE table_1.id = table_3.id
              AND table_1.time_utc >= table_3.start_time_utc
              AND table_1.time_utc <= table_3.end_time_utc
      )
;

Upvotes: 1

jose_bacoy
jose_bacoy

Reputation: 12704

I would use a common table expression (CTE) and create an interval of datetime every 30minutes. You can view my sample data in dbfiddle: http://www.sqlfiddle.com/#!4/bf5a7/18

    WITH interval_dates as
    (select  timestamp '2019-04-16 00:00:00' 
                    + NUMTODSINTERVAL(30*rownum-30,'MINUTE')  as from_interval,
     timestamp '2019-04-16 00:00:00' 
                    + NUMTODSINTERVAL(30*rownum,'MINUTE') as to_interval
     from dual connect by level <= 2000)
    select t1.*, dt.from_interval
    from interval_dates dt  
    ,(SELECT DISTINCT table_1.id,
       table_1.time_utc
       table_1.city_uuid,
       cast(table_2.score_rate as decimal(5,3)) as score_rate
FROM integrated_delivery.trip_table_1_fact table_1,
     integrated_product.driver_score_v2 table_2
WHERE table_1.id = table_2.id
  AND table_1.city_uuid = table_2.city_id
  AND table_1.day = date '2019-04-01'
  AND table_2.extract_dt = 20190331
  AND EXISTS
    (SELECT NULL
     FROM table_3
     WHERE table_1.id = table_3.id
       AND table_1.time_utc >= table_3.start_time_utc
       AND table_1.time_utc <= table_3.end_time_utc)) t1
    where t1.time_utc >= dt.from_interval and t1.time_utc < dt.to_interval

Sample result:

ID              TIME_UTC                FROM_INTERVAL
b7-19b36a410ab0 2019-04-16 22:00:09.0   2019-04-16 22:00:00.0
6b-a96c3ea196c4 2019-04-16 22:00:09.0   2019-04-16 22:00:00.0
53-9127667e288e 2019-04-17 01:06:16.0   2019-04-17 01:00:00.0

Upvotes: 1

Related Questions