pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
time intervals
9+ messages / 7 participants
[nested] [flat]

* time intervals
@ 1999-04-29 20:13 JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
  1999-04-30 08:10 ` Re: [SQL] time intervals Mark Jewiss <mark@office.knowledge.com>
  1999-04-30 09:53 ` Re: [SQL] time intervals George Moga <george@flex.ro>
  0 siblings, 2 replies; 9+ messages in thread

From: JT Kirkpatrick @ 1999-04-29 20:13 UTC (permalink / raw)
  To: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>

I have two fields, timein & timeout, both defined as type "time".  how can 
i get the difference between the two??  i am connecting to postgres through 
access97 and trying to run a simple query to show me timeout, timein, and 
timeout-timein, but the calculation is returning an error.

thanks in advance!  jt



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] time intervals
  1999-04-29 20:13 time intervals JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
@ 1999-04-30 08:10 ` Mark Jewiss <mark@office.knowledge.com>
  1999-04-30 12:45   ` Re: [SQL] time intervals José Soares <jose@sferacarta.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Mark Jewiss @ 1999-04-30 08:10 UTC (permalink / raw)
  To: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; +Cc: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>

> I have two fields, timein & timeout, both defined as type "time".  how can 
> i get the difference between the two??  i am connecting to postgres through 
> access97 and trying to run a simple query to show me timeout, timein, and 
> timeout-timein, but the calculation is returning an error.

In SQL you can do a DATEDIFF...I'm not in front of postgresql so am not
sure if it supports this.

Syntax is

