在Oracle，PostgreSQL和SQL Server中比较时间戳的常用方法 [英] Common way to compare timestamp in Oracle, PostgreSQL and SQL Server

问题描述

我正在编写一个SQL查询，其中包括查找时间戳是否在特定的天数范围内。

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

我在PostgreSQL中写过，但在Oracle和SQL Server中无效：

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?

推荐答案

我不是一个SQL服务器专家，但我知道这对Oracle和Postgres有效，我怀疑它可能在MSSQL上工作，但是无法测试ATM。

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)

或者如果您使用日期类型而不是时间戳，则可以这样做，但我确信它不适用于MSSQL。而且Oracle和Pg之间的DATE类型是完全不同的。

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

正如评论中所述对于OMG小马，您只能将日期类型的int添加到时间戳。 （Oracle默认地将时间戳刻录到日期）

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)

这篇关于在Oracle，PostgreSQL和SQL Server中比较时间戳的常用方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！