Date Format and its value in Data Search

Date data type can be very important field in Data Analytical systems especially in Reporting part. In addition if your application has online data search application as in Makalu then it becomes more important that you specify right formatting.

Date type data comes normally in String format in a YYYY-MM-DD format which is what sql normally follows. But, storing data in string format is very problematic especially when different format come into account and each one must be supported for exact data search or reporting. So, We chose long type instead which is equivalent to milliseconds after epoch time(00:00:00 GMT on January 1, 1970). It allows date comparision which is key in searching activities especially when date fields are indexed.

There is however one catch in time conversion. It arises out of default TimeZone of processing system. The time value for same date value can be different based on timezone (remember epoch is defined wrt GMT). So, date value '2018-10-12 11:12:11' is equivalent to 1539322031000 in NPT and 1539342731000 in UTC. This works fine as far as client and server are in same TimeZone. But, If the requesting client and serving server are on different TimeZone, then there maybe great variance that may even extend to one whole day! In Data search, if client requests getting all records having service date less than '2018-10-12 11:12:11' from Nepal to server based on US, it requests data wrt Nepal but since data are stored wrt UTC, it may end up getting completely records that are upto '2018-10-12 21:12:11'. A very common scenario would be easily arised due to Cloud as system are completely transparent and may exist in different US states that may lie in different time zones. So, this issue becomes very important to deal.

There is however an easy way to deal this. The DateFormat class to parse data into Date object has simple method for parsing onto common TimeZone. Using this, We converted all Date wrt GMT format while processing data. Also, while requesting client would be converting data into GMT format so they are always compatible with each other.

Here are the two ways one can do
1.    DateFormat sqlDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    sqlDateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));
    Date d = sqlDateFormat.parse(getDate(a));
    return d.getTime();
2.    Calendar c = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
    DateFormat sqlDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    Date d = sqlDateFormat.parse(getDate(a));
    c.setTimeZone(TimeZone.getTimeZone("GMT"));
    c.setTime(d);
    return c.getTimeInMillis();

References:
http://stackoverflow.com/questions/2627992/force-java-timezone-as-gmt-utc
http://docs.oracle.com/javase/1.4.2/docs/api/java/util/Date.html
http://docs.oracle.com/javase/1.4.2/docs/api/java/util/Calendar.html
http://docs.oracle.com/javase/1.4.2/docs/api/java/text/DateFormat.html#setTimeZone(java.util.TimeZone)
http://docs.oracle.com/javase/1.4.2/docs/api/java/util/TimeZone.html