比较 DATE 类型的值 - Oracle
有没有什么方法可以比较日期值来检查一个值是否在另一个值之前？ 例如，我如何知道以下行中哪一个先出现
SEQ CREATION_DTM
--------------------
234 2011-03-26 22:59:03
235 2011-03-26 22:59:03
上述数据的列被声明为数据类型 DATE。仔细阅读后发现 DATE 数据类型不存储毫秒。这是否意味着 我无法比较上述两个日期来找出哪个日期早于另一个日期？
编辑
我在 Solaris 上使用 Oracle 10G。
Is there any way of comparing to date values to check if one is before the other?
For example how do i know which came first on the following rows
SEQ CREATION_DTM
--------------------
234 2011-03-26 22:59:03
235 2011-03-26 22:59:03
The column for the above data is declarad as datatype DATE. Having read around it appears that the DATE datatype does not store milliseconds. Does this mean
i cant compare the above two dates to find out which one is before the other?
EDIT
I am using Oracle 10G on Solaris.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
日期精度仅精确到最接近的秒，因此如果您有两个与该精度相同的日期，则无法区分或排序它们。为了获得更精确的结果，您需要将它们存储为时间戳。在更一般的情况下，日期确实不同，您可以像数字一样对它们进行比较和排序。当你得到两个相同的结果时，结果是不确定的；在这种情况下，如果您按
CREATION_DTM排序，那么您无法可靠地预测结果是按234,235还是235,234排序。正如贾斯汀所建议的那样，您需要确定一种打破平局的方法。DATEprecision only goes to the nearest second, so if you have two dates that are the same to that precision then you can't distinguish between or order them. To get any more precision you'd need to store them asTIMESTAMP.In the more general case where the dates do differ you can compare and order them much like numbers. When you get two the same the results are uncertain; in you case if you ordered by
CREATION_DTMthen you couldn't reliably predict whether the results would be ordered as234,235or235,234. You would need to determine a way to break a tie, as Justin has suggested.DATE 最多只存储秒。因此，如果在同一秒内插入两行，您无法根据
CREATION_DTM列确定哪一行先插入。如果您想要这种级别的分辨率，最好使用TIMESTAMP [WITH [LOCAL] TIME ZONE]列，如果主机操作系统提供，该列将存储最多 9 位十进制数字的时间部分该粒度级别（大多数 Unix 系统将提供微秒分辨率）。在您的情况下，假设您没有使用 RAC 并且使用 Oracle 序列来填充
SEQ列，您可以使用该列来打破平局。如果这两行插入到不同的事务中，尚未更新，并且表是使用ROWDEPENDENCIES构建的，您还可以使用ORA_ROWSCN来打破平局。A DATE only stores up to the second. So if two rows are inserted in the same second, you can't determine which came first based on the
CREATION_DTMcolumn. If you want that level of resolution, you'd be better served with aTIMESTAMP [WITH [LOCAL] TIME ZONE]column which will store the time component up to 9 decimal digits if the host operating system provides that level of granularity (most Unix systems will provide microsecond resolution).In your case, assuming that you're not using RAC and that you are using an Oracle sequence to populate the
SEQcolumn, you could use that column to break the tie. If the two rows were inserted in different transactions, haven't been updated, and the table was built withROWDEPENDENCIES, you could also potentially use theORA_ROWSCNto break the tie.似乎时间戳数据类型适合您的查询。
谢谢
Seems timestamp data type will be appropriate for you query..
Thanks