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

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
Thread:
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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message aravind chandu 2009-03-30 06:18:02 Parallel Query Processing
Previous Message Craig Ringer 2009-03-30 04:51:30 Re: Installing PLPython - Version Problem