Common way to compare timestamp in oracle, postgres and SQL Server

Question

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

I have written that in the postgres 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 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...

Answer 2

Oracle (I have tested both of these solutions):

AND (creation_date BETWEEN sysdate-15 AND sysdate-6)

This syntax is also valid:

AND (creation_date BETWEEN current_timestamp - INTERVAL '15' DAY 
                       AND current_timestamp - INTERVAL '6' DAY)

Note that SYSDATE and CURRENT_TIMESTAMP are synonymous (sort of - see comments).

Note that BETWEEN returns values inclusive of the bounds. Since you are looking for values which are >= date-15 but < date-5, you need to specify -15 to -6 when using BETWEEN. The answers using an upper bound of -5 with a BETWEEN expression are wrong.

Answer 3

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)