I have a question about writing portable SQL code to do datetime
interval calculations. I would like to support both MySQL and PostgreSQL.
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
works in both databases.
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
MySQL specific:
SELECT * FROM session WHERE
UNIX_TIMESTAMP(lastop) < UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - $timeout
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
PostgreSQL uses a slightly different syntax (which I think is the SQL99
standard):
SELECT * FROM session WHERE
lastop < now() - interval '$timeout second'
Is there any way to do this portably?
- Ian
