3 Replies Latest reply on Jul 15, 2014 7:26 AM by Matt Bechard

    Time stamp to date conversion

    nitika joshi

      Hi

       

      I have been using Tableau and I am stuck at point on How do I map “TIME STAMP” parameter of the of splunk  that  to DATE/TIME format so that  various other graphs which requires at least one Time/Date Parameter are enabled.

       

      In the below image, the part highlighted with black is the data points. I want the graphs which are highlighted in green to be enabled and to enable that we require “date” parameter. So how to map this time stamp to date format is the issue.

      tableau_doubt.png

        • 1. Re: Time stamp to date conversion
          Michael McAlpin

          Hey Nikita,

          Calculated fields are most likely going to be your best friends here.  I would separate the start and end times in that field into two separate fields by doing this:

           

          Start Time:

          dateparse("hhmm",left([time],4))

           

          End Time:

          dateparse("hhmm",right([time],4))

           

          Then you should be able to just graph according to the start or end times, whichever is more applicable. For more "Dateparse" info visit this: Understanding the DATEPARSE Function | Tableau Software

          For more string calculations (ie. "left()" and "right") info visit this: String Functions

           

          Mike

          • 2. Re: Time stamp to date conversion
            Michael McAlpin

            Actually you will run into an issue with the "00" time at the start.  To fix this change the calculated field statements to:

             

            Start Time:

            if left([time],2)='00' then dateparse("hhmm",('12'+right(left([time],4),2)))

            else dateparse("hhmm",left([time],4))

            end

             

            End Time:

            if left(right([time],4),2)='00' then dateparse("hhmm",('12'+right(right([time],4),2)))

            else dateparse("hhmm",left([time],4))

            end

             

            I know its complicated and I tend to overcomplicate things but that is the way I know to deal with "00" values without getting errors.

             

            Mike

            • 3. Re: Time stamp to date conversion
              Matt Bechard

              Try This,

               

              You need to make sure that you follow the date Timstamp format for ODBC. Refer to this link from Micorsoft on ODBC Datetime Format.

               

              In my case, I already had a date field that was being tabled in Splunk.  In Splunk, I  cloned existing Time field and changed the format to:
              |eval TS=strftime(_time, "%Y-%m-%d %H:%M:%S")  TS stands for TimeSpamp.

               

              That did the trick.  The new TS field comes into Tableau from Splunk as a date field, not as a string.  The order of the YMD matters, as I tried a few other formats.

               

              With this coming into Tableau as a Date Field now, I can do incremental refresh's.

               

              Hope this helps, and will save others countless hours of troubleshooting this issue.

               

              Thanks,

              Matt Bechard