Redshift: splitting a time duration row by 24 hour period

Question

I'm also trying to split a row by 24 hours period ranges between FromDate, Todate.

For example, if a time row has given as below, (Range between FromDate, Todate is 4 days, so I want 4 rows)

ID---FromDate---Todate     
1---2014-04-01 08:00:00.000---2014-04-04 12:00:00.000

The result I want to see would be like this:

ID---FromDate---Todate---DateDiff(HH)
1---2014-04-01 08:00:00.000---2014-04-01 23:59:59.000---15
1---2014-04-02 00:00:00.000---2014-04-02 23:59:59.000---23
1---2014-04-03 00:00:00.000---2014-04-03 23:59:59.000---23
1---2014-04-04 00:00:00.000---2014-04-04 12:00:00.000---12

I have found a solution to this, but it just does not work using Redshift when I iterate inside my own auxiliary table (different from the solution using SQL Server!)

I am not sure if this could be possible using Redshift or if I should look for another way to solve it (like a custom function or something like that).

Thanks!

score 1 · Accepted Answer · answered Feb 16 '20 at 13:34

Redshift has one painful "feature". It is a pain to generate a derived table. Let me assume that you have a table big enough to generate a tally or numbers table.

Then, the rest is date arithmetic . . . but a bit complicated. I don't have Redshift on hand -- and it has a weird combination of date syntax from SQL Server and Postgres

with numbers as (
      select row_number() over () - 1 as n
      from t
     )
select t.id,
       greatest(date_trunc('day', t.fromdate) + n.n * interval '1 day', t.fromdate)  as fromdate,
       least(date_trunc('day', t.fromdate) + (n.n + 1) * interval '1 day', t.todate) as todate
       datediff(hour,
                greatest(date_trunc('day', t.fromdate) + n.n * interval '1 day', t.fromdate),
                least(date_trunc('day', t.fromdate) + (n.n + 1) * interval '1 day', todate)
               ) as hours
from t join
     numbers n
     on todate > date_trunc('day', t.fromdate) + n.n * interval '1 day';

Here is a db<>fiddle using Postgres that illustrates the main ideas behind this.

Redshift: splitting a time duration row by 24 hour period

1 Answers1