DB2 Timestamp select statement

Question

I am trying to run a simple query which gets me data based on timestamp  as follows   SELECT    FROM  lt table name gt   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 datatype of the column usagetime is correct  set to timestamp

User · Answer

You might want to use TRUNC function on your column when comparing with string format, so it compares only till seconds, not milliseconds.

SELECT * FROM <table_name> WHERE id = 1 
AND TRUNC(usagetime, 'SS') = '2012-09-03 08:03:06';

If you wanted to truncate upto minutes, hours, etc. that is also possible, just use appropriate notation instead of 'SS':

hour ('HH'), minute('MI'), year('YEAR' or 'YYYY'), month('MONTH' or 'MM'), Day ('DD')

User · Answer

bhamby is correct  By leaving the microseconds off of your timestamp value  your query would only match on a usagetime of 2012-09-03 08 03 06 000000  If you don t have the complete timestamp value captured from a previous query  you can specify a ranged predicate that will match on any microsecond value for that 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  gt    2012-09-03 08 03 06      AND usagetime  lt   2012-09-03 08 03 07

[db2] DB2 Timestamp select statement

Examples related to db2

Examples related to timestamp