Query in SQL using between Timestamp pulling wrong data

Question

I am using this query:

  SELECT ts as "TimeStamp", 
         stat as "Status"
    FROM myTable
   WHERE stat = 'O'
     AND source = 'Source1'
     AND ts BETWEEN TO_TIMESTAMP('2013-10-05','yyyy-mm-dd') AND
                    TO_TIMESTAMP('2013-10-06','yyyy-mm-dd')

And also tried:

  SELECT ts as "TimeStamp", 
         stat as "Status"
    FROM myTable
   WHERE stat = 'O'
     AND source = 'Source1'
     AND ts >= TO_TIMESTAMP('2013-10-05','yyyy-mm-dd') AND ts <
                    TO_TIMESTAMP('2013-10-06','yyyy-mm-dd')

It returns 0 records, but if I do

  SELECT ts as "TimeStamp", 
         stat as "Status"
    FROM myTable
   WHERE stat = 'O'

I can clearly identify 5 records. Apparently the TO_TIMESTAMP is not working properly I am hoping someone might be able to help identify the proper fix

Edit: To clarify, I only want the timeframe for 10/5 not including 10/6 Sorry pasted wrong results Also the field is of type TIMESTAMP(6)

asantaballa · Accepted Answer · 2013-10-11 14:56:49Z

4

Because the TO time stamp has time even though you're not specifying. So it is selecting only up to the very beginning of the lat day. Either specify time 23:59:59 and subseconds as required, or do less than the next day.

...
AND ts >= TO_TIMESTAMP('2013-10-05','yyyy-mm-dd') 
AND ts <  TO_TIMESTAMP('2013-10-07','yyyy-mm-dd')

edited Oct 11, 2013 at 14:56

answered Oct 11, 2013 at 14:50

asantaballa

4,0581 gold badge24 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

asantaballa Over a year ago

Looking, but I do notice in your edited post you have the second clause as less than 2013-10-06. If that was the try then it would still be wrong. Needs to be less than the next day, so notice my example uses 2013-10-07.

dElo · Accepted Answer · 2013-10-11 14:51:26Z

0

Try: AND ts BETWEEN TO_DATE('2013-10-05','YYYY-MM-DD') AND TO_DATE('2013-10-06','YYYY-MM-DD')

And mind that it will get you results from the 10-05 at 00:00:00 to the 10-06 at 00:00:00 If you don't specify the HH MI SS mask.

Date masks are case sensitivve and TO_DATE will work better.

answered Oct 11, 2013 at 14:51

dElo

12410 bronze badges

Collectives™ on Stack Overflow

Query in SQL using between Timestamp pulling wrong data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related