Convert HH:MM:S to seconds int

Question

I have two columns with the format YYYY-MM-DD HH:MM:SS When I do the difference between these two columns, my result has a format of the form HH:MM:SS

I would like to convert this result into seconds. How can I make it?

Example:

2019-06-15 19:27:33.045000 | 2019-06-15 19:27:22.298000 | 00:00:10.747

In Postgres you could do `to_char(timestamp '2019-06-15 20:27:33.045000' - timestamp '2019-06-15 19:17:22.298000', 'hh24:mi:ss')` - does this work in Redshift as well? — , Jul 05 '19 at 05:25

Mangesh Auti · Accepted Answer · 2019-07-05T05:27:11.320

0

You can use DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) function for more info check here

for e.g

 select datediff(second, '0','05:15:40');

edited Jul 05 '19 at 05:27

answered Jul 05 '19 at 04:57

Mangesh Auti

1,123
1
7
12

Check Datediff is avilable in redshift? If yes then try like this SELECT DATEDIFF(SECOND,0,'20:10:10') – Mangesh Auti Jul 05 '19 at 05:14

score -1 · Answer 2 · answered Jul 05 '19 at 05:15

You can try this :

Declare @time varchar(50)= '05:15:40'
select Sum(Left(@time,2) * 3600 + substring(@time, 4,2) * 60 + substring(@time, 7,2)) 
as seconds

OR

Declare @time varchar(50)= '05:15:40'
SELECT seconds = DATEPART(SECOND, @time) +  60 * DATEPART(MINUTE, @time) + 
3600 * DATEPART(HOUR, @time)

Convert HH:MM:S to seconds int

2 Answers2