Teradata Date Addition/Subtraction
Today when I started designing BI layer for a session authentication system (RADIUS), I realized that there are two types of CDRs
- Start of a session CDR
- Stop of a session CDR
Interestingly, STOP CDRs have no Start date information. Therefore, I had to subtract session duration for the stop date to get start date of a session.
Problem: To subtract duration (in seconds) from a date (session stop date) to get actual start date of a session
Solution:
Although, it appears to be a simple riddle but in Teradata, this can be a little bit tricky, specially when you are not comfortable working with dates and intervals. Following code snippet explains that how it is done.
CAST(START_DATE_TIME - CAST((SESSION_DURATION/60/60/24/28/12) AS INTERVAL YEAR) - CAST((SESSION_DURATION/60/60/24/28 MOD 12) AS INTERVAL MONTH) - CAST((SESSION_DURATION/60/60/24 MOD 28) AS INTERVAL DAY) - CAST((SESSION_DURATION/60/60 MOD 24) AS INTERVAL HOUR) - CAST((SESSION_DURATION/60 MOD 60) AS INTERVAL MINUTE) - CAST((SESSION_DURATION MOD 60) AS INTERVAL SECOND) AS DATE FORMAT 'YYYY-MM-DD') AS START_DATE
Point 1:
When dealing with huge number of seconds, their is a limitation of casting it to seconds. Running the following query will result in 'Interval field overflow':
SELECT CAST(30000 AS INTERVAL SECOND(4) )
Therefore, to add/subtract huge number of seconds, conversion to hour, minute and seconds is required.
Point 2:
To successfully cast above, we may need to convert it into hours
SELECT CAST(30000/60/60 AS INTERVAL HOUR)
It will result in 8 hours.
Point 3:
Converting 8 hours back to seconds will not give 30000. Teradata INTERVAL does not maintain precision information. Hence, to keep track of exact seconds, MOD should be used and added up to get precise number of seconds. Below example explains how it can be done.
SELECT CAST(30000/60/60 AS INTERVAL HOUR) AS hrs ,CAST(((30000/60) MOD 60) AS INTERVAL MINUTE) AS mins ,CAST((30000 MOD 60) AS INTERVAL SECOND) AS secs , hrs+mins+secs