Extract Hour, Minute, Seconds in Redshift

Question

I need to find the difference between two timestamp range. I tried using DATEDIFF but not getting the exact result. Below is the query I am using:

```
select 
rtrim(datediff(hour,'2024-05-15 16:53:55', '2024-05-15 16:56:11'))||'h:'|| 
rtrim(MOD(datediff(minute,'2024-05-15 16:53:55', '2024-05-15 16:56:11'),60))||'m:'|| 
rtrim(MOD(datediff (seconds,'2024-05-15 16:53:55', '2024-05-15 16:56:11'),60))||'s' as sla
```
I am getting output as 
`0h:3m:16s`
but the actual output should be 
`0h:2m:16s`

can anyone please help me on this?

Answer

Hi,

Can you please try the following code? I have tested this and getting the correct output:

```
SELECT
    DATEDIFF(SECOND, '2024-05-15 16:53:55', '2024-05-15 16:56:11') / 3600 || ' hours ' ||
    (DATEDIFF(SECOND, '2024-05-15 16:53:55', '2024-05-15 16:56:11') % 3600) / 60 || ' minutes ' ||
    DATEDIFF(SECOND, '2024-05-15 16:53:55', '2024-05-15 16:56:11') % 60 || ' seconds' AS timestamp_diff;
```

Extract Hour, Minute, Seconds in Redshift

Relevant content