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

2009/3/29 Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com>:
>
> I have a table that contains two timestamps (and some other fields that does
> not matter here).
>
> the_table
> ----------
> pk_planning_id  ==> INT8 (primary key)
> timestamp_start ==> (not null timestamp without time zone)
> timestamp_stop  =+> (not null timestamp without time zone)
>
>
> I would like to do a SELECT of that table, but by splitting by 24h day :
>
> So, if I have one planning from 2009-03-30 14h50 to 2009-04-01 19h00, I
> would like to get three lines in my SELECT result :
>
> 123 | 2009-03-30 14h50 | 2009-03-30 24h00
> 123 | 2009-03-31 00h00 | 2009-03-31 24h00
> 123 | 2009-04-01 00h00 | 2009-04-01 19h00
>
> I was thinking of doing that by playing with three UNION requests (beginning
> date, intermediate(s) date(s) and ending dates.
>
> Am i going in the right way or is there a cleanest (or more elegant) way to
> do that ?
>

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