1 Reply Latest reply on Oct 6, 2017 7:22 AM by Kenji Hashimoto

    How to store UTC dates from within SQL


      We have connected PI as a linked server in SQL server.


      For example, currently our queries look like this:

      SELECT * FROM OPENQUERY(PI, 'INSERT INTO piarchive..picomp2 (tag, time, value, annotations) VALUES (''SU1.1051-3.VAL'',  ''2017-09-30 00:00'', 22, ''anno'')')

      SELECT * FROM OPENQUERY (PI, 'UPDATE piarchive..picomp2 SET annotations =  ''anno'', value = 22 WHERE tag = ''SU1.1051-3.VAL'' AND time = ''2017-09-30 00:00''')


      The problem is that we have no option to insert the correct times during the wintertime shifting.


      Does anyone know how to insert UTC times from within a SQL Server stored procedure?


      Thanks for your help.

        • Re: How to store UTC dates from within SQL
          Kenji Hashimoto

          Basically PI Data Archive use localhost.tz and summer time is automatically handled. PI Data Archive keep events as UTC timestamp.

          I am not sure why your insert into query shows time difference. Do the other client tool works fine? Does only linked SQL Server get the time difference?


          Anyway, I tried to get UTC time from linked SQL Server. (I am not expert of Microsoft SQL Server though)
          Declare @tzoffset as int;
          Declare @gettime as Datetime;
          select @tzoffset = (select datediff(hour,getdate(),getutcdate()));
          Select @gettime = (Select * FROM OPENQUERY(PIDA,'SELECT time FROM [piarchive]..[pisnapshot] WHERE tag = ''cdt158'''));
          Select DATEADD(hh,@tzoffset,@gettime)

          It can show the snapshot event timestamp as UTC time.

          Maybe you can insert it to the PI with converting it to the local time again.