I am trying to solve a database portability problem in timestamp interval calculations. The basic problem is finding all the sessions older the current time minus the timeout interval in seconds. There are lots of different ways to code this calculation in SQL but I am having trouble finding one that is portable between multiple databases. I want to support both PostgreSQL and MySQL. Postgres specific (because MySQL doesn't support EPOCH type in EXTRACT): 
SELECT * FROM session WHERE
EXTRACT(EPOCH FROM lastop) < EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - $
+timeout

[download]
MySQL specific: 
SELECT * FROM session WHERE
UNIX_TIMESTAMP(lastop) < UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - $timeout

[download]
It is possible to do the calculation directly with timestamp values. The following is SQL92 standard, only supported by MySQL: 
SELECT * FROM session WHERE
lastop < now() - interval $timeout second

[download]
PostgreSQL uses a slightly different syntax (which I think is the SQL99 standard): 
SELECT * FROM session WHERE
lastop < now() - interval '$timeout second'

[download]
Is there any way to do this portably?

In reply to Portable database interval calculation by iburrell

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.