Item14704: store date formfields as epoch seconds

Values of date (or date2) formfield are best stored as epoch seconds into the dbcache because searching and sorting may skip the online date conversion then as values are numeric already. The original date string can still be preserved in a fieldName_origvalue field.

Note that this change might come with some incompatibilities:

Description	Before	After
testing for an empty date field	DateField='' or d2n(DateField)=0	DateField=0
comparing date formfields against string	d2n(DateField) > d2n('24 Jul 2017') or DateField EARLIER_THAN '24 Jul 2017'	DateField > d2n('24 Jul 2017')
comparing two date formfields	d2n(StartDate) <= d2n(EndDate) or StartDate EARLIER_THAN_OR_ON EndDate	StartDate < EndDate
formatting date formfieds	$formfield(DateField)	$formatTime(DateField) or $expand(DateField_origvalue)
sorting by date	sort="d2n(DateField)"	sort="DateField"

Note that this change improves search performance for the cost of a slightly slower indexing performance, because date strings don't have to be converted to a sortable format (epoch seconds) over and over again as they are parsed once during indexing time.

This especially helps sorting of date columns in %DATATABLES of JQDataTablesPlugin not only for performance but also for correctness.

-- MichaelDaum - 02 Jun 2018

Commenting is disabled while not logged in