Re: [GENERAL] query for a time interval
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
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
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
Re: [GENERAL] query for a time interval
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
[GENERAL] query for a time interval
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