select datediff (day, mydate, 'Jan 10 1999'
from table

If mydate = Jan 9, then the result is one. I think datediff accepts
datetime, so if you specify it properly (i.e. '1 Jan 1999 10:05") then
you'll get the right answers. Just change day to whatever you need.

Regards,

Mark.
--
Mark Jewiss
Knowledge Matters Limited




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] time intervals
  1999-04-29 20:13 time intervals JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
  1999-04-30 08:10 ` Re: [SQL] time intervals Mark Jewiss <mark@office.knowledge.com>
@ 1999-04-30 12:45   ` José Soares <jose@sferacarta.com>
  0 siblings, 0 replies; 9+ messages in thread

From: José Soares @ 1999-04-30 12:45 UTC (permalink / raw)
  To: Mark Jewiss <mark@office.knowledge.com>; +Cc: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; 'pgsql-sql@hub.org' <pgsql-sql@hub.org>

Mark Jewiss ha scritto:

> > I have two fields, timein & timeout, both defined as type "time".  how can
> > i get the difference between the two??  i am connecting to postgres through
> > access97 and trying to run a simple query to show me timeout, timein, and
> > timeout-timein, but the calculation is returning an error.
>
> In SQL you can do a DATEDIFF...I'm not in front of postgresql so am not
> sure if it supports this.
>

DATEDIFF is not a SQL command maybe an Oracle or Sybase command.

>
> Syntax is
>
> select datediff (day, mydate, 'Jan 10 1999'
> from table
>
> If mydate = Jan 9, then the result is one. I think datediff accepts
> datetime, so if you specify it properly (i.e. '1 Jan 1999 10:05") then
> you'll get the right answers. Just change day to whatever you need.
>
> Regards,
>
> Mark.
> --
> Mark Jewiss
> Knowledge Matters Limited

You can create an operator for TIME - TIME...

--  Arithmetic operations that are permitted by SQL92:
--  -----------------------------------------
--  1st operand|operator|2nd operand|result
--  -----------+--------+-----------+--------
--  datetime   |   -    |datetime   |interval
--  datetime   |   +    |interval   |datetime
--  datetime   |   -    |interval   |datetime
--  interval   |   +    |datetime   |datetime
--  interval   |   +    |interval   |interval
--  interval   |   -    |interval   |interval
--  interval   |   *    |number     |interval
--  interval   |   /    |number     |interval
--  number     |   *    |interval   |interval
--  -----------+--------+-----------+--------

--TIME - TIME = INTERVAL-------------------------------------------
create function time_mi_time(time,time) returns timespan as '
declare
        i1 interval;
        i2 interval;
begin
        i1:= $1;
        i2:= $2;
        i1:=i1 - i2;
        return i1;
end;' language 'plpgsql';

create operator - (
        leftarg=time,
        rightarg=time,
        procedure=time_mi_time,
        commutator='-',
        negator='+',
        restrict=eqsel,
        join=eqjoinsel
        );

hygea=> select  time '12:30' - time '12:00';
?column?
--------
00:30:00
(1 row)

--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] time intervals
  1999-04-29 20:13 time intervals JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
@ 1999-04-30 09:53 ` George Moga <george@flex.ro>
  1 sibling, 0 replies; 9+ messages in thread

From: George Moga @ 1999-04-30 09:53 UTC (permalink / raw)
  To: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; pgsql-sql

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
JT Kirkpatrick wrote:
<blockquote TYPE=CITE>I have two fields, timein &amp; timeout, both defined
as type "time".&nbsp; how can
<br>i get the difference between the two??&nbsp; i am connecting to postgres
through
<br>access97 and trying to run a simple query to show me timeout, timein,
and
<br>timeout-timein, but the calculation is returning an error.
<p>thanks in advance!&nbsp; jt</blockquote>

<p><br>If your field is defined by tipe "time", the date is not important.
<p>In this case you can use the age(datetime, datetime) in this mode:
<p><tt>a. timein &lt; timeout:</tt><tt></tt>
<p><tt>test=> select age(datetime('today'::date,'10:00:15'::time), datetime('today'::date,'12:25:24'::time));</tt>
<br><tt>age</tt>
<br><tt>----------------------------</tt>
<br><tt>@ 2 hours 25 mins 9 secs ago</tt>
<br><tt>(1 row)</tt>
<p>or
<p><tt>b. timein > timeout:</tt>
<br><tt>test=> select age(datetime('today'::date,'10:00:15'::time), datetime('today'::date,'2:25:24'::time));</tt>
<br><tt>age</tt>
<br><tt>-------------------------</tt>
<br><tt>@ 7 hours 34 mins 51 secs</tt>
<br>(1 row)
<br>&nbsp;
<p>I use PostgreSQL v6.5.0 (09-04-1999) on Red&nbsp;Hat LINUX 5.2 with
2.2.5 kernel.
<br>--&nbsp;<br>
Best,<br>
&nbsp; George Moga,<br>
&nbsp; george@flex.ro<br>
&nbsp; Braila,ROMANIA
<br>&nbsp;</html>




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* RE: [SQL] time intervals
@ 1999-04-30 13:24 JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
  0 siblings, 0 replies; 9+ messages in thread

From: JT Kirkpatrick @ 1999-04-30 13:24 UTC (permalink / raw)
  To: 'George Moga' <george@flex.ro>; +Cc: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>

george -- thanks!  that worked fine!  i was able to substitute my fields 
"timeout" and "timein" for the times you showed in quotes, and add a 
"where" condition to the select statement to choose a particular record. 
 now i have to find a way to present it in access97, in a regular time 
format (1:35 - for 1hr 35mins) -- that or get rid of the "ago" which should 
be pretty easy!

thanks so much for your help!

jt

-----Original Message-----
From:	George Moga [SMTP:george@flex.ro]
Sent:	Friday, April 30, 1999 5:53 AM
To:	JT Kirkpatrick; SQL PostgreSQL
Subject:	Re: [SQL] time intervals

 << File: ATT00000.html; charset = iso-8859-2 >> 



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Time Intervals
@ 2002-02-13 16:53 Michael Richards <michael@fastmail.ca>
  2002-02-13 19:23 ` Re: Time Intervals Jason Earl <jason.earl@simplot.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Michael Richards @ 2002-02-13 16:53 UTC (permalink / raw)
  To: pgsql-sql

I've got a rather odd problem that I can't seem to solve easily with 
the given date manipulation functions.

I've got an expiry timestamp and a renewal interval. If something has 
expired it gets renewed as the expiry + renewal * n
Where n is the smallest number that will cause the calculation to 
result in the future.

So if I've got a resource that is renewed by the hour and it expired 
last week then I need to add on enough hours so its new expiry will 
be up to 1 hour in the future. Only trouble is this renewal period 
can be anything from minutes to months and it may have expired up to 
6 months ago.

If I could convert the timestamp into a julian of some sort perhaps I 
could do the math that way.

Any ideas?

-Michael
_________________________________________________________________
    http://fastmail.ca/ - Fast Secure Web Email for Canadians
From pgsql-sql-owner@postgresql.org  Wed Feb 13 13:43:42 2002
Received: from ece.rice.edu (ece.rice.edu [128.42.4.34])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DIMgE40345
	for <pgsql-sql@postgresql.org>; Wed, 13 Feb 2002 13:22:42 -0500 (EST)
	(envelope-from reedstrm@rice.edu)
Received: from localhost (localhost [127.0.0.1])
	by ece.rice.edu (Postfix) with ESMTP
	id 3412D68A66; Wed, 13 Feb 2002 12:22:42 -0600 (CST)
Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154])
	by ece.rice.edu (Postfix) with ESMTP
	id 672FC68A61; Wed, 13 Feb 2002 12:22:41 -0600 (CST)
Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.34 #1 (Debian))
	id 16b43F-0002rr-00; Wed, 13 Feb 2002 12:22:41 -0600
