Skip site navigation (1)
Skip section navigation (2)
## Re: time interval math

### In response to

### Responses

### pgsql-sql by date

Hehe, thanks, I played around and ended up with this: round(SUM(extract('epoch' from (time_out - time_in)))) I will have to do the division outside of the query, but that's really a minor issue. Knowing the total in seconds was the big roadblock. And converting back is easier (a / 3600 + ":" + a / 60 + ":" + a % 60) > -----Original Message----- > From: Steve Crawford [mailto:scrawford(at)pinpointresearch(dot)com] > Sent: Wednesday, February 08, 2012 3:26 PM > To: Edward W. Rouse > Cc: pgsql-sql(at)postgresql(dot)org > Subject: Re: [SQL] time interval math > > On 02/08/2012 12:01 PM, Edward W. Rouse wrote: > > I'm still working on getting this to work, but the summary is this: > > > > I am getting several (many) intervals of hour, minutes and seconds. I > need a > > sum of the absolute value these intervals, similar to the SUM(ABS()) > > function for numbers; and I need to divide this sum by an integer > (bigint). > > Getting the intervals is no problem, but I can't find built in > functions for > > the rest. Currently on 8.3, want to upgrade to 9.x but I can't until > this is > > finished. > > > > Do these functions exist, or will I be forced to convert to seconds, > do the > > math and then convert back to hour-minute-second format (I am > assuming from > > current data that, after the divide, the result should be in the > minute: > > second range). > > You will have to do some work on your own. > > Time and intervals are tricky beasts and depend on the oddities of > daylight saving rules. Even though you are only using > hours/minutes/seconds the interval type also supports days and months. > A > day interval is probably 24 hours but could be 23 or 25 if it crosses a > DST boundary. Months have different numbers of days. You have > situations > where adding and subtracting a month does not give the original date: > > select '2011-03-31'::date - '1 month'::interval + '1 month'::interval; > ?column? > --------------------- > 2011-03-28 00:00:00 > > There is no abs(interval) function but, if you know that all your > intervals are basic H:M:S and that you won't have any difficulty due to > problems similar to the above you can mimic it with: > ...case when myinterval < '0'::interval then '0'::interval - myinterval > else myinterval end... > > You are even allowed to sum that and divide it (though I suspect there > are some interesting corner-cases waiting to be discovered): > ...sum(case when myinterval < '0'::interval then '0'::interval - > myinterval else myinterval end)/2... > > Before you upgrade, be sure to read the release notes and test your > calculations. The way intervals are handled, especially regarding > intervals across DST boundaries, have changed over time. IIRC most of > those changes were pre-8.3 but haven't looked recently. > > Cheers, > Steve

- Re: time interval math at 2012-02-08 20:25:31 from Steve Crawford

- Re: time interval math at 2012-02-09 01:51:10 from Tim Landscheidt

Next: From:Tim LandscheidtDate:2012-02-09 01:51:10Subject: Re: time interval mathPrevious: From: Steve CrawfordDate: 2012-02-08 20:25:31Subject: Re: time interval math