Search code examples
sqlmysqlsql-serverdatabaseansi-sql

Sql Ansi to manage DateTime values

I'm developing a multi-database system.
I want the difference between two dates in seconds.

In SQL Server I got:
DATEDIFF(second,stardate,enddate)
In MySql:
TIME_TO_SEC(TIMEDIFF(stardate,enddate))

My question:
Does Sql Ansi have functions to manage DateTime values?
i.e.: There are datetime functions generic for all databases?

Solution

  • According to SQL:1999, date1-date0 should give you a value of type INTERVAL, a struct from which you should be able to extract YEAR, MONTH, DAY, etc.

    I've never used it and I don't think it's widely supported (though I may not be up-to-date). If you're doing time arithmetic in the database layer and you want to be cross-DBMS compatible the usual solution is simply to use integer timestamps (of whatever resolution, but Unix time is common) and plain old integer arithmetic which is completely reliable cross-platform.