db2 查询时间戳数据类型的奇怪行为 [英] Strange behaviour for db2 query for timestamp data type

问题描述

我想查询 db2 中的时间戳数据类型.我在下面写了查询

I want to query for timestamp data type in db2. I wrote query below

Select * from sample where LASTMODIFIEDDATE = timestamp('2012-04-03 07:59:50')

我没有得到上述查询的任何结果，然后我尝试了

I didn't get any result for above query , then I tried

Select * from sample where LASTMODIFIEDDATE >时间戳('2012-04-03 07:59:50')

在上面的查询中，我得到了匹配时间戳2012-04-03 07:59:50"的结果以及更大的时间戳值，例如2012-04-03 08:59:50".

In above query I got results matching timestamp '2012-04-03 07:59:50' plus for greater values of timestamp, e.g '2012-04-03 08:59:50'.

如果我得到 '>' 运算符的结果，那么为什么我没有得到 '=' 运算符的任何结果呢?有什么原因还是我写错了查询?

If I am getting results for '>' operator then why not I am not getting any results for '=' operator ? Any reasons or am I writing wrong query ?

谢谢！

推荐答案

不，DB2 存储时间戳的完整值，包括小数秒.您可能希望将系统显示时间戳的格式更改为包括毫秒在内的格式.

No, DB2 stores the full value of the timestamp, including the fractional seconds. You may wish to change the format the system displays timestamps in to something that includes milliseconds.

尝试改用这个:

SELECT * 
FROM Sample
WHERE lastModifiedDate >= TIMESTAMP('2012-04-03 07:59:50')
AND lastModifiedDate < TIMESTAMP('2012-04-03 07:59:50)' + 1 SECONDS

除非您拥有时间戳的 full 值，包括毫秒，否则您将获得一个范围 - 在访问一系列数据时，请使用 'lower-bound 包含，上限排除'.

Unless you have the full value of the timestamp, including milliseconds, you're going to be getting a range - when accessing a range of data, use 'lower-bound inclusive, upper-bound exclusive'.

这篇关于db2 查询时间戳数据类型的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！