Is there a way to subtract 2 timestamps and get the result in 'hh:mm:ss' format in Redshif-CodePudding

I've been trying to get the duration between two timestamps using

select timestamp1-timestamp2 as time_s
from table_name

but I'm getting result as:

00:00:06.070627
00:00:33.415313
00:00:51.293319
00:02:00.146453
00:02:25.600623
00:06:37.811005
00:28:27.698517

I don't want the values after '.'

I tried using

split_part(time_s, '.',1)

but it gave

Can somebody please help?

CodePudding user response：

The difference between two timestamps is an INTERVAL.

The basic problem that you are experiencing is that there is no standard way to display or return an Interval. For example:

If you try your query in the web-based Redshift Query Editor, it will return NULL
If you run it in your particular SQL Client, it is returning 00:00:06.070627
If you run it in DbVisualizer, it is returning 0 years 0 mons 0 days 0 hours 0 mins 0.0 secs
If you run it in DataGrip, it is returning Invalid character data was found

When you were attempting to extract a portion of the result to the left of the ., Redshift was confused because it does not see the output the way it is displayed in your SQL Client.

The best way to avoid your SQL Client from doing strange things to results is to convert the answer to TEXT:

SELECT (timestamp1 - timestamp2)::TEXT

This gives an answer like: 24 days 13:57:40.561373

You can then manipulate it like a string:

SELECT SPLIT_PART((timestamp1 - timestamp2)::TEXT, '.', 1)

This gives: 24 days 13:57:40

Bottom line: When things look strange in SQL results, cast the result to TEXT or VARCHAR to see what the data really looks like.