Feature Proposal: Add date() as a query function

Motivation

d2n() in query language is geeky and store specific, date() as a hint would be better

Description and Documentation

Add date() as a 'hint' operator to the query language

Examples

Impact

WhatDoesItAffect:

%WHATDOESITAFFECT%

edit

Implementation

-- Contributors: JulianLevens - 23 Mar 2012

Discussion

As I'm contemplating SQL to access a back-end store. It struck me that d2n is wrong on two counts:

1. It's more geeky than plain date
2. Not all stores (certainly not SQL) store dates as epoch seconds

It seems to me that a date() function would be less geeky and should merely mark the result of the function as 'consider me to be a date'. When the back-end store needs to actually use this in the back-end query, only then would it convert it to the most appropriate form.

---

I agree that these hints would be nice; would be invaluable for sorting, too (for some context see SearchOrderByTopicElement).

But I want to know what query hints for other data types would look like. I appreciate that you're trying to keep the scope of your enhancement small, but I don't want us to end up with too many ways of providing hints for all the possible data types, later down the road.

Can you elaborate on how this relates to AllowTypedData? Do you see Foswiki 1.2/2.0 supporting both approaches to supporting indexed stores? Or that AllowTypedData is too hard?

I guess we kind of already have type-hints (ignore the geek factor for a moment):

• IN - actually a compound/nested type, IN(FooField) only hints that FooField is a list, without saying what type(s) might be in there... can 'o worms I suppose
• d2n
• int

I suppose I just want to hear somebody say, "we prefer this notation over something more contained like type:date(FooField) or type(date, FooField) or..."

-- PaulHarvey - 28 Mar 2012

tbh, adding d2n or date to queries should be needed only if the field isn't a date field - if it is, foswiki should do the right thing already.

but its true that casting a text , selection or whatever field to a date would still be useful - imagine a checkbox with 4 date choices..

I prefer a more generalized syntax that allows non-core typecasting.

-- SvenDowideit - 24 Apr 2012

It's not possible to always do the right thing.

Example formfield data
12.04.2014
13.05.2016
25.05.2010
31.12.2011

Now lets select data > 20.12.2012, first up as dates:

12.04.2014

13.05.2016

Now as a string

25.05.2010

31.12.2011

I.e. we get the total opposite result.

How do we know that these are dates and not reference numbers?

The problem is a lot more severe for numbers, for which we do not have a Number type anyway. I do not think it's good enough to probably be right, even most of the time.

To generalise: if we have formfield.value < query_token then

If we know that the query_token is a date or number (because the user told us so), then we can do a date/number compare on form.field.value, and only fall back to a string compare when form.field.value does not look like a date.

Conversely, if we know that form.field.value is defined to be a date/number we can assume that query token is a similar form (or even insist on it).

However, existing FW data is simply not like that. Even if you have a FormField defined as a date it's no guarantee that all those fields are dates, because of:

1. External editing of the topics
2. History, i.e. date type was added somewhat later into an application and not all current fields are valid dates
3. Revisions

From the opposite perspective:

1. WorkFlow: I have apps which flow and change Form at different points. In some states certain fields should not be edited, so they are defined as label - so you've lost any hint that they are a date. (This is a flaw in current DataForm structure: there should be a type column and a presentation column. In fact even more enhancements are desirable and I will write a feature request — eventually).

In AllowTypedData I keep using the term Strict or Strictly (more intuitive and less geeky than Strong). I see that as a modifier on types, which states that this field will always contain a date/number/complex-number/etc. However, that becomes an optional feature of a store, how can you really enforce that on a text based store? Of course you can change a database externally, so it may just be a case of stating to FW users that when you use the strict modifier you must honour that if you make external updates.

Isn't query syntax meant to be declarative. Therefore, wouldn't date(xxx) simply appear as at least two tokens (xxx could consist of many tokens) in the node parse tree, which the query engine can interpret intelligently on behalf of the store?

OT: How can a developer of a FormFieldPlugin write such a beast in a store agnostic way?

-- JulianLevens - 24 Apr 2012

This is an excellent idea. It can either be a NOP for query engines that are unable to support date comparison (if such a beast exists) or act as a guide for those that do. Note that any data conversion errors in the engine (in the event that the data does not fit the hint) must be cleanly handled.

-- CrawfordCurrie - 25 Apr 2012