Date: Wed, 13 Feb 2002 12:22:41 -0600
From: "Ross J. Reedstrom" <reedstrm@rice.edu>
To: Michael Richards <michael@fastmail.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Time Intervals
Message-ID: <20020213182241.GB10858@rice.edu>
Mail-Followup-To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>,
	Michael Richards <michael@fastmail.ca>, pgsql-sql@postgresql.org
References: <3C6A9A0D.000015.04483@ns.interchange.ca>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3C6A9A0D.000015.04483@ns.interchange.ca>
User-Agent: Mutt/1.3.27i
X-Virus-Scanned: by AMaViS snapshot-20010714
X-Archive-Number: 200202/141
X-Sequence-Number: 6569

On Wed, Feb 13, 2002 at 11:53:33AM -0500, Michael Richards wrote:
> I've got a rather odd problem that I can't seem to solve easily with 
> the given date manipulation functions.
> 
> I've got an expiry timestamp and a renewal interval. If something has 
> expired it gets renewed as the expiry + renewal * n
> Where n is the smallest number that will cause the calculation to 
> result in the future.
> 
> So if I've got a resource that is renewed by the hour and it expired 
> last week then I need to add on enough hours so its new expiry will 
> be up to 1 hour in the future. Only trouble is this renewal period 
> can be anything from minutes to months and it may have expired up to 
> 6 months ago.
> 
> If I could convert the timestamp into a julian of some sort perhaps I 
> could do the math that way.
> 
> Any ideas?

Hmm, If I undestand your problem correctly, it's actually pretty easy:
you just need to see if expiry is in the past, and if it is, set it to
current_timestamp + renewal_interval. 

If your doing the license expired detection in the frontend in a procedural
language, just do a simple update. If you want to hide all that in the
backend, you _still_ probably need to use a procedural language, such as
pgpsql.

Do you want to actually update the databse table with a new expiry, or
just calculate one on the fly?

Ross



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Time Intervals
  2002-02-13 16:53 Time Intervals Michael Richards <michael@fastmail.ca>
@ 2002-02-13 19:23 ` Jason Earl <jason.earl@simplot.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Jason Earl @ 2002-02-13 19:23 UTC (permalink / raw)
  To: Michael Richards <michael@fastmail.ca>; +Cc: pgsql-sql


PostgreSQL has all kinds of nifty date math tools.  For example,


processdata=> SELECT CURRENT_TIMESTAMP AS "NOW", 
              CURRENT_TIMESTAMP + interval '1 hour' AS "LATER";

          NOW           |         LATER          
------------------------+------------------------
 2002-02-13 12:18:30-07 | 2002-02-13 13:18:30-07
(1 row)

It seems to me that what you really want isn't to add an interval
value to your expiry timestamp, but rather you need to add the
interval value to the current timestamp.  The cool thing is that
intervals like '1 week', '30 days', '5 minutes' all work like you
would expect.

So when you update your records simply do something like this:

UPDATE my_table SET expiry = CURRENT_TIMESTAMP + interval '1 hour' WHERE ...

I hope this was helpful.

Jason

"Michael Richards" <michael@fastmail.ca> writes:

> I've got a rather odd problem that I can't seem to solve easily with 
> the given date manipulation functions.
> 
> I've got an expiry timestamp and a renewal interval. If something has 
> expired it gets renewed as the expiry + renewal * n
> Where n is the smallest number that will cause the calculation to 
> result in the future.
> 
> So if I've got a resource that is renewed by the hour and it expired 
> last week then I need to add on enough hours so its new expiry will 
> be up to 1 hour in the future. Only trouble is this renewal period 
> can be anything from minutes to months and it may have expired up to 
> 6 months ago.
> 
> If I could convert the timestamp into a julian of some sort perhaps I 
> could do the math that way.
> 
> Any ideas?
> 
> -Michael
> _________________________________________________________________
>     http://fastmail.ca/ - Fast Secure Web Email for Canadians
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Time Intervals
@ 2002-02-13 19:37 Michael Richards <michael@fastmail.ca>
  2002-02-13 21:50 ` Re: Time Intervals Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Michael Richards @ 2002-02-13 19:37 UTC (permalink / raw)
  To: jason.earl@simplot.com; +Cc: pgsql-sql

That is the trivial case. The difficulty arises when the expiry plus 
the interval is still less than the current timestamp. In my original 
description of the problem this is the 'n' part of the equation.

The only solution I can think of is if I can convert a date into a 
number of some sort and then just use normal math on it. I really 
need:

| now - then |
| ---------- | * (period+1) + then
|_  period  _|
         
That's basically find the amount of time since it's expired and 
determine the number of periods between  the expiry and now and take 
the floor of it. Multiply that by the number of periods plus 1 to get 
the new expiry.

Since there is no way to divide 2 intervals in postgres I believe I 
need to find a way to turn a timestamp and an interval into a number.

-Michael

> PostgreSQL has all kinds of nifty date math tools.  For example,
> 
> 
> processdata=> SELECT CURRENT_TIMESTAMP AS "NOW",
> CURRENT_TIMESTAMP + interval '1 hour' AS "LATER";
> 
> NOW           |         LATER
> ------------------------+------------------------
> 2002-02-13 12:18:30-07 | 2002-02-13 13:18:30-07
> (1 row)
> 
> It seems to me that what you really want isn't to add an interval
> value to your expiry timestamp, but rather you need to add the
> interval value to the current timestamp.  The cool thing is that
> intervals like '1 week', '30 days', '5 minutes' all work like you
> would expect.
> 
> So when you update your records simply do something like this:
> 
> UPDATE my_table SET expiry = CURRENT_TIMESTAMP + interval '1 hour'
> WHERE ...
> 
> I hope this was helpful.
> 
> Jason

_________________________________________________________________
    http://fastmail.ca/ - Fast Secure Web Email for Canadians
From pgsql-sql-owner@postgresql.org  Wed Feb 13 16:21:47 2002
Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id g1DLAaE92751
	for <pgsql-sql@postgresql.org>; Wed, 13 Feb 2002 16:10:37 -0500 (EST)
	(envelope-from josh@agliodbs.com)
Received: from [209.10.40.250] (account <josh@agliodbs.com>)
  by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2)
  with HTTP id 693157; Wed, 13 Feb 2002 13:10:35 -0800
From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: Time Intervals
To: "Michael Richards" <michael@fastmail.ca>, jason.earl@simplot.com
Cc: pgsql-sql@postgresql.org
X-Mailer: CommuniGate Pro Web Mailer v.3.5.2
Date: Wed, 13 Feb 2002 13:10:35 -0800
Message-ID: <web-693157@davinci.ethosmedia.com>
In-Reply-To: <3C6AC08C.0000A1.04458@ns.interchange.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit
X-Archive-Number: 200202/146
X-Sequence-Number: 6573

Michael,

> The only solution I can think of is if I can convert a date into a 
> number of some sort and then just use normal math on it. I really 
> need:
> 
> | now - then |
> | ---------- | * (period+1) + then
> |_  period  _|
>          
> That's basically find the amount of time since it's expired and 
> determine the number of periods between  the expiry and now and take 
> the floor of it. Multiply that by the number of periods plus 1 to get
>  
> the new expiry.
> 
> Since there is no way to divide 2 intervals in postgres I believe I 
> need to find a way to turn a timestamp and an interval into a number.

Hmmm ... yeah, you're right.  Do you need the date+time or just the
 date?  If the latter, you can use the DATE data type, which is
 integer-based.

If the former, maybe the core team needs some help implementing * and /
 operators for TIMESTAMP and INTERVAL ...

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Time Intervals
  2002-02-13 19:37 Re: Time Intervals Michael Richards <michael@fastmail.ca>
@ 2002-02-13 21:50 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2002-02-13 21:50 UTC (permalink / raw)
  To: Michael Richards <michael@fastmail.ca>; +Cc: jason.earl@simplot.com; pgsql-sql

"Michael Richards" <michael@fastmail.ca> writes:
> The only solution I can think of is if I can convert a date into a 
> number of some sort and then just use normal math on it. I really 
> need:

> | now - then |
> | ---------- | * (period+1) + then
> |_  period  _|

Are you *sure* that's what you want?  You can certainly do it that way
--- extract(epoch from timestamp), do math, convert back --- but the
above only works if the "period" is a constant number of seconds.
Intervals like "1 month" cannot be handled as above.  Less obviously,
intervals like "1 day" cannot be handled that way either (think about
daylight savings transitions).

There was a thread on this same topic just recently, and I think the
conclusion was that the cleanest way to handle real-world interval
definitions is to rely on a loop around a timestamp + interval addition
operator:

	while tstamp < now do
		tstamp := tstamp + interval;

This is trivial to program in a plpgsql function, for example, and it's
quite cheap as long as you don't let too many periods elapse between
updates.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2002-02-13 21:50 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-29 20:13 time intervals JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
1999-04-30 08:10 ` Mark Jewiss <mark@office.knowledge.com>
1999-04-30 12:45   ` José Soares <jose@sferacarta.com>
1999-04-30 09:53 ` George Moga <george@flex.ro>
1999-04-30 13:24 RE: [SQL] time intervals JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
2002-02-13 16:53 Time Intervals Michael Richards <michael@fastmail.ca>
2002-02-13 19:23 ` Re: Time Intervals Jason Earl <jason.earl@simplot.com>
2002-02-13 19:37 Re: Time Intervals Michael Richards <michael@fastmail.ca>
2002-02-13 21:50 ` Re: Time Intervals Tom Lane <tgl@sss.pgh.pa.us>

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