I am trying to run a simple query which gets me data based on 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 datatype of the column usagetime is correct, set to timestamp.
@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 >= '2012-09-03 08:03:06'
AND usagetime < '2012-09-03 08:03:07'
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')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With