pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate comparison question
4+ messages / 3 participants
[nested] [flat]
* Date comparison question
@ 2003-05-20 16:22 Jan Bodey <jan_bodey@hotmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Jan Bodey @ 2003-05-20 16:22 UTC (permalink / raw)
To: pgsql-sql
Hi
I require a query that will see if a stored date is more than a certain
interval before the current date. Using an interval of 10 hours, the query
would be something like:
SELECT * FROM utable
WHERE now() - utimestamp > interval '10 hours';
The problem I am having is that the interval is not fixed, and I would like
to be able to store it as a value in a database table. The unit of the
interval can, however, be fixed, and will almost certainly be fixed to
hours.
I have not been able to find a way of doing what I require, does anybody
have any ideas to what I need to do?
Cheers.
Jan.
_________________________________________________________________
Tired of 56k? Get a FREE BT Broadband connection
http://www.msn.co.uk/specials/btbroadband
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Date comparison question
@ 2003-05-20 16:57 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Jan Bodey <jan_bodey@hotmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-05-20 16:57 UTC (permalink / raw)
To: Jan Bodey <jan_bodey@hotmail.com>; +Cc: pgsql-sql
On Tue, 20 May 2003, Jan Bodey wrote:
> Hi
>
> I require a query that will see if a stored date is more than a certain
> interval before the current date. Using an interval of 10 hours, the query
> would be something like:
>
> SELECT * FROM utable
> WHERE now() - utimestamp > interval '10 hours';
>
> The problem I am having is that the interval is not fixed, and I would like
> to be able to store it as a value in a database table. The unit of the
> interval can, however, be fixed, and will almost certainly be fixed to
> hours.
>
> I have not been able to find a way of doing what I require, does anybody
> have any ideas to what I need to do?
If you assume that you're just going to stick one row in the interval
table, I think you can do something kinda like:
create table interv(i interval);
insert into interv values ('10 hours');
analyze interv;
select utable.* from utable, interv
where now()-utimestamp>i;
This could be made more complicated if you want to have multiple interval
values, etc. Also, neither of the above is probably terribly fast if
utable is big.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Date comparison question
@ 2003-05-20 17:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Bodey <jan_bodey@hotmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-05-20 17:36 UTC (permalink / raw)
To: Jan Bodey <jan_bodey@hotmail.com>; +Cc: pgsql-sql
"Jan Bodey" <jan_bodey@hotmail.com> writes:
> SELECT * FROM utable
> WHERE now() - utimestamp > interval '10 hours';
> The problem I am having is that the interval is not fixed, and I would like
> to be able to store it as a value in a database table. The unit of the
> interval can, however, be fixed, and will almost certainly be fixed to
> hours.
Do you not want to store the values as type interval? That would seem
like the most straightforward answer. If you really want to store them
as plain numbers, there's a float-times-interval operator, so you could
do something like
... > table.float8col * interval '1 hour';
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Date comparison question
@ 2003-05-20 17:39 Jan Bodey <jan_bodey@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jan Bodey @ 2003-05-20 17:39 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql
The problem I have with having the values as type interval, is that I am
restricted by using Torque. I can't work out what Torque type (if any!)
maps to Interval! Your 'float-times-interval' operator may well get me
around that though, although I will not test until tomorrow, high time I
left the office!!
Cheers
Jan.
>
>"Jan Bodey" <jan_bodey@hotmail.com> writes:
> > SELECT * FROM utable
> > WHERE now() - utimestamp > interval '10 hours';
>
> > The problem I am having is that the interval is not fixed, and I would
>like
> > to be able to store it as a value in a database table. The unit of the
> > interval can, however, be fixed, and will almost certainly be fixed to
> > hours.
>
>Do you not want to store the values as type interval? That would seem
>like the most straightforward answer. If you really want to store them
>as plain numbers, there's a float-times-interval operator, so you could
>do something like
> ... > table.float8col * interval '1 hour';
>
> regards, tom lane
_________________________________________________________________
Express yourself with cool emoticons - download MSN Messenger today!
http://www.msn.co.uk/messenger
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-20 17:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-20 16:22 Date comparison question Jan Bodey <jan_bodey@hotmail.com>
2003-05-20 16:57 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-20 17:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-20 17:39 Re: Date comparison question Jan Bodey <jan_bodey@hotmail.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