Add support for datetime annotation types

[Nicklas Nordborg]

This is a rather big change and will have effect in other parts of BASE as well. Here is a list of things that we need to do.

1. Add a new table for storing datetime values and a subclass to ParameterValues to handle it.
2. Add a new option to the Type enumeration.
3. Update the gui so that it is possible to add datetime values
4. Update the query api so that it is possible to search for datetime values
5. There are probably some effects also on the plug-in api since the Type enumeration and ParameterValues class are used by plug-in parameters as well. I'll have to investigate the effect and maybe add a specific ticket for adding datetime support to the plug-in system if the work required is substantial.
6. There may be similar effects on "extendable" classes (eg. reporters, users and raw data).
7. Update the annotation importer so that it can import timestamp values. I am not really sure how it handles date values right now. It may work if the date values are in yyyy-MM-dd format, but it has no support for specifying a different format.
8. Check and update documentation
9. The table exporter seems to remove time information from timestamp columns.
10. More...?

[Nicklas Nordborg]

Hmm... it seems like the table used to store dates is actually using a 'timestamp' column so theoretically it should be possible to use the existing table to store datetime values. And some (but not all) parts of the core api already allows the time part to be stored for date values. The main exception is that all string conversions to/from dates don't handle time and most of the gui is about string handling. I am trying to figure out what is the best:

• Use the existing table for both date and datetime values.
• Create a new table for datetime values and convert the old to only store date values

The latter is "cleaner" and is more correct according to the current documentation. The ability to store the 'time' part can be considered a bug. On the other hand, converting the existing table is an incompatible change which will cause data loss if someone is storing datetimes in it. It's not that likely though, since it can't happen with the official BASE release.

[Nicklas Nordborg]

Oh no... what a mess... I have to correct myself on the previous comment. It is possible to store datetime values with the current BASE release and it is not very complicated.

By changing the BASE->Preferences->Date format setting to a template that includes time placeholders (for example yyyy-MM-dd hh:mm:ss) it is possible to enter datetime values and the time part will be parsed and stored in the database. It messes up the query api though, since it will not match anything if not the exact date+time (including seconds) is entered as a search condition. Other users that hasn't changed the 'Date format' option will not be able to find any datetime values at all (except 00:00:00) since the time part is discarded before the query is made.

I think the "mess" needs to be cleaned up and they best way to do that is to make sure that no time part is getting into date-only values. Eg. we need one table for storing date-only values and one table for storing datetime values.

[Nicklas Nordborg]

(In [5422]) References #1512: Add support for datetime annotation types

Adds a new data class/table for storing datetime parameters (=timestamp in the SQL world). The schema update changes the existing DateValues table to only store dates.

Added Type.TIMESTAMP to the Type enumeration.

This is basically 1) and 2) in the ticket description.

[Nicklas Nordborg]

(In [5423]) References #1512: Add support for datetime annotation types

Makes it possible to crete TIMESTAMP annotation types and to enter values for them.

This is basically 3) in the ticket description.

[Nicklas Nordborg]

(In [5424]) References #1512: Add support for datetime annotation types

Makes it possible to use TIMESTAMP for regular list columns. Added support for searching on the date part to the query api.

Changes all existing tables with date+time data to use TIMESTAMP instead.

This is half of 4) in the ticket description. It is still not possible to search on TIMESTAMP annotations.

[Nicklas Nordborg]

(In [5429]) References #1512: Add support for datetime annotation types

Changed the approach used to search for date/timestamps. By using cast(value as date) we can avoid all special cases when searching for a date among timestamp values. This affects the query api for all regular timestamp columns and it is now also possible to search for timestamp annotation values.

[Nicklas Nordborg]

(In [5430]) References #1512: Add support for datetime annotation types

The annotation importer now support importing date/timestamp values. Dates could be imported earlier, but only in yyyy-MM-dd format. Both date and timestamp formats are now a configuration parameter. The default values are taken from the user configuration in BASE->Preferences. There is also a new error handling option that handles invalid dates/timestamps (skip/fail).

This should complete item 7 in the ticket description. 5, 6 and 8 remaining...

[Nicklas Nordborg]

Regarding item 5 in the ticket description: The plug-in api doesn't use the Type enumeration for parameter definitions. Instead it uses the ParameterType class and it's subclasses. One subclass is DateParameterType that handles date parameters. Due to the same "bug" as discovered above it was possible to enter date+time values as parameters. But the changes in [5422] fixes this so that it is only possible to store the date part. No other changes should affect the plug-in api and unless we need timestamp support we don't have to do anything more as part of this ticket.

[Nicklas Nordborg]

It seems like the table exporter has some issues with timestamps. Added item 9 to the ticket description.