Re: How to split timestamps values by 24h slices ?

Le 30/03/09 05:39, Osvaldo Kussama a écrit :
> 2009/3/29 Bruno Baguette <bruno.baguette@gmail.com>:
>> I would like to do a SELECT of that table, but by splitting by 24h day :
> Try:
>
> bdteste=# \set ini '\'2009-03-30 14:50\'::timestamp'
> bdteste=# \set fim '\'2009-04-01 19:00\'::timestamp'
>
> bdteste=# SELECT CASE WHEN (:ini)::date + s.a = (:ini)::date THEN :ini
> bdteste-#             ELSE (:ini)::date+s.a*'1 day'::interval
> bdteste-#        END AS "Inicio",
> bdteste-#        CASE WHEN (:ini)::date + s.a < (:fim)::date THEN
> ((:ini)::date+(s.a+1)*'1 day'::interval)-'1 second'::interval
> bdteste-#             ELSE :fim
> bdteste-#        END AS "Fim"
> bdteste-#        FROM generate_series(0, extract(day from (:fim -
> :ini))::int) as s(a);
>        Inicio        |         Fim
> ---------------------+---------------------
>  2009-03-30 14:50:00 | 2009-03-30 23:59:59
>  2009-03-31 00:00:00 | 2009-03-31 23:59:59
>  2009-04-01 00:00:00 | 2009-04-01 19:00:00
> (3 registros)
>
> Osvaldo

Really nice !

I under-estimasted the power of the generate_series() function and I
didn't thought using that function with date manipulation.

The calendar suggest of Artacus is also interesting but it needs to be
be regularly populated.

Regards,

--
Bruno Baguette