Teradata: Breaking Timestamp Group

Pradeep

Teradata: Breaking Timestamp Group

This tutorial will mostly cover breaking a timestamp range into 1 hour interval but similar logic can be used to break timestamp range into as small or larger intervals as required.

2 things are required to achieve the required result.

Interval Metadata Table.
Driver Table for the required dates.

Interval Metadata Table : Interval table tutorial_db.time_interval in this case is for 1 hour Interval, it can be defined for any grain.


Table Structure
===============
CREATE MULTISET TABLE tutorial_db.time_interval  
(
   tm_interval_60_min VARCHAR(30), 
   tm_interval_id INTEGER,
   start_interval_tm VARCHAR(10),
   end_interval_tm VARCHAR(10)
) PRIMARY INDEX(tm_interval_id);


Interval metadata Dmls
======================
INSERT INTO tutorial_db.time_interval  VALUES ('0000 - 0100',1,'00:00:00','00:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('0100 - 0200',2,'01:00:00','01:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('0200 - 0300',3,'02:00:00','02:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('0300 - 0400',4,'03:00:00','03:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('0400 - 0500',5,'04:00:00','04:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('0500 - 0600',6,'05:00:00','05:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('0600 - 0700',7,'06:00:00','06:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('0700 - 0800',8,'07:00:00','07:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('0800 - 0900',9,'08:00:00','08:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('0900 - 1000',10,'09:00:00','09:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('1000 - 1100',11,'10:00:00','10:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('1100 - 1200',12,'11:00:00','11:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('1200 - 1300',13,'12:00:00','12:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('1300 - 1400',14,'13:00:00','13:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('1400 - 1500',15,'14:00:00','14:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('1500 - 1600',16,'15:00:00','15:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('1600 - 1700',17,'16:00:00','16:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('1700 - 1800',18,'17:00:00','17:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('1800 - 1900',19,'18:00:00','18:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('1900 - 2000',20,'19:00:00','19:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('2000 - 2100',21,'20:00:00','20:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('2100 - 2200',22,'21:00:00','21:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('2200 - 2300',23,'22:00:00','22:59:59');
INSERT INTO tutorial_db.time_interval  VALUES ('2300 - 0000',24,'23:00:00','23:59:59');


Overview of data in Metadata
============================
SELECT * FROM tutorial_db.time_interval ORDER BY 2;

tm_interval_60_min   tm_interval_id  start_interval_tm  end_interval_tm
-------------------  --------------  -----------------  ---------------
0000 - 0100                       1  00:00:00           00:59:59
0100 - 0200                       2  01:00:00           01:59:59
0200 - 0300                       3  02:00:00           02:59:59
0300 - 0400                       4  03:00:00           03:59:59
0400 - 0500                       5  04:00:00           04:59:59
0500 - 0600                       6  05:00:00           05:59:59
0600 - 0700                       7  06:00:00           06:59:59
0700 - 0800                       8  07:00:00           07:59:59
0800 - 0900                       9  08:00:00           08:59:59
0900 - 1000                      10  09:00:00           09:59:59
1000 - 1100                      11  10:00:00           10:59:59
1100 - 1200                      12  11:00:00           11:59:59
1200 - 1300                      13  12:00:00           12:59:59
1300 - 1400                      14  13:00:00           13:59:59
1400 - 1500                      15  14:00:00           14:59:59
1500 - 1600                      16  15:00:00           15:59:59
1600 - 1700                      17  16:00:00           16:59:59
1700 - 1800                      18  17:00:00           17:59:59
1800 - 1900                      19  18:00:00           18:59:59
1900 - 2000                      20  19:00:00           19:59:59
2000 - 2100                      21  20:00:00           20:59:59
2100 - 2200                      22  21:00:00           21:59:59
2200 - 2300                      23  22:00:00           22:59:59
2300 - 0000                      24  23:00:00           23:59:59

Driver Table for the required dates : For tutorial purpose, dates are kept between 1st Oct & 5th Oct and transaction data is set between these days. User can populate table for extended days as per the transactional data.


SET SESSION DATEFORM = ANSIDATE ;

CREATE MULTISET TABLE tutorial_db.fiscal_hourly_inter_day AS
(
  SELECT (calendar_date (FORMAT 'YYYY-MM-DD')) AS calendar_dt,
         tm_interval_id,
         start_interval_tm,
         end_interval_tm,
         CAST((calendar_dt  ||' '||start_interval_tm) AS TIMESTAMP(0)) start_interval_ts,
         CAST((calendar_dt||' '||end_interval_tm) AS TIMESTAMP(0))  end_interval_ts
  FROM tutorial_db.time_interval a ,sys_calendar.calendar b 
  WHERE calendar_date BETWEEN '2020-10-01' AND '2020-10-05'
)
WITH DATA;
 


Overview of data in Driver Table
================================
SELECT * FROM tutorial_db.fiscal_hourly_inter_day WHERE calendar_dt='2020-10-02' ORDER BY 2;

calendar_dt  tm_interval_id  start_interval_tm  end_interval_tm    start_interval_ts      end_interval_ts
-----------  --------------  -----------------  ---------------  -------------------  -------------------
 2020-10-02               1  00:00:00           00:59:59         2020-10-02 00:00:00  2020-10-02 00:59:59
 2020-10-02               2  01:00:00           01:59:59         2020-10-02 01:00:00  2020-10-02 01:59:59
 2020-10-02               3  02:00:00           02:59:59         2020-10-02 02:00:00  2020-10-02 02:59:59
 2020-10-02               4  03:00:00           03:59:59         2020-10-02 03:00:00  2020-10-02 03:59:59
 2020-10-02               5  04:00:00           04:59:59         2020-10-02 04:00:00  2020-10-02 04:59:59
 2020-10-02               6  05:00:00           05:59:59         2020-10-02 05:00:00  2020-10-02 05:59:59
 2020-10-02               7  06:00:00           06:59:59         2020-10-02 06:00:00  2020-10-02 06:59:59
 2020-10-02               8  07:00:00           07:59:59         2020-10-02 07:00:00  2020-10-02 07:59:59
 2020-10-02               9  08:00:00           08:59:59         2020-10-02 08:00:00  2020-10-02 08:59:59
 2020-10-02              10  09:00:00           09:59:59         2020-10-02 09:00:00  2020-10-02 09:59:59
 2020-10-02              11  10:00:00           10:59:59         2020-10-02 10:00:00  2020-10-02 10:59:59
 2020-10-02              12  11:00:00           11:59:59         2020-10-02 11:00:00  2020-10-02 11:59:59
 2020-10-02              13  12:00:00           12:59:59         2020-10-02 12:00:00  2020-10-02 12:59:59
 2020-10-02              14  13:00:00           13:59:59         2020-10-02 13:00:00  2020-10-02 13:59:59
 2020-10-02              15  14:00:00           14:59:59         2020-10-02 14:00:00  2020-10-02 14:59:59
 2020-10-02              16  15:00:00           15:59:59         2020-10-02 15:00:00  2020-10-02 15:59:59
 2020-10-02              17  16:00:00           16:59:59         2020-10-02 16:00:00  2020-10-02 16:59:59
 2020-10-02              18  17:00:00           17:59:59         2020-10-02 17:00:00  2020-10-02 17:59:59
 2020-10-02              19  18:00:00           18:59:59         2020-10-02 18:00:00  2020-10-02 18:59:59
 2020-10-02              20  19:00:00           19:59:59         2020-10-02 19:00:00  2020-10-02 19:59:59
 2020-10-02              21  20:00:00           20:59:59         2020-10-02 20:00:00  2020-10-02 20:59:59
 2020-10-02              22  21:00:00           21:59:59         2020-10-02 21:00:00  2020-10-02 21:59:59
 2020-10-02              23  22:00:00           22:59:59         2020-10-02 22:00:00  2020-10-02 22:59:59
 2020-10-02              24  23:00:00           23:59:59         2020-10-02 23:00:00  2020-10-02 23:59:59

Sample Transaction Data


Transaction Table:
=================
CREATE MULTISET TABLE  tutorial_db.timestamp_breaking
(
id Integer,
t1_start_ts TIMESTAMP(0),
t1_end_ts TIMESTAMP(0)
)
PRIMARY INDEX(id);


Sample Data:
=============
INSERT INTO tutorial_db.timestamp_breaking VALUES(1, '2020-10-01 00:00:01', '2020-10-01 00:50:00');
INSERT INTO tutorial_db.timestamp_breaking VALUES(2, '2020-10-01 01:00:01', '2020-10-01 02:50:00');
INSERT INTO tutorial_db.timestamp_breaking VALUES(3, '2020-10-01 03:00:01', '2020-10-01 05:50:00');
INSERT INTO tutorial_db.timestamp_breaking VALUES(4, '2020-10-01 23:10:01', '2020-10-02 00:50:00');
INSERT INTO tutorial_db.timestamp_breaking VALUES(5, '2020-10-02 23:10:01', '2020-10-04 00:50:00');


Overview of data
=================
SELECT * FROM tutorial_db.timestamp_breaking ORDER BY 1;

 id          t1_start_ts            t1_end_ts
---  -------------------  -------------------
  1  2020-10-01 00:00:01  2020-10-01 00:50:00
  2  2020-10-01 01:00:01  2020-10-01 02:50:00
  3  2020-10-01 03:00:01  2020-10-01 05:50:00
  4  2020-10-01 23:10:01  2020-10-02 00:50:00

Required Logic and explanation


Required Query:
==============
SELECT trans.id, 
       trans.t1_start_ts, 
       trans.t1_end_ts,  
       CASE WHEN trans.t1_start_ts > driver.start_interval_ts THEN trans.t1_start_ts 
            ELSE driver.start_interval_ts
       END new_start_interval_ts, 
       CASE WHEN trans.t1_end_ts < driver.end_interval_ts THEN trans.t1_end_ts 
            ELSE driver.end_interval_ts
       END new_end_interval_ts
FROM tutorial_db.timestamp_breaking trans, tutorial_db.fiscal_hourly_inter_day driver
WHERE driver.calendar_dt BETWEEN trans.t1_start_ts(DATE) AND trans.t1_end_ts(date)
AND (trans.t1_start_ts, trans.t1_end_ts) OVERLAPS (driver.start_interval_ts, driver.end_interval_ts)
ORDER BY trans.id, new_start_interval_ts;


Required Output:
================
 id          t1_start_ts            t1_end_ts  new_start_interval_ts  new_end_interval_ts
---  -------------------  -------------------  ---------------------  -------------------
  1  2020-10-01 00:00:01  2020-10-01 00:50:00    2020-10-01 00:00:01  2020-10-01 00:50:00
  2  2020-10-01 01:00:01  2020-10-01 02:50:00    2020-10-01 01:00:01  2020-10-01 01:59:59
  2  2020-10-01 01:00:01  2020-10-01 02:50:00    2020-10-01 02:00:00  2020-10-01 02:50:00
  3  2020-10-01 03:00:01  2020-10-01 05:50:00    2020-10-01 03:00:01  2020-10-01 03:59:59
  3  2020-10-01 03:00:01  2020-10-01 05:50:00    2020-10-01 04:00:00  2020-10-01 04:59:59
  3  2020-10-01 03:00:01  2020-10-01 05:50:00    2020-10-01 05:00:00  2020-10-01 05:50:00
  4  2020-10-01 23:10:01  2020-10-02 00:50:00    2020-10-01 23:10:01  2020-10-01 23:59:59
  4  2020-10-01 23:10:01  2020-10-02 00:50:00    2020-10-02 00:00:00  2020-10-02 00:50:00

Product join is happening between driver table and transaction table to identify interval for breaking the timestamp. Date condition(marked in blue) has been added to limit the product join impact to the involved dates only.

(t1_start_ts, t1_end_ts) OVERLAPS (start_interval_ts, end_interval_ts) used in above query translates into below logic in Teradata


(t1_start_ts > start_interval_ts AND NOT (t1_start_ts >= end_interval_ts AND t1_end_ts >= end_interval_ts))
 OR
(start_interval_ts > t1_start_ts AND NOT (start_interval_ts >= t1_end_ts AND end_interval_ts >= t1_end_ts))
 OR
(t1_start_ts = start_interval_ts AND (t1_end_ts = end_interval_ts OR t1_end_ts <> end_interval_ts))

This tutorial will mostly cover breaking a timestamp range into intervals .

dbmstutorials.com

Teradata: Breaking Timestamp Group