CODEWITHSUNDEEP
sqlteradata
lexu
lexu

Reputation: 8849

Teradara: Casting Seconds to Hours, Minutes and Seconds

I need to cast a Duration, measured in seconds, Decimal(18,0) to Hours, Minutes and Seconds.

e.g.: 
8192 Seconds => 2:16:32 (Hours:Minutes:Seconds)

I've found (here) that I can do this if the seconds are a literal string, like this:

SELECT CAST(INTERVAL '8192' SECOND AS INTERVAL HOUR TO SECOND)

But when I try:

SELECT Event_Id
     , CAST(INTERVAL Duration SECOND AS INTERVAL HOUR TO SECOND)
  FROM EVENT_TABLE
 WHERE <some conditions>

Event_Id and Duration are both declared Decimal(18,0) in the table EVENT_TABLE

Teradata complains:

[Error 3707] Syntax error, expected something like a string or Unicode character literal between the 'INTERVAL' keyword and the integer 8

What is the correct syntax or approach to use here?

SOLUTION: based on BellevueBob's post

SELECT Event_Id
     , CAST(
            Duration SECOND * INTERVAL '0000:01' MINUTE TO SECOND  /* 1 */
            AS INTERVAL HOUR TO SECOND                             /* 2 */
           )
  FROM EVENT_TABLE
 WHERE <some conditions>

Explanation:

  1. Multiply the known duration in seconds with an interval of one second length
  2. convert the resulting intervall of seconds into an interval of hours, minutes and seconds.

Upvotes: 4

Views: 6494

Answers (1)

BellevueBob
BellevueBob

Reputation: 9618

From the same solution you found, try this:

select CAST(duration * INTERVAL '0000:01' MINUTE TO SECOND 
            AS INTERVAL HOUR TO SECOND)
from (
   select cast(8192 as decimal(18,0)) as duration
   ) x

Upvotes: 3

Related Questions