Convert dates to ISO dates

Foswiki date handling should use uniform ISO dates so that apps can filter and sort.

See also: AllowTypedData

Problem

Dates used in data forms cannot be trusted and used by apps because their format may be converted by Foswiki::Time.

That is because we distribute a date picker (JSCalendarContrib) that mixes up responsibility: is a date picker and a date converter at the same time.

• You can create any date format token string like %Y.%m.%d. So the entered date will be stored as 2011.11.28
• Without JSCalendarContrib no app will be able to read that date without calling JSCalendarContrib functions

To make dates really work, all dates should be stored as ISO. And then displayed as you like, according to locale settings.

Possible solution

That would mean:

• Have a date picker that does less: only show a interface element that lets you enter/pick date and time (Javascript) (easy)
• Have a date formatter (Perl) that is configurable (config, site, web, topic, user) (doable)
• Have a script to read existing dates and convert those to ISO. The hard part: to provide a user interface where the admin can control which date fields (which webs, topics) to convert.

Summary of discussion

• For backward compatibility, add a new date type, isodate.
• isodate stores ISO dates including timezone. Using seconds (epoch or time) is not practical if we want to keep the original timezone data.There are no time zone designators in ISO 8601, but we can add "Z" to the time, like 1997-07-16T19:20:30.45+01:00 (http://www.w3.org/TR/NOTE-datetime).
• isodate values should always be stored in the same format, and may not be changed to a different format by a plugin
• isodate should not need to parse strings other than the strict format in the example above. Parsing from input data to iso should be done by either plugins or javascript frontend code.
• ISO dates can be rendered using the GMTIME macro.
  • Check if GMTIME does renders both time zone designators Z and +hh:mm / -hh:mm.
• The frontend date chooser should be pluggable using a setting. JSCalendarContrib cannot be used as is because it changes the date format.

-- ArthurClemens - 29 Nov 2011, 22 Feb 2012

Discussion

At least two things to consider.

• Backwards compatibility: Since the current JSCalendarContrib sets both the display and storage format, there are probably existing wiki apps that will break if we do anything to the current implementation.
• Future flexibility: I think you've pretty clearly defined that we really need to separate this into 2 or possibly 3 components:
  • Storage - as an ISO date that can be processed, searched, etc.
  • Display - as whatever is appropriate that the user wants.
  • Entry - If possible, as "natural" as practical. A "picker" is not always the most effective.

So I wonder if this would make sense:

• Stabilize JSCalendarContrib - keep it as-is for existing Wiki apps. It continues to work with the Formfield type "date"
• Define a new Formfield type - ISODate?
• Display ISO dates using the system wide Locale date format.
  • Do we need a per form or topic override? - I think yes.
  • Do we need to expand the supported system date display formats? There have been comments that we are not flexible enough,
• Edit dates flexibly with both an entry field and picker. (Consider datejs - http://www.datejs.com/ )
  • Can the Picker be plugable - JQuery vs. current JSCalendarContrib's calendar.js
    • It could, but keep in mind that most tools do not let you enter seconds, and if they do, in a clumsy way. That is not to say it doesn't exist. -- AC

And as you mention - tools to help migrate from Date to ISODate are needed.

The MongoDB and Sql DB users need to comment as well - How does this fit with database assisted search?

-- GeorgeClark - 29 Nov 2011

Normalising dates is absolutely a missing part for mongo & dbistore. ATM I've carefully ensured we are using isodates throughout our wiki-apps to mitigate the problem.

I think George's suggestion of a new formfield type, tentatively named isodate, is absolutely the right way forward to avoid clobbering existing wikiapps assuming the old bugs/features.

See also: AllowTypedData

-- PaulHarvey - 30 Nov 2011

Storing dates in epoch seconds would be even better for sorting and still remain locale and timezone independent.

-- MichaelDaum - 30 Nov 2011

In our case we had GMT/timezone offsets we didn't want to lose.

-- PaulHarvey - 30 Nov 2011

Hm, storing a point in time should always be definite ... which epoch seconds are. Displaying timezone information is only a matter of the view on that data, isnt it?

Let me guess internal formats of dates in real databases are epoch-ish as well, not iso date strings.

So best would be to separate things like we do for the +values formfields, where stored bits differ from their outside display (which reminds me that I have an open feature request to act on adding checkbox+values and radio+values).

-- MichaelDaum - 30 Nov 2011

The isodate appeals, though perhaps time would be an alternative. Note that storing epoch secs would be more compact, but would require the form definition to be read when displaying form data, in order to recognise the field as a time and format it as something readable - something we have sought to avoid (though frankly, I think it causes more problems than it's worth).

-- CrawfordCurrie - 30 Nov 2011

I see now Paul's point: given there's one DataForm definition with two date-time formfields. Both have explicit TZ information, and they might differ in both date-time formfields by purpose. So storing $epoch seconds is only sensible when it is sufficient to display that information by using the implicit locale settings of the user viewing the record. It would be wrong to strip off explicit TZ information by converting it to epoch seconds and then display it again using some other TZ as that's not what the data said before.

-- MichaelDaum - 30 Nov 2011

I have added a summary above, and created the proposal CreateISODateFormat.

-- ArthurClemens - 22 Feb 2012