From david@bizsurf.com Thu May  8 06:33:56 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 4A272475B8E
	for <pgsql-sql@postgresql.org>; Thu,  8 May 2003 02:33:56 -0400 (EDT)
Received: from mail.sebasasia.com (unknown [203.115.208.204])
	by postgresql.org (Postfix) with ESMTP id C5DFA476553
	for <pgsql-sql@postgresql.org>; Thu,  8 May 2003 02:33:49 -0400 (EDT)
Message-ID: <003501c3152c$b3872a30$f92c2c0a@david>
From: "David Loh" <david@bizsurf.com>
To: <pgsql-sql@postgresql.org>
Subject: A minute in timestamp
Date: Thu, 8 May 2003 14:40:22 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0032_01C3156F.C18EF2F0"
X-Spam-Status: No, hits=-1.5 required=5.0 tests=BAYES_20,HTML_20_30
	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200305/157
X-Sequence-Number: 13322

This is a multi-part message in MIME format.

------=_NextPart_000_0032_01C3156F.C18EF2F0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi all.
After some read of postgres' documentations, I can't found any command perf=
orm similar task like timediff() in other SQL server, the only thing I got =
is date_part().
For my case, what I actually need is to create a trigger (before insert) th=
at check previous record (1 mins), grab the previous value and deduct new v=
alue (NEW.myValue :=3D Previous.myValue - NEW.myOtherValue), and since the =
records are insert every minutes,the function should trace back to the reco=
rd entered a minute ago (for e.g, 12:38 will check for record of 12:37 and =
grab the value), hence timestamp even include the milli-second, is there an=
y command/trick to check if the record are entered approximately 55-65 secs=
 (1 minutes +/- 5 secs) ?
