AnsweredAssumed Answered

Query in different time zone no DST adjusted via Linked Server (SQL Server) when server and client adjust to DST

Question asked by CesarA on Apr 24, 2017
Latest reply on Apr 25, 2017 by Eugene Lee

I am using a Linked Server (SQL Server) and I need to query in a specific time zone (EST) without adjusting to DST (no EDT). The server is in Central time (CST) adjusting to DST (CDT). Local clients are also in Central Time adjusting to DST.

 

I am running the following query using PI SQL Commander:

select '2016-07-21 13:00' currentTime, a.tag, a.timestep, a.time,  a.__utctime ,a.calcbasis, a.value, a.pctgood

from [piarchive]..[piavg] a

where a.time > '2016-07-21 13:00' and a.time <= '2016-07-21 14:00'

and a.tag = ‘MY:PITAG01.TST’

union all

select '2016-01-21 13:00' currentTime, a.tag, a.timestep, a.time,  a.__utctime ,a.calcbasis, a.value, a.pctgood

from [piarchive]..[piavg] a

where a.time > '2016-01-21 13:00' and a.time <= '2016-01-21 14:00'

and a.tag = ‘MY:PITAG01.TST’

 

I get the following results:

From PI SQL Commander

currentTime

tag

timestep

time

__utctime

calcbasis

value

pctgood

7/21/2016 13:00

MY:PITAG01.TST

1:00:00

7/21/16 14:00

7/21/16 19:00

TimeWeighted

  1. 99.50810994

100

1/21/2016 13:00

MY:PITAG01.TST

1:00:00

1/21/16 14:00

1/21/16 20:00

TimeWeighted

  1. 67.45977194

100

 

Then I run the same equivalent query using the linked server (SQL Server 12.0.2000):

select * from OPENQUERY ([MYPISERVER],'

select ''2016-07-21 13:00'' currentTime, a.tag, a.timestep, a.time,  a.__utctime ,a.calcbasis, a.value, a.pctgood

from [piarchive]..[piavg] a

where a.time > ''2016-07-21 13:00'' and a.time <= ''2016-07-21 14:00''

and a.tag = ''MY:PITAG01.TST''

union all

select ''2016-01-21 13:00'' currentTime, a.tag, a.timestep, a.time,  a.__utctime ,a.calcbasis, a.value, a.pctgood

from [piarchive]..[piavg] a

where a.time > ''2016-01-21 13:00'' and a.time <= ''2016-01-21 14:00''

and a.tag = ''MY:PITAG01.TST'';

');

And I made some changes to the connection string as shown below and get those results:

Linked Server with Time Zone=UTC

currentTime

tag

timestep

time

__utctime

calcbasis

value

pctgood

7/21/2016 13:00

MY:PITAG01.TST

1:00:00

7/21/16 14:00

7/21/16 14:00

TimeWeighted

  1. 95.97016552

100

1/21/2016 13:00

MY:PITAG01.TST

1:00:00

1/21/16 14:00

1/21/16 14:00

TimeWeighted

  1. 71.63009731

100

 

Linked Server with Time

Zone=Server

currentTime

tag

timestep

time

__utctime

calcbasis

value

pctgood

7/21/2016 13:00

MY:PITAG01.TST

1:00:00

7/21/16 14:00

7/21/16 19:00

TimeWeighted

  1. 99.50810994

100

1/21/2016 13:00

MY:PITAG01.TST

1:00:00

1/21/16 14:00

1/21/16 20:00

TimeWeighted

  1. 67.45977194

100

 

Linked Server with Time Zone=Local

currentTime

tag

timestep

time

__utctime

calcbasis

value

pctgood

7/21/2016 13:00

MY:PITAG01.TST

1:00:00

7/21/16 14:00

7/21/16 19:00

TimeWeighted

  1. 99.50810994

100

1/21/2016 13:00

MY:PITAG01.TST

1:00:00

1/21/16 14:00

1/21/16 20:00

TimeWeighted

  1. 67.45977194

100

 

Why the time is not adjusted to UTC in the Linked Server?

 

What is the easiest way to query (time) using EST considering the server and the client adjust to DST?

Outcomes