Oracle、PostgreSQL 和 SQL Server 中比较时间戳的常用方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2505382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Common way to compare timestamp in Oracle, PostgreSQL and SQL Server
提问by Pratik Garg
I am writing an SQL query which involves finding if timestamp falls in particular range of days.
我正在编写一个 SQL 查询，它涉及查找时间戳是否落在特定的天数范围内。
I have written that in the PostgreSQL but it doesn't works in Oracle and SQL Server:
我已经在 PostgreSQL 中编写了它，但它在 Oracle 和 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?
有没有通用的方法来比较不同数据库的时间戳？
采纳答案by Scott Bailey
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.
我不是 SQL Server 专家，但我知道这适用于 Oracle 和 Postgres，我怀疑它可能适用于 MSSQL，但无法在 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.
或者，如果您使用的是日期类型而不是时间戳，您可以这样做，但我很确定它不适用于 MSSQL。Oracle 和 Pg 的 DATE 类型也有很大不同。
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)
正如 OMG Ponies 的评论中所指出的，您只能将整数添加到日期类型而不是时间戳。（Oracle 默默地将时间戳转换为日期）
回答by Eric Leschinski
How to compare two timestamps in postgresql, the following returns true:
如何在postgresql中比较两个时间戳，以下返回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 2012is after the 2010
返回 true 因为在2012之后2010
回答by Dean Harding
I don't believe there is common syntax that'll work across all database engines. In SQL Server, you do it like this:
我不相信存在适用于所有数据库引擎的通用语法。在 SQL Server 中，您可以这样做：
AND creation_date BETWEEN DateAdd(dd, -5, GetUtcDate()) AND DateAdd(dd, -15, GetUtcDate())
I'm not sure about Oracle...
我不确定甲骨文...
回答by Igby Largeman
Oracle(I have tested both of these solutions):
Oracle（我已经测试了这两种解决方案）：
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).
请注意， SYSDATE 和 CURRENT_TIMESTAMP 是同义词（有点 - 请参阅评论）。
Note that BETWEEN returns values inclusiveof 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.
请注意， BETWEEN 返回包含边界的值。由于您正在寻找>=日期为 15 但<日期为 5 的值，因此在使用 BETWEEN 时需要指定 -15 到 -6。使用上限 -5 和 BETWEEN 表达式的答案是错误的。
回答by Usman
select * from timing where (db_time < CURRENT_DATE) AND (db_time > (CURRENT_DATE - INTERVAL '1' DAY)) ;
回答by Jonathan L
Again this is specific for Oracle, assume intime is TIMESTAMP(3) data type, you can do this
同样这是针对 Oracle 的，假设 intime 是 TIMESTAMP(3) 数据类型，您可以这样做
select * from MYTABLE where intime >= to_timestamp('2014-10-01 7:00:56', 'YYYY-MM-dd HH24:MI:SS')
