2 Replies Latest reply on Aug 3, 2018 11:36 AM by Michael L

    Hourly measurements with gaps in timestamp: assign the missing records to 0 values

    Michael L

      I have a database table with columns (timestamp, value). The timestamp is always rounded to an hour. Sometimes there are gaps between timestamps:

      timestamp              value 
      --------------------   ---------
      Aug,13 2014 21:00:00     52
      Aug,13 2014 22:00:00     51
      Aug,13 2014 23:00:00     47
      Aug,14 2014 01:00:00     28
      Aug,14 2014 02:00:00     31
      Aug,14 2014 08:00:00     32

      I would like to create a line plot where for the missing hours the displayed values will be 0.

      My attempts:

      1) Treat the timestamp (X-axis) as a discrete.

               Issue: the gaps are ignored and the X-axis become non-uniform.

              I think if I knew how to assign the value for missing timestamps to 0 then it will solve the problem because the X-axis will be uniform.

      2) Treat the timestamp (X-axis) as a continuous to make X-axis uniform.

           Issue: the gaps in timestamps are ignored and I do not know how to fill it with 0 values.