AnsweredAssumed Answered

Selecting from PIAvg returning a nano/microsecond, why?

Question asked by markiejd on Feb 4, 2015
Latest reply on Feb 23, 2015 by markiejd

I'm running the following from SQL

 

SELECT * FROM OpenQuery([PI], 'SELECT    Tag,Time, Value

       FROM          PIArchive..PIAvg

       WHERE         Tag = ''TESTTAG''

       AND                  Time  >       ''08-12-2014 20:00''

       AND time  <=  ''01-01-2015 00:00''

       AND                  Timestep = ''240m''')

 

And I'm getting the following :-

tagtimevalue
TESTTAGE2014-12-08 20:00:00.00001000.86523895674913
TESTTAGE2014-12-09 00:00:00.00001002.48799477454177
TESTTAGE2014-12-09 04:00:00.00001002.4745603913731
TESTTAGE2014-12-09 08:00:00.00001002.46351917280091
TESTTAGE2014-12-09 12:00:00.00001002.52878820343055
TESTTAGE2014-12-09 16:00:00.00001002.56503107464624
TESTTAGE2014-12-09 20:00:00.00001002.58847232778215
TESTTAGE2014-12-10 00:00:00.00001002.54459329407451
TESTTAGE2014-12-10 04:00:00.00001002.52623585674634
TESTTAGE2014-12-10 08:00:00.00001002.51432519391274
TESTTAGE2014-12-10 12:00:00.00001002.49144708813472
TESTTAGE2014-12-10 16:00:00.00001002.46550831420812
TESTTAGE2014-12-10 20:00:00.00001002.51938932950298
TESTTAGE2014-12-11 00:00:00.00001002.43062776181528
TESTTAGE2014-12-11 04:00:00.00001002.48961288165715
TESTTAGE2014-12-11 08:00:00.00001002.44111843095533
TESTTAGE2014-12-11 12:00:00.00001002.48492772823814
TESTTAGE2014-12-11 16:00:00.00001002.54189082994989
TESTTAGE2014-12-11 20:00:00.00001002.49447977857783
TESTTAGE2014-12-12 00:00:00.00001002.52632289708244
TESTTAGE2014-12-12 04:00:00.00001002.50787251060859
TESTTAGE2014-12-12 08:00:00.00001003.2702869936714
TESTTAGE2014-12-12 12:00:00.00001002.70039140072028
TESTTAGE2014-12-12 16:00:00.00001003.45905933043444
TESTTAGE2014-12-12 20:00:00.00001004.43400015491931
TESTTAGE2014-12-13 00:00:00.00001004.27678500517388
TESTTAGE2014-12-13 04:00:00.00001004.22289084054543
TESTTAGE2014-12-13 08:00:00.00001004.17908937387413
TESTTAGE2014-12-13 12:00:00.00001004.12030092023489
TESTTAGE2014-12-13 16:00:00.00001004.08662657897587
TESTTAGE2014-12-13 20:00:00.00001004.18698843592463

 

 

and so on....

 

Yet

 

SELECT * FROM OpenQuery([PI], 'SELECT    Tag,Time, Value

       FROM          PIArchive..PIAvg

       WHERE         Tag = ''TESTTAG''

       AND                  Time  >       ''01-01-2014 00:00''

       AND time  <=  ''07-12-2014 16:00''

       AND                  Timestep = ''240m''')

results in

 

 

 

tag time value

TESTTAG 2014-01-01 04:00:00.0000000 4.02610156478153

TESTTAG 2014-01-01 08:00:00.0000000 4.04350070406993

TESTTAG 2014-01-01 12:00:00.0000000 4.01995285878579

TESTTAG 2014-01-01 16:00:00.0000000 3.98154862473408

TESTTAG 2014-01-01 20:00:00.0000000 3.96526499559482

TESTTAG 2014-01-02 00:00:00.0000000 3.98014522125324

TESTTAG 2014-01-02 04:00:00.0000000 3.99749899082714

TESTTAG 2014-01-02 08:00:00.0000000 4.00226875270406

TESTTAG 2014-01-02 12:00:00.0000000 4.03300642672512

TESTTAG 2014-01-02 16:00:00.0000000 4.03468594431877

TESTTAG 2014-01-02 20:00:00.0000000 4.01158966150549

TESTTAG 2014-01-03 00:00:00.0000000 3.96916770266162

TESTTAG 2014-01-03 04:00:00.0000000 3.93653078017963

TESTTAG 2014-01-03 08:00:00.0000000 3.97822095513344

TESTTAG 2014-01-03 12:00:00.0000000 3.98605019177

TESTTAG 2014-01-03 16:00:00.0000000 4.02306055174934

TESTTAG 2014-01-03 20:00:00.0000000 4.10379320273797

TESTTAG 2014-01-04 00:00:00.0000000 4.120219810009

TESTTAG 2014-01-04 04:00:00.0000000 4.24240081124836

TESTTAG 2014-01-04 08:00:00.0000000 4.84892112225294

TESTTAG 2014-01-04 12:00:00.0000000 4.16986979110373

TESTTAG 2014-01-04 16:00:00.0000000 4.00585796275073

TESTTAG 2014-01-04 20:00:00.0000000 3.96436219334602

TESTTAG 2014-01-05 00:00:00.0000000 3.99418218016624

TESTTAG 2014-01-05 04:00:00.0000000 4.0612885170016

TESTTAG 2014-01-05 08:00:00.0000000 4.28695076064931

TESTTAG 2014-01-05 12:00:00.0000000 4.0349843789306

TESTTAG 2014-01-05 16:00:00.0000000 4.02444671847754

 

 

and is what I expect. 

 

 

Problem, of course is 2014-12-08 20:00:00.0000100 is not equal to 2014-12-08 20:00    which is causing my problems.   I don't think its data as its taking an average, I just don't want it returning microseconds!

 

Thanks

 

Message was edited by: Mark Duffy

Outcomes