title: How should I store a date interval in Cassandra?
body: <p>I'm working on an application that stores sensor measurements. Sometimes, the sensors will send erroneous measurements (e.g. the measured value is out of bound). We do not want to persist each measurement error separately, but we want to persist statistics about these errors, such as the sensor id, the date of the first error, the date of the last error, and other infos like the number of successive errors, which I'll omit here...</p>
  
  <p>Here is a simplified version of the "ErrorStatistic" class:</p>
  
  <pre><code>package foo.bar.repository;
  
  import org.joda.time.DateTime;
  
  import javax.annotation.Nonnull;
  import javax.annotation.Nullable;
  
  import static com.google.common.base.Preconditions.checkNotNull;
  
  public class ErrorStatistic {
  
      @Nonnull
      private final String sensorId;
      @Nonnull
      private final DateTime startDate;
      @Nullable
      private DateTime endDate;
  
      public ErrorStatistic(@Nonnull String sensorId, @Nonnull DateTime startDate) {
          this.sensorId = checkNotNull(sensorId);
          this.startDate = checkNotNull(startDate);
          this.endDate = null;
      }
  
      @Nonnull
      public String getSensorId() {
          return sensorId;
      }
  
      @Nonnull
      public DateTime getStartDate() {
          return startDate;
      }
  
      @Nullable
      public DateTime getEndDate() {
          return endDate;
      }
  
      public void setEndDate(@Nonnull DateTime endDate) {
          this.endDate = checkNotNull(endDate);
      }
  
  }
  </code></pre>
  
  <p>I am currently persisting these ErrorStatistic using Hector as follows:</p>
  
  <pre><code>private void persistErrorStatistic(ErrorStatistic errorStatistic) {
      Mutator&lt;String&gt; mutator = HFactory.createMutator(keyspace, StringSerializer.get());
  
      String rowKey = errorStatistic.getSensorId();
      String columnName = errorStatistic.getStartDate().toString(YYYY_MM_DD_FORMATTER);
      byte[] value = serialize(errorStatistic);
  
      HColumn&lt;String, byte[]&gt; column = HFactory.createColumn(columnName, value, StringSerializer.get(), BytesArraySerializer.get());
      mutator.addInsertion(rowKey, COLUMN_FAMILY, column);
  
      mutator.execute();
  }
  
  private static final DateTimeFormatter YYYY_MM_DD_FORMATTER = DateTimeFormat.forPattern("yyyy-MM-dd");
  </code></pre>
  
  <p>When we receive the first measurement in error, we create an ErrorStatistic with <code>sensorId</code> and <code>startDate</code> set, and a null <code>endDate</code>. This ErrorStatistic is kept in our in-memory model, and persisted in Cassandra.
  We then update the ErrorStatistic in memory for the next measurements in error, until we receive a valid measurement, at which point the ErrorStatistic is persisted and removed from our in-memory model.</p>
  
  <p>Cassandra thus contains ErrorStatistics with open-ended intervals (e.g. [2012-08-01T00:00Z|null]), and closed intervals (e.g. [2012-08-01T00:00Z|2013-01-12T10:23Z]).</p>
  
  <p>I want to be able to query these ErrorStatistics by date.</p>
  
  <p>For example, if I have these 3 error statistics:</p>
  
  <pre><code>sensorId  = foo
  startDate = 2012-08-01T00:00Z
  endDate   = 2012-09-03T02:10Z
  
  sensorId  = foo
  startDate = 2012-10-04T03:12Z
  endDate   = 2013-02-01T12:28Z
  
  sensorId  = foo
  startDate = 2013-03-05T23:22Z
  endDate   = null
  (this means we have not received a valid measurement since 2013-03-05)
  </code></pre>
  
  <p>If I query Cassandra with the date:</p>
  
  <ul>
  <li>2012-08-04T10:00Z --> it should return the first ErrorStatistic</li>
  <li>2012-09-04T00:00Z --> it should return that there were no errors at this time</li>
  <li>2014-01-03T00:00Z --> it should return the last ErrorStatistic (since it is open-ended)</li>
  </ul>
  
  <p>I am not sure how I should store and "index" these ErrorStatistic objects, to efficiently query them. I am quite new to Cassandra, and I might be missing something obvious.</p>
  
  <hr>
  
  <p>Edit: the following was added in response to Joost's suggestion that I should focus on the type of queries I am interested in.</p>
  
  <p>I will have two types of query:</p>
  
  <ul>
  <li>The first, as you guessed, is to list all ErrorStatistics for a given sensor and time range. This seems relatively easy. The only problem I will have, is when an ErrorStatistics starts <em>before</em> the time range I'm interested in (e.g. I query all errors for the months of april, and I want my query to return ErrorStatistics[2012-03-29:2012-04-02] too...)</li>
  <li>The second query seems harder. I want to find, for a given sensor and date, the ErrorStatistics whose interval contains the given date, or whose <code>startDate</code> precedes the given date, with a null <code>endDate</code> (this means that we are still receiving errors for this sensor). I don't know how to do this efficiently. I could just load up all ErrorStatistics for the given sensor, then check the intervals in Java... But I'd like to avoid this if possible. I guess I want Cassandra to start at a given date and look backward until it finds the first ErrorStatistics with a <code>startDate</code> that precedes the given date (if any), then load it and check in Java if its <code>endDate</code> is <code>null</code> or after the given date. But I have no idea if that's possible, and how efficient that would be.</li>
  </ul>