Re: calculating interval

On 22 Nov 2002, praveen vejandla wrote:

> Dear All,
>
> Is there any way in postgresql to calculate the interval between
> two times excluding specific days,specific duration.
>
> Ex:
> timestamp1 : 2002-10-01 10:30AM
> timestamp2 : 2002-15-01 04:50PM
>
> suppose if i need the difference between timestamp1,timestamp2 but
> i don't want to count how many sun days are coming, i want to
> ignore all sundays in between,i want to ignore certain timings(say
> 10.00 AM to 5:00PM)s also,then how can I get the duration in this
> way.

My guess: write a function. Calculating the number of days between the
two dates is easy. To avoid certain days, of the week, in your case,
Sunday, I would count the number of whole weeks between the two dates.

test=# select '2002-10-01 10:30AM'::timestamp - '2002-15-01
04:50PM'::timestamp;
?column?
----------------
258 days 16:40

In this case 258/7 = 36.85... So you know you have 36 Sundays in there.
This will need adjusting for non-full weeks.

Hope that gets you started.