2 Replies Latest reply on Aug 8, 2018 6:59 PM by tramachandran

    API TimeStamp

    cageybee

      Hi, I'm using an SSIS package to connect to PI System via the web API and to get the data for one of the plants. The plant is in LA and we are in St Louis. I am getting the data and it has a timestamp as DateTimeOffset and it gives the time back in terms of our time. How do I get the local system time instead of our time from the call? Here is my code. This is all happening inside of a data flow task  in a Script Component

       

      #region Namespaces
      using System;
      using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
      using Microsoft.SqlServer.Dts.Runtime.Wrapper;
      using System.Collections.Generic;
      using System.Text;
      using System.Runtime.Serialization;
      using System.Runtime.Serialization.Json;
      using System.Net;
      using System.IO;
      #endregion
      
      
      [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
      public class ScriptMain : UserComponent
      {
      
      
          [DataContract]
          public class AFValue
          {
              [DataMember]
              public string AttributePath { get; set; }
              [DataMember(Name = "TimeStamp")]
              private string TimeStampRaw { get; set; }
              [DataMember]
              public float? Value { get; set; }
      
      
              public DateTimeOffset? Timestamp
              {
                  get
                  {
                      DateTimeOffset result;
                      if (!DateTimeOffset.TryParse(TimeStampRaw.Replace('T', ' '), out result))
                          return null;
      
      
                      return result;
                  }
              }
          }
      
      
          [DataContract]
          public class AFValueSearchCriteria
          {
              [DataMember]
              public string AttributePath { get; set; }
              [DataMember]
              public string StartDate { get; set; }
              [DataMember]
              public string EndDate { get; set; }
          }
      
      
          public static List<AFValue> results = new List<AFValue>();
      
      
          public override void PreExecute()
          {
              base.PreExecute();
              string equipmentStateIDPath = Variables.VEquipmentStateIDPath.ToString();
              string defaultStartDate = Variables.VWebServiceStartDate.ToString();
              string defaultEndDate = Variables.VWebServiceEndDate.ToString();
              string webAPIURL = Variables.PPWebAPIURL.ToString();
      
      
              AFValueSearchCriteria criteria = new AFValueSearchCriteria();
              criteria.AttributePath = equipmentStateIDPath;
              criteria.StartDate = defaultStartDate;
              criteria.EndDate = defaultEndDate;
      
      
              WebClient client = new WebClient();
              client.Encoding = Encoding.ASCII;
              client.Headers.Add("Content-Type", "application/json");
              client.Headers.Add("Accept", "application/json");
      
      
              MemoryStream mstream1 = new MemoryStream();
              DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(AFValueSearchCriteria));
              serializer.WriteObject(mstream1, criteria);
      
      
              mstream1.Position = 0;
      
      
              StreamReader reader = new StreamReader(mstream1);
              string jsonDataRequest = "[" + reader.ReadToEnd() + "]";
      
      
              string webAPIResponse = client.UploadString(webAPIURL, "POST", jsonDataRequest);
      
      
              DataContractJsonSerializer deserializer = new DataContractJsonSerializer(typeof(List<AFValue>));
              
              byte[] buffer = Encoding.ASCII.GetBytes(webAPIResponse);
      
      
              using (Stream mstream2 = new MemoryStream())
              {
                  mstream2.Write(buffer, 0, buffer.Length);
      
      
                  mstream2.Position = 0;
                  List<AFValue> deserializedResponse = deserializer.ReadObject(mstream2) as List<AFValue>;
      
      
                  results = deserializedResponse;
              }
          }
      
      
          public override void PostExecute()
          {
              base.PostExecute();
          }
      
      
          public override void CreateNewOutputRows()
          {
              foreach (AFValue item in results)
              {
                  ResultBuffer.AddRow();
                  ResultBuffer.AttributePath = item.AttributePath;
                  ResultBuffer.TimeStamp = (DateTimeOffset)item.Timestamp;
                  ResultBuffer.Value = item.Value.GetValueOrDefault();
              }
          }
      
      
      }
      
        • Re: API TimeStamp
          Marcos Vainer Loeff

          Hello Ramin,

           

          I am not sure which method you are using against PI Web API but you might check the timeZone parameter as stated here.

           

          The description is below:

           

          The time zone in which the time string will be interpreted. This parameter will be ignored if a time zone is specified in the time string. If no time zone is specified in either places, the PI Web API server time zone will be used. See Time Zone

          for more information.

           

          Since this is a .NET library, I would use the PI Web API client library for .NET. You will able to write code faster and it does support the timeZone input parameter.

           

          Hope this helps!

          1 of 1 people found this helpful
          • Re: API TimeStamp
            tramachandran

            Adding to Marcos' comments, note that PI Web API always returns timestamps in UTC and any conversions need to happen on the client side.

            Time Zone behavior:

            • The time zone can be specified for endpoints with a time (e.g. start time and end time) parameter. The specified time zone context will be used to interpret the time string when possible (e.g. for relative PI time strings such as "y" and "t"). The following behavior is to be expected:
            • If the time string already has a time zone offset designated in ISO 8601 format (e.g. "2016-07-07T05:00:00Z" or "2016-07-07T05:00:55+02:00"), the time zone parameter is ignored
            • If the time string has no time zone offset designated (e.g. "2016-07-07T05:00:00" or "y"), the time zone specified in the time zone parameter (see accepted formats below) will be used to evaluate the given time strings.
            • If no time zone information is specified in the time string or the time zone query parameter, the local time (including daylight saving settings) on the PI Web API instance is used to interpret the time strings.

             

            Feedback on client side conversion:PI Web API - return queries in custom timezone

            2 of 2 people found this helpful