Storing Date in Couchbase

How should one store date in Couchbase? So far, I have come across two options. I will discuss their relative merits here.

Save Full Date in Local Time

In this option, the full date (year, month, hour etc.) is stored. For example:

{
    "createdOn": "Mar 13, 2014 9:00:20 PM"
}

The corresponding Java class field will be:

private java.util.Date createdOn;

Gson will convert the date and keep it in local time.

Indexing a date becomes a problem since Couchbase can only index string and integer keys. If you directly index the createdOn field, it will be simply indexed as a string. That will be no good if you wish to sort things by date or search for documents within a date range. The solution is to convert the date into an array of integers and create a composite key. For example:

function (doc, meta) {
  if (doc.type == "Story") {
    emit(dateToArray(doc.createdOn), null);
  }
}

The dateToArray() function will return an array with elements representing year, month, day and so on. It expects the input date to be in local time but converts the date to GMT before outputting the array. So, in the example above, the key emitted will be [2014,3,14,1,0,20].

To do a query, you will need to use composite keys as usual. For example, to get all documents created between March 10, 2014 and March 15, 2014, the start key will be [2014, 3, 10] and end key will be [2014, 3, 15]. The timezone for the keys is always GMT.

I personally find working with full date cumbersome. Firstly, there is no standard on how date should be stored in JSON. Fortunately, the format used by Gson seems to work with Couchbase. Secondly, there is no indication of timezone in the JSON field. It would have been OK if the timezone was GMT. But, it is not. Gson keeps to the local time zone. Finally, working with composite keys is just plain hassle. Also do not forget that the composite key created by dateToArray is in GMT. That is just one more thing to keep in mind.

This is where saving the timestamp comes handy.

Save Timestamp

Timestamp is basically milliseconds since epoch. For Java and UNIX, epoch starts from January 01, 1970. Here, we simply save a long integer value.

{
    "createdOn": 192338393
}

The corresponding Java field will be:

private long createdOn;

In Java, you can get timestamp by calling System.currentTimeMillis().

Indexing the timestamp becomes super easy.

function (doc, meta) {
  if (doc.type == "Story") {
    emit(doc.createdOn, null);
  }
}

Querying becomes simpler also since you need to use simple integer as keys. I am pretty sure searching this simple index will be faster than the previous approach.