From: Jean-Luc Lachance <jllachan@nsd.ca>
To: Dan Langille <dan@langille.org>
Cc: praveen vejandla <praveen_vejandla@rediffmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: calculating interval
Date: Fri, 22 Nov 2002 11:26:25 -0500
Message-ID: <3DDE5AB1.84D805C0@nsd.ca> (raw)
References: <20021122092748.X3909-100000@m20.unixathome.org>
Watch out! 36.85 weeks could have 37 sundays...
Take into account the day of the week of the first and last day.
Also, process the first and last day separately and work with whole day,
if you want to exclude part of the day.
Dan Langille wrote:
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jllachan@nsd.ca, dan@langille.org, praveen_vejandla@rediffmail.com
Subject: Re: calculating interval
In-Reply-To: <3DDE5AB1.84D805C0@nsd.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox