CODEWITHSUNDEEP
sqldatetimeamazon-redshift
Bilberryfm
Bilberryfm

Reputation: 537

SQL Redshift difference in days between 2 timestamps

In my Redshift table I have 2 columns that stores timestamp values: start_date_time and end_date_time.

I need to find the difference between start_date_time and end_date_time, such that, if difference is 1 day, then result should be 1. If diff is 12 hours, than result should be 0.5, if diff is 8 hours - than 0.3333 etc.

How can I do this? I tried to use datediff function: but that will not return what I wanted:

select datediff(day,'2011-12-31 8:30:00','2011-12-31 20:30:00') as day_diff;

Will result in 0. But I need 0.5, because difference is 12 hours.

Upvotes: 1

Views: 8383

Answers (2)

MatBailie
MatBailie

Reputation: 86706

Then just do it in hours and divide by 24.0?

DATEDIFF(HOUR,'2011-12-31 8:30:00','2011-12-31 20:30:00') / 24.0

Or for better granularity, in minutes or seconds?

DATEDIFF(MINUTE,'2011-12-31 8:30:00','2011-12-31 20:30:00') / 1440.0

DATEDIFF(SECOND,'2011-12-31 8:30:00','2011-12-31 20:30:00') / 86400.0

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use a smaller time unit:

select datediff(hour, '2011-12-31 8:30:00', '2011-12-31 20:30:00')/24.0 as day_diff;

Or:

select datediff(minute, '2011-12-31 8:30:00', '2011-12-31 20:30:00')/(24.0 * 60) as day_diff;

Or:

select datediff(second, '2011-12-31 8:30:00', '2011-12-31 20:30:00')/(24.0 * 60 * 60) as day_diff;

Upvotes: 3

Related Questions