SQL Redshift difference in days between 2 timestamps

Question

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.

MatBailie · Accepted Answer · 2018-05-22 14:54:13Z

7

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

edited May 22, 2018 at 14:54

answered May 22, 2018 at 14:52

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NI6 Over a year ago

Thanks for this! I'm trying to diff between 2 timestamps as follows: select datediff(month, TIMESTAMP 'Jan 17,2021 00:00:00', TIMESTAMP 'Feb 1,2021 00:00:00'). I'd like it to return 0, but instead get 1. Any ideas of how doing such diff for months/years? Thanks In advance

MatBailie Over a year ago

@NI6 Please don't use comments to ask questions, please open a new question, tagging people if appropriate. Also, please check if your question has been asked before; for example, Google gave me this... stackoverflow.com/questions/67021316/…

Gordon Linoff · Accepted Answer · 2018-05-22 14:53:01Z

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;

Collectives™ on Stack Overflow

SQL Redshift difference in days between 2 timestamps

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related