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--