Portable SQL for date arithmetic and comparison

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 correc开发者_运维百科t 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)?

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

Although it is standard SQL, there are DBMS (such as SQL Server) that do not support the standard. So the answer is yes and no.

Although I believe the ANSI standard way of writing an interval literal is: INTERVAL '15' MINUTE, not the way you wrote it

SQL Server does not support this. You would have to use the dataadd function. So the answer is no this is not protable SQL.