pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedA minute in timestamp
3+ messages / 3 participants
[nested] [flat]
* A minute in timestamp
@ 2003-05-08 06:40 David Loh <david@bizsurf.com>
2003-05-08 07:24 ` Re: A minute in timestamp Richard Huxton <dev@archonet.com>
2003-05-08 07:50 ` Re: A minute in timestamp Victor Yegorov <viy@nordlb.lv>
0 siblings, 2 replies; 3+ messages in thread
From: David Loh @ 2003-05-08 06:40 UTC (permalink / raw)
To: pgsql-sql
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 := 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) ?
So far, I'd tried 'select * from myTable where date_part('minute',Update_Time - now()) = 1;', but this SQL statement will only return result if the hour:minute:milli-seconds MATCH EXACTLY !! (12:38.51451->12:39.51451 = match, the rest aren't). Then I switch to 'select * from myTable where Update_Time = 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.
Thanks in advance.
Regards,
David Loh
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: A minute in timestamp
2003-05-08 06:40 A minute in timestamp David Loh <david@bizsurf.com>
@ 2003-05-08 07:24 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2003-05-08 07:24 UTC (permalink / raw)
To: David Loh <david@bizsurf.com>; pgsql-sql
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 := 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) ?
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()
functions you get in most systems. I find them clearer now I've got used to
them.
--
Richard Huxton
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: A minute in timestamp
2003-05-08 06:40 A minute in timestamp David Loh <david@bizsurf.com>
@ 2003-05-08 07:50 ` Victor Yegorov <viy@nordlb.lv>
1 sibling, 0 replies; 3+ messages in thread
From: Victor Yegorov @ 2003-05-08 07:50 UTC (permalink / raw)
To: David Loh <david@bizsurf.com>; +Cc: pgsql-sql
* David Loh <david@bizsurf.com> [08.05.2003 10:03]:
> Hi all.
> 'select * from myTable where date_part('minute',Update_Time > - now()) = 1;'
Does it helps, if you'll try this:
select * from myTable where date_part('second', now() - Update_Time) between 55 and 65;
--
Victor Yegorov
IT Programmer, NORD/LB Latvija JSC
Fax (+371) 7323449, Phone (+371) 7077142, Mobile (+371) 9131883
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-05-08 07:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-08 06:40 A minute in timestamp David Loh <david@bizsurf.com>
2003-05-08 07:24 ` Richard Huxton <dev@archonet.com>
2003-05-08 07:50 ` Victor Yegorov <viy@nordlb.lv>
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