Menu
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?
662
Postgres DATE data type Postgres uses the DATE data type for storing different dates in YYYY-MM-DD format. It uses 4 bytes for storing a date value in a column. You can design a Postgres table with a DATE column and use the keyword DEFAULT CURRENT_DATE to use the current system date as the default value in this column.
By casting "TimeStamp" to date you throw away the time part of the timestamp, so all values within one day will be considered equal and are returned in random order. It is by accident that the first rows appear in the order you desire. Don't cast to date in the ORDER BY clause if the time part is relevant for sorting.
TIMESTAMP is the same as DATE , except it has added fractional seconds precision. The biggest difference: DATE is accurate to the second and doesn't have fractional seconds. TIMESTAMP has fractional seconds.
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)
119
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
42
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...
3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
