3 Replies Latest reply on Aug 11, 2017 5:48 AM by Fergal Dalton

    Convert epoch time to datetime - GMT or Localtime?

    Fergal Dalton

      Hi all, new user to Tableau here. I have a SQL link with timestamps in epoch time, and I need to convert to either GMT or localtime (I can use one or the other, but it needs to be consistent). My timezone is UTC winter, UTC+1 summer. So I'm using this formula:

       

      dateadd('second',[Epoch Time],#1970-01-01#)

       

      However, the result comes out inconsistent. I can substitute the "[Epoch Time]" variable directly for an integer, and using the following values I get this result. The lines in boldface show where the result is inconsistent. In short, the formula always (summer and winter) yields GMT, except for a period of one hour during the Spring activation of daylight savings, during which the formula yields local time.

       

      TestValue          ActualGMT     ActualLocal     TableauResult     TableauIsGiving

      ...

      27/March/2017

      1490493599     00:59:59          00:59:59          00:59:59               GMT & Local

      1490493600     01:00:00          02:00:00          02:00:00               Local

      ...

      1490493599     01:59:59          02:59:59          02:59:59               Local

      1490463600     02:00:00          02:00:00          02:00:00               GMT

      ...

      29/Oct/2017

      1509238799     00:59:59          01:59:59          00:59:59               GMT

      1509238800     01:00:00          01:00:00          01:00:00               GMT & Local

      ...

      1509242399     01:59:59          01:59:59          01:59:59               GMT & Local

      1509242400     02:00:00          02:00:00          02:00:00               GMT & Local

       

      Am I missing something or is this an issue in Tableau date calculations?

       

      Thanks.