From: | Dan Langille <dan(at)langille(dot)org> |
---|---|
To: | praveen vejandla <praveen_vejandla(at)rediffmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: calculating interval |
Date: | 2002-11-22 14:36:15 |
Message-ID: | 20021122092748.X3909-100000@m20.unixathome.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-11-22 15:10:42 | Re: Date trunc in UTC |
Previous Message | praveen vejandla | 2002-11-22 14:26:09 | calculating interval |