why the diff in count with timestamp column?

comming back to the same question...
when i run this:
select count(*) from errors where error_tmstp >= to_timestamp ('01-Oct-10 00:00:00.000000', 'DD-Mon-RR HH24:MI:SS.FF');  --count = 1626417,
when i run this:
select count(*) from errors where error_tmstp < to_timestamp ('01-Oct-10 00:00:00.000000', 'DD-Mon-RR HH24:MI:SS.FF');  --count = 1080186.
the total count on the table is: 2706718, i am missing 115 rows, why is that?
I have to delete all rows prior to Oct 10th.
the field format is: ERROR_TMSTP  TIMESTAMP(6) WITH TIME ZONE,  -- 10/1/2010 12:07:11.000000 AM -04:00
the table is  active table, rows are getting written to it constatly but only about 10 rows in a couple of minutes.
Rao_SAsked:
Who is Participating?
 
sdstuberCommented:
NULL values  are neither >=  nor <  your timestamp literal

select count(*) from errors where error_tmstp IS NULL

should return your missing rows
0
 
sdstuberCommented:
also note,  since your timstamps have timezone info in them,  you ought to include timezone in your select and delete criteria.


that's unrelated to your missing row problem, but you'll want to do it when you do the delete to be sure you're keeping the correct data
0
 
Rao_SAuthor Commented:
select count(*) from errors where error_tmstp IS NULL  ==> this one got me the missing rows!

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rao_SAuthor Commented:
is this the right conversion for greenwich time?

select count(*) from errors where error_tmstp < to_timestamp_tz ('01-OCT-10 01.00.00.100000 PM -05:00',  'DD-MON-RR HH.MI.SSXFF PM TZR ') OR error_tmstp IS NULL;
0
 
sdstuberCommented:
depends on where you are and if you are observing Daylight Saving Time or not.

Your EE profile says Pacific time  which is -7 including DST.

If your data is for the US, then Oct 1, 2010 is a DST time, but still don't know if Eastern/Central/Mountain/Pacific or other
0
 
Rao_SAuthor Commented:
i am Eastern time.
0
 
sdstuberCommented:
Easter Daylight Time is  -4

October 1, 2010 is during daylight saving time for the US
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.