Common way to compare timestamp in Oracle, PostgreSQL and SQL Server

Question

I am writing an SQL query which involves finding if timestamp falls in particular range of days.

I have written that in the PostgreSQL but it doesn't works in Oracle and SQL Server:

AND creation_date < (CURRENT_TIMESTAMP - interval '5 days')
AND creation_date >= (CURRENT_TIMESTAMP - interval '15 days')

Is there are common way to compare the timestamp across different databases?

Answer 1

I'm not a SQL Server expert but I know this works on Oracle and Postgres and I suspect it may work on MSSQL but have no way to test it ATM.

AND creation_date < (CURRENT_TIMESTAMP - interval '5' day)
AND creation_date >= (CURRENT_TIMESTAMP - interval '15' day)

Or if you are using the date type instead of timestamp, you could do this but I'm pretty sure it wouldn't work on MSSQL. And the DATE type is quite different between Oracle and Pg.

AND creation_date < CURRENT_DATE - 5
AND creation_date >= CURRENT_DATE - 15

As was noted in the comments for OMG Ponies, you can only add ints to Date types not timestamps. (Oracle silently casts the timestamp to date)

Answer 2

How to compare two timestamps in postgresql, the following returns true:

select to_timestamp('2010-01-01 10:10:85.123', 'YYYY-MM-dd HH:MI:SS.MS') <
    to_timestamp('2012-01-01 10:10:85.123', 'YYYY-MM-dd HH:MI:SS.MS');

Returns true because the 2012 is after the 2010

Answer 3

I don't believe there is common syntax that'll work across all database engines. In SQL Server, you do it like this:

AND creation_date BETWEEN DateAdd(dd, -5, GetUtcDate()) AND DateAdd(dd, -15, GetUtcDate())

I'm not sure about Oracle...