For some requirements it might be required to show 24 hours interval in hour ranges,example : 3 -> 03:00-04:00.

Teradata: Convert Hour to Hour Range

For some requirements it might be required to show 24 hours interval in hour ranges,example : 3 -> 03:00-04:00. Specially this is used for cases to view pattern/performance over different hour of the day.

Different ways can be used to achieve this.

➠ Approach 1 : This can be achieved using Teradata Interval functions in a Simplest way

Assume value of CURRENT_TIMESTAMP(0) is 2016-11-11 23:33:22

SELECT CAST(CAST( CURRENT_TIMESTAMP(0) AS FORMAT 'HH') AS VARCHAR(2)) ||':00-'|| 
       CAST(CAST(( CURRENT_TIMESTAMP(0) + INTERVAL '01' HOUR) AS FORMAT 'HH') AS VARCHAR(2))||':00' ;
 
Output: 23:00-00:00

➠ Approach 2 : Other way to achive this, is by using Teradata EXTRACT HOUR function and create String in required format

Assume value of CURRENT_TIMESTAMP(0) is 2016-11-11 23:33:22

SELECT CASE WHEN EXTRACT( HOUR FROM CURRENT_TIMESTAMP(0))=23 THEN '23:00-00:00' 
            WHEN EXTRACT( HOUR FROM CURRENT_TIMESTAMP(0))<2 THEN '0'||
                  CAST(EXTRACT( HOUR FROM CURRENT_TIMESTAMP(0)) AS VARCHAR(1))||':00-'||
                  CAST(EXTRACT( HOUR FROM CURRENT_TIMESTAMP(0)) AS VARCHAR(1))||':00'
            ELSE CAST(EXTRACT( HOUR FROM CURRENT_TIMESTAMP(0)) AS VARCHAR(2))||':00-'||
                 CAST(EXTRACT( HOUR FROM CURRENT_TIMESTAMP(0)) AS VARCHAR(1))||':00'
       END;

Output: 23:00-00:00

Note: In above examples, CURRENT_TIMESTAMP(0) can be replaced with a column of datatype TIMESTAMP(0) from Teradata table.