Re: query for a time interval

On Wed, Dec 21, 2005 at 11:52:56 -0800,
Mark <sendmailtomark(at)yahoo(dot)com> 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