Geek asks and answers

DB2 Timestamp Custom Instruction

I am trying to run a simple query that gets me data based on a timestamp, as follows:

SELECT * 
FROM <table_name> 
WHERE id = 1 
AND usagetime = timestamp('2012-09-03 08:03:06') 
WITH UR;

This does not seem to return a record to me, whereas this record is present in the database for id = 1.

What am I doing wrong here?

The usagetime column data type is correct, set to a timestamp.

+5
source share
1 answer

@bhamby is true. If you leave microseconds outside your timestamp value, your request will only match at usagetime 2012-09-03 08: 03: 06.000000

If you do not have the full timestamp value taken from the previous query, you can specify a range predicate that will correspond to any microsecond value during this time:

...WHERE id = 1 AND usagetime BETWEEN '2012-09-03 08:03:06' AND '2012-09-03 08:03:07'

or

...WHERE id = 1 AND usagetime >= '2012-09-03 08:03:06' 
   AND usagetime < '2012-09-03 08:03:07'
+17
source

More articles:


All Articles