Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux