[IQUG] Timestamp data type generating inconsistent results

Louie, David David.Louie at blackrock.com
Wed Nov 15 12:34:08 MST 2017


Please let me know what you think of this.


I have data in my_table for these date ranges in a LinuxIQ and SolarisIQ ( version 15.4)

RECORD_CREATE_DT is a timestamp.

myhost{dlouie}:/sybdata1/DENT_Linux/phase1=>more check_agg
select count(*) from my_table where RECORD_CREATE_DT between '1/1/2007' and '12/31/2007';
select count(*) from my_table where RECORD_CREATE_DT between '1/1/2008' and '12/31/2008';
select count(*) from my_table where RECORD_CREATE_DT between '1/1/2009' and '12/31/2009';
select count(*) from my_table where RECORD_CREATE_DT between '1/1/2010' and '12/31/2010';
select count(*) from my_table where RECORD_CREATE_DT between '1/1/2011' and '12/31/2011';
select count(*) from my_table where RECORD_CREATE_DT between '1/1/2012' and '12/31/2012';
select count(*) from my_table where RECORD_CREATE_DT between '1/1/2013' and '12/31/2013';
select count(*) from my_table where RECORD_CREATE_DT between '1/1/2014' and '12/31/2014';
select count(*) from my_table where RECORD_CREATE_DT between '1/1/2015' and '12/31/2015';
select count(*) from my_table where RECORD_CREATE_DT between '1/1/2016' and '12/31/2016';


Ran output is exactly the same for all counts between CL and QTR.  Row counts for each date range year match 100%

myhost{dlouie}:/sybdata1/DENT_Linux/phase1=>sqsh -S LinuxIQ -i check_agg -o raw.qtr.counts.out
myhost{dlouie}:/sybdata1/DENT_Linux/phase1=>sqsh -S SolarisIQ -i check_agg -o raw.clone.counts.out
myhost{dlouie}:/sybdata1/DENT_Linux/phase1=>diff raw.qtr.counts.out raw.clone.counts.out
myhost{dlouie}:/sybdata1/DENT_Linux/phase1=>

Determine if there is data past 1/1/2007 of which there are none

myhost{dlouie}:/sybdata1/DENT_Linux/phase1=>sqsh -S SolarisIQ
SolarisIQ.dlouie..2001.1> select count(*) from my_table where RECORD_CREATE_DT < '1/1/2007';
count()
--------------------
                    0

(1 row affected)
SolarisIQ.dlouie..2002.1> quit
myhost{dlouie}:/sybdata1/DENT_Linux/phase1=>sqsh -S LinuxIQ
LinuxIQ.dlouie..2001.1>  select count(*) from my_table where RECORD_CREATE_DT < '1/1/2007';
count()
--------------------
                    0

(1 row affected)
LinuxIQ.dlouie..2002.1>

RUN this  and seeing diffs in row count despite individual year counts matching and no data past 1/1/2007 on either server.

LinuxIQ.dlouie..2002.2> select count(*) from my_table where RECORD_CREATE_DT < '1/1/2017';
count()
--------------------
            450391996

(1 row affected)
LinuxIQ.dlouie..2003.1> quit
myhost{dlouie}:/sybdata1/DENT_Linux/phase1=>sqsh -S SolarisIQ
SolarisIQ.dlouie..2001.1> select count(*) from my_table where RECORD_CREATE_DT < '1/1/2017';
count()
--------------------
            451664788

(1 row affected)
SolarisIQ.dlouie..2002.1>


Thanks
David


This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers for further information.  Please refer to http://www.blackrock.com/corporate/en-us/compliance/privacy-policy for more information about BlackRock’s Privacy Policy.
For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/en-us/about-us/contacts-locations.

© 2017 BlackRock, Inc. All rights reserved.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20171115/a12a7f2f/attachment.html>


More information about the IQUG mailing list