So far, I'd tried 'select * from myTable where date_part('minute',Update_Ti=
me - now()) =3D 1;', but this SQL statement will only return result if the =
hour:minute:milli-seconds MATCH EXACTLY !! (12:38.51451->12:39.51451 =3D ma=
tch, the rest aren't). Then I switch to 'select * from myTable where Update=
_Time =3D now()-interval '1 minute'', but it still the same :(
If any of you guys have a solution to solve my problems, please kindly let =
me know, your help is greatly apprepriate.=20
Thanks in advance.

Regards,
David Loh



------=_NextPart_000_0032_01C3156F.C18EF2F0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi all.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>After some read of postgres' documentation=
s, I=20
can't found any command perform similar task like timediff() in other SQL=
=20
server, the only thing I got is date_part().</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>For my case, what I actually need&nbsp;is =
to create=20
a trigger (before insert) that check previous record (1 mins), grab the pre=
vious=20
value and deduct new value (NEW.myValue :=3D Previous.myValue - NEW.myOther=
Value),=20
and since&nbsp;the records are&nbsp;insert&nbsp;every minutes,the function=
=20
should trace back to the record entered a minute ago (for e.g, 12:38&nbsp;w=
ill=20
check for record of 12:37 and grab the value), hence timestamp even include=
 the=20
milli-second, is there any command/trick to check if the record are entered=
=20
approximately 55-65 secs (1 minutes +/-&nbsp;5 secs) ?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>So far, I'd tried '<STRONG>select * from m=
yTable=20
where date_part('minute',Update_Time - now()) =3D 1;</STRONG>', but this SQ=
L=20
statement will only return result if the hour:minute:milli-seconds MATCH EX=
ACTLY=20
!! (12:38.51451-&gt;12:39.51451 =3D match, the rest aren't). Then I switch =
to=20
'<STRONG>select * from myTable where Update_Time =3D now()-interval '1=20
minute'</STRONG>', but it still the same :(</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>If any of you guys have&nbsp;a solution to=
 solve my=20
problems, please kindly let me know, your help is greatly apprepriate.=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Thanks in advance.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Regards,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>David Loh</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0032_01C3156F.C18EF2F0--




From dev@archonet.com Thu May  8 07:24:11 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id AF1C147630B
	for <pgsql-sql@postgresql.org>; Thu,  8 May 2003 03:24:11 -0400 (EDT)
Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net
	[194.217.242.85])
	by postgresql.org (Postfix) with ESMTP id 9EC4F475B8E
	for <pgsql-sql@postgresql.org>; Thu,  8 May 2003 03:24:10 -0400 (EDT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-35.mail.demon.net with esmtp (Exim 3.36 #2)
	id 19Dfl9-0005OW-0Z; Thu, 08 May 2003 08:24:07 +0100
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 5860F176B6; Thu,  8 May 2003 08:24:06 +0100 (BST)
Received: from client.archonet.com (client.archonet.com [192.168.1.16])
	by mainbox.archonet.com (Postfix) with ESMTP
	id B4EC81678A; Thu,  8 May 2003 08:24:05 +0100 (BST)
Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "David Loh" <david@bizsurf.com>, <pgsql-sql@postgresql.org>
Subject: Re: A minute in timestamp
Date: Thu, 8 May 2003 08:24:03 +0100
User-Agent: KMail/1.4.3
References: <003501c3152c$b3872a30$f92c2c0a@david>
In-Reply-To: <003501c3152c$b3872a30$f92c2c0a@david>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200305080824.04091.dev@archonet.com>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Spam-Status: No, hits=-38.9 required=5.0
	tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT,
	REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_KMAIL
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200305/159
X-Sequence-Number: 13324

On Thursday 08 May 2003 7:40 am, David Loh wrote:
> Hi all.
> After some read of postgres' documentations, I can't found any command
> perform similar task like timediff() in other SQL server, the only thing I
> got is date_part(). For my case, what I actually need is to create a
> trigger (before insert) that check previous record (1 mins), grab the
> previous value and deduct new value (NEW.myValue :=3D Previous.myValue -
> NEW.myOtherValue), and since the records are insert every minutes,the
> function should trace back to the record entered a minute ago (for e.g,
> 12:38 will check for record of 12:37 and grab the value), hence timestamp
> even include the milli-second, is there any command/trick to check if the
> record are entered approximately 55-65 secs (1 minutes +/- 5 secs) ?=20

Do exactly as you've just described:

SELECT ....
WHERE t BETWEEN now()-'1 min 5 secs'::interval AND now()-'55 secs'::interval

It takes a while to get used to PG's date/time/interval types vs timediff()=
=20
functions you get in most systems. I find them clearer now I've got used to=
=20
them.

--=20
  Richard Huxton




From viy@nordlb.lv Thu May  8 07:48:10 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id C7F2C476348
	for <pgsql-sql@postgresql.org>; Thu,  8 May 2003 03:48:10 -0400 (EDT)
Received: from shield.nordlb.lv (shield.pirmabanka.lv [195.114.57.4])
	by postgresql.org (Postfix) with ESMTP id 01629475EC9
	for <pgsql-sql@postgresql.org>; Thu,  8 May 2003 03:48:09 -0400 (EDT)
Received: from wall2.pirmabanka.lv (wall2-pub.nordlb.lv [195.114.57.14])
	by shield.nordlb.lv (8.12.9/8.12.9) with SMTP id h487m7bg023192
	for <pgsql-sql@postgresql.org>; Thu, 8 May 2003 10:48:08 +0300
Received: from share.nordlb.lv ([10.0.0.10]) by wall2.pirmabanka.lv;
	Thu, 08 May 2003 10:48:06 +0300 (EET DST)
Received: from pc311x1.nordlb.lv (localhost.localdomain [127.0.0.1])
	by nordlb.lv (8.11.6/8.11.6) with SMTP id h487m6w02120
	for <pgsql-sql@postgresql.org>; Thu, 8 May 2003 10:48:06 +0300
Received: (qmail 16898 invoked by uid 100); 8 May 2003 07:50:14 -0000
Date: Thu, 8 May 2003 10:50:14 +0300
From: "Victor Yegorov" <viy@nordlb.lv>
To: "David Loh" <david@bizsurf.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: A minute in timestamp
Message-ID: <20030508075014.GF1764@nordlb.lv>
Mail-Followup-To: "David Loh" <david@bizsurf.com>, <pgsql-sql@postgresql.org>
References: <003501c3152c$b3872a30$f92c2c0a@david>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="mojUlQ0s9EVzWg2t"
Content-Disposition: inline
In-Reply-To: <003501c3152c$b3872a30$f92c2c0a@david>
User-Agent: Mutt/1.4.1i
X-Spam-Status: No, hits=-28.1 required=5.0
	tests=BAYES_10,IN_REP_TO,PGP_SIGNATURE_2,REFERENCES,
	USER_AGENT_MUTT autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200305/160
X-Sequence-Number: 13325

--mojUlQ0s9EVzWg2t
Content-Type: text/plain; charset=koi8-r
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* David Loh <david@bizsurf.com> [08.05.2003 10:03]:
> Hi all.
> 'select * from myTable where date_part('minute',Update_Time > - now()) =
=3D 1;'

Does it helps, if you'll try this:

select * from myTable where date_part('second', now() - Update_Time) betwee=
n 55 and 65;

--=20

Victor Yegorov
IT Programmer, NORD/LB Latvija JSC
Fax (+371) 7323449, Phone (+371) 7077142, Mobile (+371) 9131883

--mojUlQ0s9EVzWg2t
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE+ugw12RKGLEykUDYRAiqFAJ4mRwdlnQymHxMeBNbbUn3SVXaU4gCgraNV
dhrCjCn67TZJHzAIPvhXRTU=
=Q4DW
-----END PGP SIGNATURE-----

--mojUlQ0s9EVzWg2t--