CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
user2428207
user2428207

Reputation: 825

Loading Data from table with between timestamp

I'm having a Source tbl with a column timestamp (DD.MM.YYYY HH24:MI:SS)

Now I want to load data between, lets say, 01.11.2013 and 05.11.2013.

When I use i.e. the following:

select count(1) from tbl where a.timestamp between trunc(to_date('1.11.2013', 'DD.MM.YYYY')) AND trunc(to_date('05.11.2013', 'DD.MM.YYYY'))

So the output is 312.020

When doing a select count and grouping by DD.MM.YYYY I get a higher number for these days in sum than with the query above. What could be the reason.

Upvotes: 0

Views: 104

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

It's a little hard to tell but it seems like you're losing some or all of the data from the 5th because you're ignoring the time portion. When you do:

a.timestamp between to_date('1.11.2013', 'DD.MM.YYYY')
  AND to_date('05.11.2013', 'DD.MM.YYYY')

(I've removed the trunc() as they don't add anything), you're counting from 01.11.2013 00:00:00 to 05.11.2013 00:00:00, not up to 05.11.2013 23:59:59 which might be what you're expecting.

You could compare with trunc(timestamp) instead but that can have performance implications. You could do:

a.timestamp between to_date('1.11.2013 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
  AND to_date('05.11.2013 23:59:59', 'DD.MM.YYYY HH24:MI:SS')

... or:

 a.timestamp >= to_date('1.11.2013', 'DD.MM.YYYY')
  AND a.timestamp < to_date('06.11.2013', 'DD.MM.YYYY')

noting that the 'to' date in the second version is now the 6th, so that it goes up to but not beyond the end of the 5th.

I'm assuming from the description that your field is of type DATE; calling it timestamp is a bit confusing since there is a TIMESTAMP data type as well. If it is actually a TIMESTAMP then the second version will still work, but the first would need to take the fractional seconds into account and go up to 23:59:59.999999999, and both should use a to_timestamp() call instead of to_date().

a.timestamp between to_timestamp('1.11.2013 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
  AND to_timestamp('05.11.2013 23:59:59.999999999', 'DD.MM.YYYY HH24:MI:SS.FF9')

Depending on the precision of your timestamp field, you might not need all the 9s, and could perhaps use FF3 or FF6. More in the docs.

Upvotes: 1

Related Questions