Strange behaviour for db2 query for timestamp data type

Ask Time：2012-06-25T01:44:59 Author：Sachin Doiphode

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 > timestamp('2012-04-03 07: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 ?

Thanks !

Author:Sachin Doiphode，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/11179744/strange-behaviour-for-db2-query-for-timestamp-data-type

Clockwork-Muse :

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.\n\nTry using this instead: \n\nSELECT * \nFROM Sample\nWHERE lastModifiedDate >= TIMESTAMP('2012-04-03 07:59:50')\nAND lastModifiedDate < TIMESTAMP('2012-04-03 07:59:50)' + 1 SECONDS\n\n\nUnless 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'.",

2012-06-25T19:16:22

Strange behaviour for db2 query for timestamp data type