Re: [GENERAL] query for a time interval

2005-12-23 Thread Tom Lane 
Michael Fuhr [EMAIL PROTECTED] writes:
 Unless I'm missing something that wouldn't use an index either,
 because the planner wouldn't know what value to compare start_date
 against without hitting each row to find that row's time_to_live.
 But something like this should be able to use an expression index
 on (start_date + time_to_live):

 WHERE start_date + time_to_live  now()

Or perhaps simpler, get rid of time_to_live in favor of an end_date
column, which could be indexed directly.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] query for a time interval

2005-12-23 Thread Bruno Wolff III 
On Wed, Dec 21, 2005 at 11:52:56 -0800,
  Mark [EMAIL PROTECTED] wrote:
 Hello everybody,
 
 I'm looking for an elegant SQL statement that will work in
 Postgresql, MySQL and ORACLE.
 The query will be executed by Java client.
 
 To have this query for Postgresql is priority number one.
 
 
 In this query I try to get a list of message Ids that expired.
 
 time_to_live is in seconds.
 
 SELECT id
   FROM mq
  WHERE now - start_date  time_to_live;

An interval is not going to be comparable to an integer without casting.
If you use explicit casting the query probably won't be portable. If there
is some implicit cast that makes the above SQL valid, it is probably a cast
to text which won't do what you want.

 
 I have a following table:
 
 CREATE TABLE mq
 {
 msg_id INTEGER,
 retry_date  TIMESTAMP NOT NULL DEFAULT ('now'::text)::timestamp(1),
 start_date  TIMESTAMP NOT NULL DEFAULT ('now'::text)::timestamp(1),
 time_to_live INTEGER
 }

Can you make time_to_live an interval?

 
 Thanks!
 Mark.
 
 __
 Do You Yahoo!?
 Tired of spam?  Yahoo! Mail has the best spam protection around 
 http://mail.yahoo.com 
 
 ---(end of broadcast)---
 TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] query for a time interval

2005-12-22 Thread Jim C. Nasby 
On Wed, Dec 21, 2005 at 11:52:56AM -0800, Mark wrote:
 SELECT id
   FROM mq
  WHERE now - start_date  time_to_live;

The problem is you can't use an index on this, because you'd need to
index on (now() - start_date), which obviously wouldn't work. Instead,
re-write the WHERE as:

WHERE start_date  now() - time_to_live
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] query for a time interval

2005-12-22 Thread Michael Fuhr 
On Thu, Dec 22, 2005 at 09:47:11AM -0600, Jim C. Nasby wrote:
 On Wed, Dec 21, 2005 at 11:52:56AM -0800, Mark wrote:
  SELECT id
FROM mq
   WHERE now - start_date  time_to_live;
 
 The problem is you can't use an index on this, because you'd need to
 index on (now() - start_date), which obviously wouldn't work. Instead,
 re-write the WHERE as:
 
 WHERE start_date  now() - time_to_live

Unless I'm missing something that wouldn't use an index either,
because the planner wouldn't know what value to compare start_date
against without hitting each row to find that row's time_to_live.
But something like this should be able to use an expression index
on (start_date + time_to_live):

WHERE start_date + time_to_live  now()

-- 
Michael Fuhr

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] query for a time interval

2005-12-21 Thread Mark 
Hello everybody,

I'm looking for an elegant SQL statement that will work in
Postgresql, MySQL and ORACLE.
The query will be executed by Java client.

To have this query for Postgresql is priority number one.


In this query I try to get a list of message Ids that expired.

time_to_live is in seconds.

SELECT id
  FROM mq
 WHERE now - start_date  time_to_live;

I have a following table:

CREATE TABLE mq
{
msg_id INTEGER,
retry_date  TIMESTAMP NOT NULL DEFAULT ('now'::text)::timestamp(1),
start_date  TIMESTAMP NOT NULL DEFAULT ('now'::text)::timestamp(1),
time_to_live INTEGER
}

Thanks!
Mark.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match