Portable SQL for date arithmetic and comparison

Question

SQL

A table for storing user sessions:

CREATE TABLE sessions (
    user_id INT,
    expires TIMESTAMP
);

To create a session:

INSERT INTO sessions (user_id, expires) VALUES (:user_id,
    CURRENT_TIMESTAMP + INTERVAL '+15 minutes');

To retrieve a session:

SELECT * FROM sessions WHERE user_id = :user_id AND
    CURRENT_TIMESTAMP < expires;

Questions

• Is this portable SQL?
  Will this work on any database available through the PHP PDO extension (excluding SQLite)?

• Is this correct in different timezones? Across a daylight saving time adjustment?
  Any problem mixing CURRENT_TIMESTAMP (which includes timezone information) with a TIMESTAMP column (which doesn't)?

Answer 1

Date/Time values are pretty problematic across SQL dialects, in my experience.

• Oracle supports DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.
  • DB2 supports DATE, TIME and TIMESTAMP.
  • SQL Server supports DATETIME and (recently) DATE. SQL Server has a TIMESTAMP data type, but it's not what you think it is.
  • MySQL supports DATE, DATETIME, TIMESTAMP, TIME and YEAR
  • PostgresSQL supports both TIMESTAMP and TIME, with and without timezone, along with DATE

If I had to deal with date/time values in an absolutely portable way, I'd store the value as a char/varchar in ISO8601 compact form

YYYYMMDDTHHMMSS[±HH:MM]

where the time component is 24h/military time. If you need timezone support, include the offset from UTC, or 'Z' to indicate Zulu (UTC) time. Strictly speaking, without the 'Z' suffix, the ISO 8601 date/time value is supposed to be interpreted as local time.

Depending on your needs, it might be worthwhile to break the date and time components out into separate columns.

ISO8601 gives you

• portability
• proper collation/comparison
• ease of parsing