Lists: | pgsql-general |
---|
From: | Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to split timestamps values by 24h slices ? |
Date: | 2009-03-30 00:07:03 |
Message-ID: | 49D00D27.2030409@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hello !
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 ?
Thanks in advance !
Regards,
--
Bruno Baguette
From: | Osvaldo Kussama <osvaldo(dot)kussama(at)gmail(dot)com> |
---|---|
To: | Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to split timestamps values by 24h slices ? |
Date: | 2009-03-30 03:39:16 |
Message-ID: | 690707f60903292039h5fd29b4eh4196c807d24cb14f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Artacus <artacus(at)comcast(dot)net> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to split timestamps values by 24h slices ? |
Date: | 2009-03-30 06:15:54 |
Message-ID: | 49D0639A.6030003@comcast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Bruno Baguette wrote:
> Hello !
>
> 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 ?
>
> Thanks in advance !
>
> Regards,
>
I'd suggest a calendar table. Depending on needs, you may want to fields
like day_of_week, quarter, term, moon_phase, whatever...
CREATE TABLE calendar (
calendar_date date NOT NULL,
CONSTRAINT calendar_pk PRIMARY KEY (calendar_date)
);
-- populate your table with suitable date ranges
INSERT INTO calendar
SELECT '2000-01-01'::date + i
FROM generate_series(0,10000) i;
Now change your original query like so:
SELECT *
FROM my_table
JOIN calendar ON calendar_date BETWEEN TRUNC(timestamp_start)
AND timestamp_end
Artacus
From: | Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Osvaldo Kussama <osvaldo(dot)kussama(at)gmail(dot)com> |
Subject: | Re: How to split timestamps values by 24h slices ? |
Date: | 2009-04-08 09:34:34 |
Message-ID: | 49DC6FAA.7040107@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Le 30/03/09 05:39, Osvaldo Kussama a écrit :
> 2009/3/29 Bruno Baguette <bruno(dot)baguette(at)gmail(dot)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