Thread: How should I phrase this?

I've got a table that I insert a value and a timestamp "now()".  Then I
want to delete entries that are older than 30 minutes old.  After some
experimentation, I found the following works, but it looks awkward and
backwards.  Is there a better, more readable way of phrasing this or
rewriting it?

DELETE
FROM           generators
WHERE          age(now(),started) > interval '30 minutes'


--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
"Nobody can be told what the dominatrix is, they have to see it for themselves"

An alternative, though I'm not sure it's better in any way :

DELETE FROM generators WHERE started + '30 minutes'::interval <= now();

-Mitch

----- Original Message -----
From: "Paul Tomblin" <ptomblin@xcski.com>
To: <pgsql-general@postgresql.org>
Sent: Sunday, July 08, 2001 2:12 PM
Subject: [GENERAL] How should I phrase this?


> I've got a table that I insert a value and a timestamp "now()".  Then I
> want to delete entries that are older than 30 minutes old.  After some
> experimentation, I found the following works, but it looks awkward and
> backwards.  Is there a better, more readable way of phrasing this or
> rewriting it?
>
> DELETE
> FROM           generators
> WHERE          age(now(),started) > interval '30 minutes'
>
>
> --
> Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
> "Nobody can be told what the dominatrix is, they have to see it for
themselves"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Quoting Mitch Vincent (mvincent@cablespeed.com):
> An alternative, though I'm not sure it's better in any way :
>
> DELETE FROM generators WHERE started + '30 minutes'::interval <= now();

Well, that looks a little nicer.  Thanks.

--
Paul Tomblin <ptomblin@xcski.com>, not speaking for anybody
Q:  How did you get into artificial intelligence?
A:  Seemed logical -- I didn't have any real intelligence.

Actually, I'd recommend

DELETE FROM generators WHERE started <= now() - '30 minutes'::interval;

This might or might not seem more natural than the other way, but it
has the advantage that there's at least a potential to make use of an
index on the "started" column.  In practice, because now() is considered
a non-constant-foldable function by the optimizer, you have to cheat a
little bit to make the righthand side reduce to a constant so that
indexing will actually work.  See past discussions in the archives.

            regards, tom lane