1 Reply Latest reply on Aug 1, 2013 11:38 AM by Mark Holtz

    Joining Time Data

    Patrick Calnan

      I have two datasets that I want to join on timestamps and perform calculations using this join.

       

      The problem that I have is that one set of data is on a minute by minute basis while the other is on a half hourly basis. I have solved this problem in SQL previously by creating a table with the half hour data broken into into minute-by-minute

       

      Half HourData
      01/02/2012 00:0039
      01/02/2012 00:3017
      01/02/2012 01:0037
      01/02/2012 01:3040
      01/02/2012 02:0015
      01/02/2012 02:304

       

      Half Hour-min-byminData
      01/02/2012 00:0039
      01/02/2012 00:0139
      01/02/2012 00:0239
      01/02/2012 00:0339
      01/02/2012 00:0439
      01/02/2012 00:0539

       

      The second dataset that I want to join to is minute by minute from the outset

       

      Half Hour-min-byminData 2
      01/02/2012 00:00319
      01/02/2012 00:01219
      01/02/2012 00:0281
      01/02/2012 00:03605
      01/02/2012 00:04914
      01/02/2012 00:05945
        • 1. Re: Joining Time Data
          Mark Holtz

          Hi Patrick,

           

          Not sure I fully understand your question. A packaged (.twbx) workbook would be helpful. So Your Question Didn't Get Answered...

           

          That said, I think you could convert your minute time stamps to their corresponding half hours sort of like a DATETRUNC function would do...

           

          "Time-to-last-half-hour":

          DATETIME(

          STR(MONTH([DateTimeField]))+'/' //month

          +STR(DAY([DateTimeField]))+'/' //day

          +STR(YEAR([DateTimeField]))+' ' //year

          +STR(DATEPART('hour',[DateTimeField]))+':' //hour

          +IF DATEPART('minute',[DateTimeField]) > 30 THEN '30' ELSE '00' END //minute

          + ':00' //second

          )

           

          Then, perform your join on this calculated field?