Time interval difference between dates in Redshift

Question

I need to analyze the time interval difference from two dates in Amazon-Redshift, for example:

'2021-10-13 14:37:00' - '2021-10-13 12:00:00' = '02:37:00'

or in case the days is different:

'2021-10-13 14:37:00' - '2021-10-12 12:00:00' = '26:37:00'

I figured out only datediff() function. This function only return part from what I want.

So, is there a way better to return the result that I need?

The Impaler · Accepted Answer · 2021-10-13 19:23:22Z

2

Subtract them using - to get an INTERVAL. Then, you can just format the interval according to your needs. For example:

select to_char(timestamp '2021-10-13 14:37:00' - timestamp '2021-10-13 12:00:00', 'HH:MI:SS')

Result:

02:37:00

answered Oct 13, 2021 at 19:23

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

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

3 Comments

Ranier Cordeiro Over a year ago

Thank you for your help. Yeah, it works. I tested it. But, when I put the fields in place of these strings the subtraction returns an empty interval, because of that, the to_char returns an error. (NOTICE: Function "to_char(interval,text)" not supported.)

The Impaler Over a year ago

@RanierCordeiro What's the data type of those fields? TIMESTAMP or something else?

Ranier Cordeiro Over a year ago

Yeah, it's TIMESTAMP. Also I tried explicit force it with :: timestamp each column when I call the function for example: to_char(filed_2 :: timestamp without time zone - field_1 :: timestamp without time zone, 'HH:MI:SS') but the error is the same.

Collectives™ on Stack Overflow

Time interval difference between dates in Redshift

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related