Rao_S
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
select count(*) from errors where error_tmstp IS NULL ==> this one got me the missing rows!
ASKER
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;
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;
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/P
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/P
ASKER
i am Eastern time.
Easter Daylight Time is -4
October 1, 2010 is during daylight saving time for the US
October 1, 2010 is during daylight saving time for the US
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