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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
