Filter on date

[Johan Enell]

I can't filter on date. In the reporter view I write 2006-12-11 which is exactly the value of a reporter in the list. When I press enter it says 'No reporters were found'. If i type 20061211 it says 'No reporters were found' and change the value of the field to 1970-01-01.

[Nicklas Nordborg]

It turns out that the column type for the last updated value is a timestamp column which also stores hours, minutes and seconds. The display of reporter information doesn't reflect this and only display the date part. It is possible to also display the time part by changing the 'Date format' in File -> Preferences to also display time values. This display mismatch can easily be fixed by changing the formatter for the last updated column to the 'Date-time formatter' instead. All other places where reporter information is being displayed also need to be changed.

However, this will not help with filtering since for a timestamp to match both the date and time part must match exactly. It is possible but it doesn't help to enter time values in the filter since those are discarded before the value sent to the database. This happens because there is no support for timestamp values in the Query API in the core. It only support date values, which has no time part. Note, the we never reset the time part, it is simply ignored by the JDBC driver or database since we don't tell it to use the time.

I think it requires a lot of work to fix this. First, the Type class must get a TIMESTAMP option. This will also affect the annotation system and plugin parameter system which must be extended with support for timestamps (or we need to find a way for those parts to ignore the TIMESTAMP option). Then, the web client must also be aware of the new type. This possible affects the table taglib and other parts of the code where strings are parsed and dates are converted.

Another possibility which would be useful (and maybe enough) even if the above fix isn't implemented is to have the query system automatically extend dates to cover the whole day. For example instead of just looking for an exact the the actual query should be converted to the equivalent of:

WHERE ... lastUpdated >= '2006-12-11' AND lastUpdated < '2006-12-12'

This will match all reporter updated on 2006-12-11 no matter what the time part of the lastUpdated column is.

The easiest workaround in the current situation is to use a 'greater than' filter. For example:

>2006-12-11

With the proper sort order, the reporters that were updated on 2006-12-11 will be displayed first.

The seconds example (20061211) is behaving as expected. It is interpreted as the number of milliseconds since 1970-01-01 (which still is 1970-01-01). All dates (in filters) are stored in the millisecond form internally and converted to strings when needed. This way it is possible to display a filter with the correct date format for the logged in user. Whenever the code comes across a date not in the specified format, it will also check if is a valid millisecond value (ie. any numeric value).

[Jari Häkkinen]

Milestone BASE 2.4 deleted

[Nicklas Nordborg]

(In [3453]) Fixes #561