Categories
Extract Hours, Minutes & Seconds from TIMESTAMP
My requirement is that I have a text field (CHAR) that shows time values in the form HH:MM:SS. I would like extract the hours, minutes and seconds from the text field and convert them all to seconds. I have tried using INSTR, SUBSTRING & LOCATE but have not been able to come up with a solution.
Example Output - 02:00:00 will become 7200 seconds.
Answers
-
cast(SUBSTRING('02:10:05' FROM 1 FOR 2) as INT) * 3600 + cast(SUBSTRING('02:10:05' FROM 4 FOR 2) as INT) * 60 + cast(SUBSTRING('02:10:05' FROM 7 FOR 2) as INT)Result = 7805
0 -
Hello Christian. Thank you for responding. I had thought of the the solution you offered but the requirement is unique in that the HH part might not always be a 2 digit number but sometimes is 3 digits Some of the data looks as follows:
HH:MM:SS
07:00:12
98:21:00
101:05:11
112:13:15
The requirement also needs me to sort on that column and currently the sort on this data is not working.
0 -
I ended up getting the length of the string when HH are 2 digit or 3 digit numbers and doing the conversion that way.
0 -
So it's done? For the benefit of other users can you post the solution?
0 -
Below is the solution that I used. There may be a more elegant way to do this.
case
when LENGTH("Table"."Column") = 8 then
cast(SUBSTRING("Table"."Column" FROM 1 FOR 2) as INT) * 3600 + cast(SUBSTRING("Table"."Column" FROM 4 FOR 2) as INT) * 60 + cast(SUBSTRING("Table"."Column" FROM 7 FOR 2) as INT)
when LENGTH("Table"."Column") = 9 then
cast(SUBSTRING("Table"."Column" FROM 1 FOR 3) as INT) * 3600 + cast(SUBSTRING("Table"."Column" FROM 5 FOR 2) as INT) * 60 + cast(SUBSTRING("Table"."Column" FROM 8 FOR 2) as INT)
when LENGTH("Table"."Column") = 10 then
cast(SUBSTRING("Table"."Column" FROM 1 FOR 4) as INT) * 3600 + cast(SUBSTRING("Table"."Column" FROM 6 FOR 2) as INT) * 60 + cast(SUBSTRING("Table"."Column" FROM 9 FOR 2) as INT)
end
0 -
Also a valid approach. I was regex'ing it inside an EVALUATE :-)
0 -
Hi,
I would like to know if you have already done the reverse. Do you have the total seconds and need to arrive in HH:MI:SS format?
Entry: 7200 seconds
Output: 02:00:00
So far I have not been able to calculate this, could you help me?
0 -
Hi @User_A1BVT ,
You better post a new question. This is a 5 years old thread and you don't really have the "same" question (because you have the opposite one).
Just make sure to specify the product name and version in your question, so that answers will be adapted.
0
