3 Replies Latest reply on Feb 7, 2019 2:07 AM by Ciara Brennan

    Creating a Timestamp in Tableau from Teradata

    Steven Davic

      I don't know if anybody else run into this issue before, but I thought that I would share an interesting quirk i found working with dates and times in Teradata.

       

      From Teradata I was receiving two fields, LogDate [Date] and LogHour [INTEGER]

       

      but I wanted a single timestamp field, so i used the DATEADD Function in a Calculation to create the timestamp:

       

      but when I added the new Calculated Field to the workbook I got an error:

      here is the SQL that Tableau created to query Teradata based off of my Calculated field:    

      SELECT
           EXTRACT(YEAR FROM ("sessionsumhr_hst"."LogDate" + "sessionsumhr_hst"."LogHour" * INTERVAL '1' HOUR)) AS "yr:calculation_735212653322457"
      FROM 
           "PDCRINFO"."SessionSumHr_Hst" "sessionsumhr_hst"
      
      GROUP BY 1
      

       

      after some research I realized that because LogDate is a Date field, it can only handle intervals of YEAR, MONTH, or DAY but not HOUR.  Teradata can only handle intervals of HOUR if the field is in a TIMESTAMP format.  So, LogDate needed to be cast as a Timestamp (which Tableau doesn't inherently do).  what the above query should look like to work properly is something like this:

      SELECT
           EXTRACT(YEAR FROM (CAST("sessionsumhr_hst"."LogDate" as TIMESTAMP) + "sessionsumhr_hst"."LogHour" * INTERVAL '1' HOUR)) AS "yr:calculation_735212653322457"
      FROM 
           "PDCRINFO"."SessionSumHr_Hst" "sessionsumhr_hst"
      
      GROUP BY 1
      

       

      To get Tableau to create that Cast statement LogDate needs to have its Tableau data type changed from Date to Date & Time.  Once you do that, Tableau will have no issue with the Calculated field I had created.

       

       

      If anybody else ran into this issue and was wondering what happened, I hoped this helped.

       

      -Steven