pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcalculating interval
4+ messages / 4 participants
[nested] [flat]
* calculating interval
@ 2002-11-22 14:26 praveen vejandla <praveen_vejandla@rediffmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: praveen vejandla @ 2002-11-22 14:26 UTC (permalink / raw)
To: pgsql-sql
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.
Thanks and Regards,
Praveen
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: calculating interval
@ 2002-11-22 14:36 Dan Langille <dan@langille.org>
parent: praveen vejandla <praveen_vejandla@rediffmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Dan Langille @ 2002-11-22 14:36 UTC (permalink / raw)
To: praveen vejandla <praveen_vejandla@rediffmail.com>; +Cc: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: calculating interval
@ 2002-11-22 16:26 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Dan Langille <dan@langille.org>
0 siblings, 0 replies; 4+ messages in thread
From: Jean-Luc Lachance @ 2002-11-22 16:26 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: praveen vejandla <praveen_vejandla@rediffmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: calculating interval
@ 2002-11-22 18:34 Jeff Boes <jboes@nexcerpt.com>
parent: praveen vejandla <praveen_vejandla@rediffmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jeff Boes @ 2002-11-22 18:34 UTC (permalink / raw)
To: pgsql-sql
On Fri, 22 Nov 2002 09:26:31 -0500, 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.
>
The easiest way would be to write a function in a language that supports
some kind of date library. I'm most familiar with Perl and its
Date::Manip library, so that's what I would turn to.
--
Jeff Boes vox 616.226.9550 ext 24
Database Engineer fax 616.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-11-22 18:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-22 14:26 calculating interval praveen vejandla <praveen_vejandla@rediffmail.com>
2002-11-22 14:36 ` Dan Langille <dan@langille.org>
2002-11-22 16:26 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-11-22 18:34 ` Jeff Boes <jboes@nexcerpt.com>
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