postgresql - Postgres: Selecting a 24 hour window for multiple dates and picking the initial date as start date -

i'm trying select 24 hour chunks of data list of days , retain start date. it's easier describe table i'll that:

original table

id      localminute          usage 1   01-01-2013 00:00:00-05    3 . . 1   12-31-2013 00:00:00-05    4 2 . . 

let's dates i'm interested in february 1st , february 12th. want aggregate usage 8 on february 1st 07:59 on february 2nd , likewise 8 february 12th 7:59 february 13th.

this each individual person output be:

id       eventday      total  1      02-01-2013      78  1      02-01-2013      89  2      02-13-2013      94  2      02-13-2013      67  .  . 

my problem here adding date start counting on item event day i.e february 1st in case 1 , february 13th in second case i'm not sure how because of overlapping days. simple query getting aggregate minus date below:

create table aggregates select id, sum(usage) total source_table localminute between '02-01-2013 08:00' , '02-02-2013 07:59' group dataid  union    same above february 13th.  

if select date column , add date column in group statement, can't control date item randomly comes 02-01-2013 or 02-02-2013 depending on situation.

is there way manually input day column each iteration or other workaround storing days in list etc.?

any appreciated!

it looks want making happens before 8am count towards previous day. in case, can offset timestamp 8 hours, truncate on day, , use grouping.

select id, date_trunc('day', localminute - '8 hours'::interval)::date,        sum(usage) source_table group id, date_trunc('day', localminute - '8 hours'::interval) 

if you're interested in data between 01 feb 2013 08:00 , 13 feb 2013 08:00, can add where localminute >= '01 feb 2013 08:00'::timestamptz , localminute < '13 feb 2013 08:00'::timestamptz (before group by).

i avoid '02-02-2013 07:59' , go strict inequality on '02-13-2013 08:00', able catch entries occuring during last minute (between includes end of range). alternatively, if you're using sufficiently recent version of postgresql, use range type, although reasonably short this, 2 inequality (as above) seem reasonable.