Teradata Date Time - Part 10 - Combining Time with Interval literals

We can combine Time literals with time interval literals.

Note :The resulting time will always be the same data type as the starting time, irrespective of what the interval type is.

If we want different time data type (w.ie with different precision ) we can use cast to do that.

The Time literals are represented as format TIME 'HH:MM:SS.nnnnnn' .

Just like DATE literals we use the keyword TIME before the literals.

Examples:

SELECT TIME '12:12:12.121' + INTERVAL '12:12' HOUR TO MINUTE;

(12:12:12.121+ 12:12)

0:24:12

The above result might seem to be opposite of what we explained earlier.

The starting TIME is of data type TIME(3), but the output is TIME(0).

If we convert the output to CHAR we will be able to see the exact precision. I currently don’t know why it works this way. Any input is appreciated.

SELECT CAST((TIME '12:12:12.121' + INTERVAL '12:12' HOUR TO MINUTE) AS CHAR(15));

(12:12:12.121+ 12:12)

0:24:12.121

Other way to confirm this is to use the TYPE function as shown below:

SELECT TYPE((TIME '12:12:12.121' + INTERVAL '12:12' HOUR TO MINUTE)); --> Output is TIME(3)

Look at the below example:

SELECT TYPE(TIME '11:12:12' + INTERVAL '02:12:12.666666' HOUR TO SECOND);

Output is TIME(0). Note that the interval literal was of the type HOUR TO SECOND(6). But still the output takes the precision of the staring time.ie TIME(0)

To be able to get the result as TIME(2) we need to use cast as shown below:

SELECT CAST((TIME '09:30:22'
       - INTERVAL '01:20:10.45' HOUR TO SECOND)AS TIME(2));