CODEWITHSUNDEEP
sqlamazon-redshiftdate-arithmetic
Benny
Benny

Reputation: 432

Time Difference in Redshift

how to get exact time Difference between two column

eg:

   col1 date is 2014-09-21 02:00:00
   col2 date is 2014-09-22 01:00:00

output like

result: 23:00:00

I am getting result like 

 Hours Minutes Seconds
 --------------------
  3    3       20
  1    2       30

using the following query

SELECT start_time,
       end_time,
       DATE_PART(H,end_time) - DATE_PART(H,start_time) AS Hours,
       DATE_PART(M,end_time) - DATE_PART(M,start_time) AS Minutes,
       DATE_PART(S,end_time) - DATE_PART(S,start_time) AS Seconds
FROM user_session

but i need like

 Difference 
 -----------
  03:03:20
  01:02:30

Upvotes: 13

Views: 28710

Answers (2)

Joe Harris
Joe Harris

Reputation: 14035

Use DATEDIFF to get the seconds between the two datetimes:

DATEDIFF(second,'2014-09-23 00:00:00.000','2014-09-23 01:23:45.000')

Then use DATEADD to add the seconds to '1900-01-01 00:00:00':

DATEADD(seconds,5025,'1900-01-01 00:00:00')

Then CAST the result to a TIME data type (note that this limits you to 24 hours max):

CAST('1900-01-01 01:23:45' as TIME)

Then LTRIM the date part of the value off the TIME data (as discovered by Benny). Redshift does not allow use of TIME on actual stored data:

LTRIM('1900-01-01 01:23:45','1900-01-01')

Now, do it in a single step:

SELECT LTRIM(DATEADD(seconds,DATEDIFF(second,'2014-09-23 00:00:00','2014-09-23 01:23:45.000'),'1900-01-01 00:00:00'),'1900-01-01');

:)

Upvotes: 19

Benny
Benny

Reputation: 432

SELECT LTRIM(DATEADD(seconds,DATEDIFF(second,'2014-09-23 00:00:00','2014-09-23 01:23:45.000'),'1900-01-01 00:00:00'),'1900-01-01');

Upvotes: 1

Related Questions