6 Replies Latest reply on Jan 2, 2014 7:16 PM by ee.ashley

    Data-Link UTC Date

    matthew.rivett

      There doesn't appear to be any way to return UTC dates in data link.  I asked tech support and they said it was so.  Instead you must convert local date to UTC date.  They provided me with some sample  code to get back the UTCSeconds using PITimeServer and I think modified it to get back a date object.

       

      To OSIsoft:  Please add this feature to data-link!

       

      I'm not that great with date math.  Can anyone spot any issues in this code or is there a better way to accomplish this?

       
      Option Explicit
      
      Public Function ConvertLocalDatetoUTCDate(ByVal vdatExcelDate As Date) As Date
          On Error GoTo Err:
          
          ' only run if a date is set
          If vdatExcelDate Then
              ' Variables
              Dim pit As New PITimeServer.PITime
      
              ' Get the Local Date
              pit.LocalDate = vdatExcelDate
          
              ' convert UTC seconds into days and add the beginning of UTC time (1/1/1970) in days
              ConvertLocalDatetoUTCDate = (pit.UTCSeconds / 60 / 60 / 24) + DateSerial(1970, 1, 1)
          End If
          Exit Function
          
      Err:
          MsgBox vdatExcelDate & vbCrLf & Err.Description, vbCritical, "Error"
          Exit Function
      End Function
      

       

       

      Thanks,
      Matt 

        • Re: Data-Link UTC Date
          Roger Palmen

          I'm not a good programmer, but the code looks ok to me. Can't thing of anything that would cause incorrect results.

           

          As always, define some testcases and run them through your code to see if it behaves at expected date ranges, extremities and oddities (e.g. leap-year days).

            • Re: Data-Link UTC Date
              matthew.rivett

              Roger,

               

              Thanks for the response.

               

              One thing I need to test is DST.  Not sure how it'll handle the extra hour scenario.

               

              PITimeServer is doing the complex math but how would it know which hour is the extra hour?  I think it'll end up handling both hours the same way.

               

              Matt

                • Re: Data-Link UTC Date
                  matthew.rivett

                  As I suspected it doesn't work for DST.  That's the problem with doing the conversion after you've already pulled the data.  I'm not sure there is any way to solve this problem without changing Data Link to return UTC time stamps.

                    • Re: Data-Link UTC Date
                      ee.ashley

                      Depending on what you need to use the UTC time for, have you tried using a PI Expression to get the UTC time as a value from the server?

                       

                      For example, I am in Mountain Time, so I can get UTC timestamps using a DataLink PI Expression such as: ('*' - '01-JAN-1970')+7*60*60

                       

                      For me, this produced the following UTC times in November's DST change for 30m sampled values (the far left column is just a difference to show that the UTC times are always increasing):

                       

                      4846.UTCTimeDataLink.png

                       

                       

                       

                      To get timestamps for compressed values, you can probably use a Timed Data expression using the timestamps of your Compressed Data retrieval cells.

                        • Re: Data-Link UTC Date
                          matthew.rivett

                          Ashley,

                           

                          That's an interesting idea but I think Data-Link will treat the timestamps as local date when requesting data from PI.  You can't tell data link to use UTC as input.

                           

                          Thanks,

                           

                          Matt

                            • Re: Data-Link UTC Date
                              ee.ashley

                              To use UTC as an input, given that the Timestamp inputs for DataLink allows us to use PI time expressions such as *-1d, etc, I tried something like this and it seems to work:

                               

                              =PIArcVal("sinusoid","01-jan-1970 + 1383465600s",1,"\\lt0277-pi","interpolated")

                               

                              You could then perhaps use some Excel formulas to build the time expression string for you? This would allow you to specify a UTC Seconds timestamp as the reference timestamps for a DataLink expression.