Feature Proposal: Create an ISO date format

See the brainstorming topic ConvertDatesToISO.

This (narrower) proposal deals with the implementation of an ISO date format. Rendering and date picking are left for another proposal.

Description and Documentation

  • Next to date, add a new date type: datetime.
  • datetime values can and should be rendered using the GMTIME macro in local user time (if necessary, GMTIME must be adapted to handle all time zone notations) - but that is outside of this proposal.
  • This proposal is about the storage of date-time:
    • datetime uses a strict input format similar to W3C Date and Time Formats, but without the string delimiters. For example: 2012-05-31 19:59:59.
      • Short background on this format:
        • For the omission of the delimiters I am following The Best of Dates, The Worst Of Dates: While the original scope for ISO 8601 was for computer to computer "Information Interchange", ISO 8601 is now being used in many areas the original standards committee never considered when the standard was designed. Thus an out of specification space character is acceptable for displaying dates for people as computer to human transfers are not strictly within ISO 8601's scope. Likewise we don't use the Z character to specifiy the time zone string part.
        • At some point we have pondered to use epoch values, because these will be parsed most easily. While performance is important, we must assume that humans read and enter date fields without having javascript to convert epoch to human. The proposed format is relatively easy for humans and sorts relatively well (except for the time zone offsets). If desired we can also store the epoch value.
    • Time is in UTC and can have a time zone offset specified.
    • The current Configure setting {DefaultDateTimeFormat} can only be used for display, so does not play a role here.
    • A time zone offset number is relative to UTC, in the form of a sign character (+/-) followed by a four digit time providing hours and minutes of the offset; no colon is used.
    • A time zone name is one of the Olson Time Zone names
      • Derick Rethans argues in Storing Date/Times in Databases that for precise time calculations with Daylight Savings Time you also need to store the timezone identifier, like America/Santiago (which is in the same UTC offset as Chile, but uses a different DST).
    • See below for a list of supported date string formats. datetime values should always be stored in one of these formats, and may not be changed to a different format by a plugin.
    • Parsing and rendering of date strings is performed by DateTime::Format::Strptime, part of the DateTimelibrary. Therefore:
  • While rendering is out of scope of this proposal, the generated html should be ready to be picked up by a javascript renderer. For example by adding (in handler renderForEdit) the html attribute data-format='isodate', so that jQuery code could add a date picker. So the "human friendly" format 2012-05-31 19:59:59 +0300 is also passed as computer-friendly ISO date 2012-05-31T19:59:59+0300.
  • Parsing nano seconds is out of scope for now.

Supported date formats

Simplest 2012-05-05 15:28:37 UTC time is assumed
Time zone offset (number) 2012-05-05 15:28:37 -0500 Offset should not include Daylight Savings
Time zone name 2012-05-05 15:28:37 America/Nassau One of the Olson Time Zone names. Offset will be calculated automatically.
Time zone offset plus time zone name 2012-05-05 15:28:37 -0500 America/Nassau Probably redundant format

Data form template

Example:

| *Name* | *Type*  | *Size* |
| Name   | text    | 100    |
| Date   | datetime | 28     |

Perl code

Foswiki::Form::Datetime.pm is a subclass of Foswiki::Form::FieldDefinition.

Rendered HTML

<input type='text' name='mydate' size='80' class='foswikiInputField' data-isodate='2012-05-31T19:59:59+0300' value='2012-05-31 19:59:59 +0300' />
Example with javascript picking up =data=format=:

<div>
<input type='text' name='mydate' size='80' class='foswikiInputField' data-isodate='2012-05-31T19:59:59+0300' value='2012-05-31 19:59:59 +0300' />
</div>

%ADDTOZONE{
"script"
requires="JQUERYPLUGIN"
text="<script type='text/javascript'>
var formatDate = function(date) {
   var days = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];
   var months = ['January', 'February', 'March', 'April', 'May', 
   'June', 'July', 'August', 'September', 'October', 'November', 'December'];
   var pad = function(str) { str = String(str); return (str.length < 2) ? '0' + str : str; }

   var meridian = (parseInt(date.getHours() / 12) == 1) ? 'PM' : 'AM';
   var hours = date.getHours() > 12 ? date.getHours() - 12 : date.getHours();
   return days[date.getDay()] + ' ' + months[date.getMonth()] + ' ' + date.getDate() + ' ' 
     + date.getFullYear() + ' ' + hours + ':' + pad(date.getMinutes()) + ':' 
     + pad(date.getSeconds()) + ' ' + meridian;
};
jQuery(document).ready(function ($) {
   $('input[type=text][data-isodate]').each(function() {
      var $this = $(this),
         date = new Date($this.data('isodate'));
         $clone = $this.clone().appendTo($this.parent());
      $this.hide();
      $clone.val(formatDate(date));
   });
});
</script>"
}%

Implementation

  • Create DateTimeContrib
    • For presumed efficiency, the epoch value is written when saving the form. It needs a good check if comparing the stored epochs delivers the same results as DateTime compare functions.
    • Facilitate Javascript usage
      • This takes more work, because js dates are normally converted to local time. But perhaps that is ok.
  • Add DateTime to included CPAN libs (in the Contrib)
  • Write unit tests
  • Document in DataForms
  • Document in places where dates or time is mentioned

-- Contributors: ArthurClemens - 22 Feb 2012, updated 05 May 2012

Discussion

Hello Arthur, I am really looking forward to seeing this smile

After some brief argument on IRC, there seems to be concern about sortability of these strings

Do we add more magic to QuerySearch /SEARCH ? That could be painful..

Do we atomize the date+tz string into separate GMTIME + offset attributes? That needs a new attribute on the META:FIELD schema, Eg. 1997-07-16T19:20:30.45+01:00 might be stored as %META:FIELD{name="Date" value="1997-07-15T19:20:30.45" offset="+01:00"}% (or rather than a isodate-specific attribute offset, something generic like metavalue ?)

Later: Sven advocates a new dedicated %META:FIELD datum, named as some mutation of the isodate field, Eg.:

 %META:FIELD{name="Date" value="1997-07-15T19:20:30.45"}%
 %META:FIELD{name="Date_offset" value="+01:00"}%

Which I think sounds good, but perhaps we can control that with an isodate+offset sub-type

FWIW in the bibtex formfield type, I've added "single-field-entry, multilple-fields-generated-on-save" in BibtexFormfieldsPlugin

-- PaulHarvey - 22 Feb 2012

mmm, my main concern is to do with how the data is stored in the file / database. To me, IsoDate - or any string is not an in-database (txt, sql, nosql) format of choice, as it will seriously blow out any queries done.

so what i was advocating is:
 %META:FIELD{name="Date" value="123434567"}%
 %META:FIELD{name="Date_enteredOffset" value="+01:00"}%

specifically so that backend operations are fast (the query engine does not need to do TZ calculations to work out what topics have dates between T1 and T2, or convert, add 30 days then re-convert.

Ignoring my concern about MongoDB and SQLStore indexing issues (and after bashing it out on irc, please ignore it, its my problem to solve), I'd suggest:
 %META:FIELD{name="DateWithTzField" epochseconds="123434567" enteredTZ="" enteredOffset="+01:00" value="1997-07-15T19:20:30.45Z+01:0"}%
 %META:FIELD{name="LegacyDate" epochseconds="99123434567" value="2/3/2012"}%

(where epochseconds is in gmtime for performance and safety)

So, YES PLEASE to having a date view, entry and storage system that keeps the date and tz data - I need that too. (but this is a UI issue, not a 'whats in the format of the date in the META::FIELD issue

my only concern is naming:

as far as the user is concerned, they care about storing either a date or a date+tz (i'd love the first to just die). How we store that, depends on making the backend perform.

I think they're more likely to see and use something called date+tz than they are to find isodate in the list, and know what it means.

-- SvenDowideit - 23 Feb 2012

+1 to the idea to clean up date/time storage! The above discussion is really for date/time, not date. I'd like the ability to specify both ... perhaps it would be appropriate to call the new datatype datetime (because that's really what's being stored), and stuff something in the attributes property for properties that really are storing dates. I don't think that the entered TZ matters at all for pure dates.

Also, I agree with SvenDowideit that it's important to store number of seconds past the unix epoch, UTC for simplicity of queries.

-- KipLubliner - 23 Feb 2012

I like datetime for the name of the new formfield type. I don't like splitting up a single field value into multiple %META:FIELDs. Each %META:FIELD is a container for a list of key-value pairs. So why have two lists with one value each? So my +1 to the according proposal to have epoch seconds, tz offset in a single %META:FIELD. Speeds up queries - eases date conversion.

Whether to store the value as displayed along with it is problematic. Imagine you'd suddenly like to change the display format for all of your data. That's a huge bunch of redundancy storing the date as displayed along with the raw data, besides the %META:FIELD being redundant in itself.

The way data is displayed is normally up to a "view" on that data. The only location where to store the view for a formfield in foswiki is either the DataForm definition, or part of a view template, if none of these exist falling back to a preconfigured {DefaultDateTimeFormat} (we only have {DefaultDateFormat} right now=.

So I'd propose to store the actual way a formfield is displayed in the values column of a DateForm definition, not as part of the data record itself.

Of course using the values column is a problem in itself as it is used in "non-values" way by various formfield types already. It surely would be better to add a separate column just to parametrize the formfield further ... which needs a separate proposal of its own.

-- MichaelDaum - 23 Feb 2012

So, can we decide on how dates should be stored? I'm worried we'll scare Arthur away from his own proposal smile

-- PaulHarvey - 23 Feb 2012

More data about the need for having a way to declar that a field stores a date (not datetime):
  • If users in two different TZ enter the same date, then they both should map to the same epochseconds value (at midnight UTC). (In one wiki, I currently have some code that truncates the time portion of my datefield. This is ugly.)
  • Helpful hints that could be used by RenderFormPlugin, JSCalendar.
  • I think that date will be more widely used in wikiapps than datetime.
2012-02-23T00:22:30Z
2012-02-23
2012-02
2012

%META:FIELD{name="aDateTime" epochseconds="123434567" value="2012-02-23T00:22:30-07:00"}

  • value contains an ISO 8601 date/time, with the TZ that the user entered.
  • epochseconds contains seconds after the Unix epoch, UTC.
  • The entered TZ can be found from the value, by discarding all text before + or - character.

%META:FIELD{name="aDate" epochseconds="123434000" value="2012-02-23"}

%META:FIELD{name="aMonth" epochseconds="123400000" value="2012-02"}

%META:FIELD{name="aYear" epochseconds="123000000" value="2012"}
| *Name*    | *Type*         |
| aDateTime | datetime       |
| aDate     | datetime+date  |
| aMonth    | datetime+month |
| aYear     | datetime+year  |

-- KipLubliner - 23 Feb 2012

Why not just
%META:FIELD{name="aDate" value="123434000"}
%META:FIELD{name="aMonth" value="123400000"}
%META:FIELD{name="aYear" value="123000000"}

The formfields are defining the display value sufficiently using datetime+year, datetime+month etc. There's no need to tell the thing "2012", given that 123000000 was "2012" in epoch secs.

-- MichaelDaum - 23 Feb 2012

Michael: Personally, I'm perfectly happy with storing the epochseconds in the value property. But remember that we need to store two items: the epochseconds, and the time zone offset for the time that the user originally entered. This is the minimum required info, with no redundancies:
%META:FIELD{name="aDateTime" value="123434567" TZ="+01:00"}
%META:FIELD{name="aDate" value="123434000" }
%META:FIELD{name="aMonth" value="123400000"}
%META:FIELD{name="aYear" value="123000000"}

-- KipLubliner - 23 Feb 2012

We can easily have separate isodate vs isodatetime types.

There are ways of signaling that the old date type is deprecated, Eg. an foswikiAlert inline on renderForEdit (like the MISSING TYPE warning).

If isodate is too geeky... date2?

-- PaulHarvey - 23 Feb 2012

Instead of having tons of formfield types together with their counterparts as a perl class for every different format of a datetime, let's please agree on having one single datetime formfield type and subtypes like Kip proposed above. isodates are then produced by datetime+iso.

-- MichaelDaum - 24 Feb 2012

There are two reasonable storage formats for date/times: epochseconds, and ISO 8601 formatted string. There are two advantages to the 8601 string:
  1. One atomic value can contain full information about the time and TZ;
  2. The value can be interpreted by humans directly.

However:
  1. We will want to search and sort using this new date/time field. If we use the ISO 8601 format, then it means that we can't sort on that field directly (because of multiple TZs).
  2. Humans generally will not want to view the field directly, anyway. They prefer to see date/times in their time zone. (User's TZ could be a new field in UserForm)

So I am raising a concern because:
  1. I think that storing epochseconds is a better solution, and don't see any compelling arguments otherwise.
  2. If we are going to specify storage for date/time fields, we should also specify storage for date fields using a consistent mechanism. Otherwise the problem will be "half-solved".

If we must store everything in the value property, we could do it like so:
%META:FIELD{name="aDateTime" value="123434567+0100"}

-- KipLubliner - 25 Feb 2012

That seems the most efficient, and easy to parse.

But if we still want to support readability by humans, we could write our own "ISO date without delimiters" format:

2012-07-16T19:20:30.45+01:00

then becomes:

20120716192030.45+01:00

This string can be read as a float, so can be used for sorting and filtering, and is easy to format to an ISO string.

Dealing with other ISO dates, for example simply the year:

2012

would be stored as the date 2012-01-01-00:00:00, so without delimiters:

20120101000000

I am also in favor of the name datetime. It sounds a bit friendlier than isodate, and sounds more specific than (current) date.

-- ArthurClemens - 25 Feb 2012

The crux of the question is: what should the internal representation be optimized for: readability by humans, or sorting/searching? I believe that the internal representation should be optimized for sorting/searching. If there are "leakages" whereby the raw internal representation is shown to end-users, then those should be fixed. I think that end-users will generally want to see the time in their time zone anyway - not in UTC time (and certainly not in epochseconds!).

-- KipLubliner - 26 Feb 2012

+1 for datetime. But I'd rather choose between ISO date string or epoch seconds, rather than making something Foswiki-specific.

Kip: the problem is that wiki-app'ers must know about epoch seconds to write HTML <form> markup, POST updates to formfields via AJAX, etc. which would be unusual (stuff carried over HTTP is rarely using epoch seconds). This is a "leak" that cannot be fixed, because Fowiki::Form is a V (with a bit of C) only, we're missing the M of MVC to make this work properly.

Not to mention that the Topic.txt is our "leaky" API, and IMHO should be up to the store to digest an iso-date into something more performant, but I guess we're already using epoch seconds in the TOPICINFO anyway.

I'm not sure that parsing an iso-date is actually a big deal (especially in the ocean of all our other inefficiencies), but I understand the desire to avoid adding more slowness.

If you are all convinced that ISO dates will add too much CPU overhead... then I guess we should store them as epoch seconds.

-- PaulHarvey - 26 Feb 2012

According to perlnumber:
On typical hardware, floating point values can store numbers with up to 53 binary digits, and with binary exponents between -1024 and 1024. In decimal representation this is close to 16 decimal digits and decimal exponents in the range of -304..304.

At least storage of a Foswiki-specific number should not be a problem.

-- ArthurClemens - 26 Feb 2012

A base-2 float really shouldn't be used here, though I agree with you it shouldn't be a problem.

-- PaulHarvey - 26 Feb 2012

1. After more reflection, I'm OK with storage in ISO date format in UTC. This format is almost as good as epochseconds for sort/search. The only query that can't be written efficiently is "aDateTime1 + 300 < aDateTime2".

2. Have you guys see the Wikipedia page about this date format? http://en.wikipedia.org/wiki/ISO_8601 The following representation is part of the spec:

20120227T121130Z

The "Z" at the end means UTC. If it was absent then the string should be interpreted as "local time", which in this case I would guess means "server time".

3. Arthur has specified that these fields follow an exact convention. Does this mean that wiki app authors are expected to follow the convention, or will there be some helper code to ensure that the field values get set to the correct format? Perhaps even converted from their local TZ to UTC?

4. For datetime+date fields, it's important to not store the time portion at all (or to make sure that it gets stored as all zeroes). This will let us compare two dates using =. Will there be helper code to truncate user inputs? (think of a wiki app that uses a JSCalendar which sends a date/time value, we will need to convert it to ISO date format. FYI a legal ISO date format is:

20120227

-- KipLubliner - 27 Feb 2012

+1 A better date/datetime format is long overdue, alas it's not that simple.

From http://www.w3.org/TR/NOTE-datetime

Examples

  • 1994-11-05T08:15:30-05:00 corresponds to November 5, 1994, 8:15:30 am, US Eastern Standard Time.
  • 1994-11-05T13:15:30Z corresponds to the same instant.

As stated above these represent the same instant in time. Therefore, if you store these as-is then how will you ensure they sort and index correctly?

Conversely, if you intend to store them in a different form so they index correctly, will you be able to retrieve the original value perfectly from the indexable form?

In the above cases, lets normalise by converting to a UTC value, but keep the offset as well. Then we get

  • 1994-11-05T13:15:30-05:00
  • 1994-11-05T13:15:30+00:00

Now these sort/index correctly and we can retrieve the original value, but I fear I may have missed something here.

As I've discussed on AllowTypedData, we may need to create an index (or many) that's different to the original data. This should be possible with META:FIELD values, i.e. META:FIELD{name="StartDate" value="2012-09-12T08:15:30-05:00" index1="2012-09-12T13:15:30"}. This would require QUERY to use index1 if available when searching, but retrieve value at other times.

If we are really serious about datetime with time zones, do we need to be aware of Europe/London or America/Boston type designations (for DST adjustments)?

Do we want to ignore or consider leap-seconds (which UTC strictly includes)? Epoch time does not handle leap-seconds, as per relevant POSIX standards. If not we need to document this clearly.

Some Unix/Linux variants are now using a 64-bit integer for epochs to avoid the 2038 issue. Any indexing-use of epoch should bear that in mind — at least add some leading zeroes to the epoch seconds storage to handle the future.

A datetime always stored with no offset (i.e adjusted to UTC representing an instant in time), will be a lot easier. How many users/apps of Foswiki need to specify the time zone explicitly?

With my VDBI work, I've recognised that the whole datetime issue is rather large, and that does not include the datetime variations of the underlying SQL engines.

This work needs doing, but I'm concerned about the specifics.

-- JulianLevens - 27 Feb 2012

Hi Julian,

Let me try to think through some of your questions.

  • As for your first example, I think storing the TZ 'locale' (Eg. EST - which btw isn't a unique TZ code, we have it here in Australia too) is just inviting an unnecessary and increasingly intractable mess. I'd draw the line at storing the correct GMT offset with the date. That means any DST component must already be included in the GMT offset. So for Canberra, when we have DST, our offset is saved as +11:00, and +10:00 otherwise. I'm almost 99% sure that's not hard to do (in Javascript it's a matter of Date.getTimezoneOffset()or similar).
    • I was suggesting Europe/London style as these are unique.
  • Your second point is that normalizing to GMT isn't helpful, because it's impossible to trivially reverse the normalisation to recover the original entered value. I think we all agree we need to save the offset.
    • I suspected as much, just didn't have to think it thru.
  • As I've discussed on AllowTypedData, we may need to create an index (or many) that's different to the original data. - yes, Sven & I have come to that conclusion in MongoDBPlugin as well. However, taking a leaf out of Solr's book, I'd always thought it should be possible to automatically generate and query against these indexes transparently. So having multiple indexes for the same piece of data shouldn't pollute Foswiki's embedded .txt form with any differently normalized versions of the original data.
    • I agree with that now.
  • If we are really serious about datetime with time zones, do we need to be aware of Europe/London or America/Boston type designations (for DST adjustments)?As I mentioned earlier, I think we should draw the line at just saving an accurate GMT offset which already has the DST component removed.
    • I think that this is OK. However, if we wish to display it adjusted locally, then Europe/London would ensure DST is taken into account. Otherwise users have to manually adjust there time zone offset with clock forward/backward.
  • Do we want to ignore or consider leap-seconds (which UTC strictly includes)? Epoch time does not handle leap-seconds, as per relevant POSIX standards. If not we need to document this clearly. can you elaborate? It seems leap-seconds should be handled when converting fromepoch, back to something human-readable. Is that a difficult problem to solve?
    • POSIX days are always 86400 seconds per day, with UTC some days can be 86401 or 86402 seconds long. For 99.99% (or higher) cases it really does not matter. I'm just saying that the ISO standard talks about UTC, and UTC by definition includes leap seconds. Therefore our datetime is only roughly ISO (and not UTC).
  • Some Unix/Linux variants are now using a 64-bit integer I agree that this shouldn't be a concern for Foswiki core but rather implementers of query algorithms/stores.
  • How many users/apps of Foswiki need to specify the time zone explicitly? I thought this point had been settled; but any wiki-app which covers events that are not in the immediate locale of wherever the server is situated, will suffer significant usability problems if we don't have a solution to keep the offset with the datetime. No, converting to the logged-in user's TZ is not appropriate, when the user really doesn't care where they themselves are, but actually the locale in which an event actually happened.
    • I bow to your experience on this.

So, in summary:
  • Are you satisfied that we need to support GMT offsets in datetime (or a subtype thereof?)
  • Are you satisfied that storing the TZ/locale code is unnecessary if we already have the GMT offset (this already includes any DST component, so storing & reversing UTC normalization should be trivial)?
  • Do you think this proposal needs to flesh out the mechanics of supporting multiple normalizations/indexes? Perhaps that's best discussed in AllowTypedData (although I realise that datetime is an excellent case-study to ensure we get it right)?
  • Do you think we need to worry about leap-year conversion in this proposal if using an epoch seconds storage format (isn't that up to whoever tries to render the data for view)? Or perhaps you're alluding to queries with mixed epoch + iso date formats (though I thought d2n() already took care of leap-seconds)?

-- PaulHarvey - 27 Feb 2012

Paul, I've realised that we should indeed simply store the datetime+offset. The indexing as you suggest should be provided via a function registered via registerMETA for datetime.

For other points I've added my responses above.

I'm almost happy, but I have something nagging at me. It may be I read stuff about datetime (general SW eng) some time ago and was surprised about how involved it all was with some important gotchas. However, as I cannot be specific I have removed my concern.

-- JulianLevens - 28 Feb 2012

Thanks Julian, I hope I haven't been obnoxious, and I want you to know I don't consider myself an expert by any means - I just want clarity and to make sure we are all on the same page - you raised good points that we need to get right.

By the way, when I say "I'd always thought it should be possible to automatically generate and query against these indexes transparently..." - I hope yourself, Crawford or Sven can chime in with opinions on just how feasible/tedious/rewarding/costly this might be - otherwise my statement is a little hollow (as somebody who never wrote a store/query algo himself!)

I just wanted to clarify one more thing though

I think that this is OK. However, if we wish to display it adjusted locally, then Europe/London would ensure DST is taken into account. Otherwise users have to manually adjust their time zone offset with clock forward/backward. - Perhaps I'm being thick, so please bear with me:
  • An iso date can unambiguously indicate the local wall-time of an event (which might include DST, if the TZ has DST in effect)
  • An iso date always allows us to unambiguously derive the time in GMT
  • Given that we can always get what the time was in GMT, shouldn't we be able to convert that into any TZ/Locale, automatically and without any additional information?
  • Or perhaps your concern is that the user browsing this time datum might be interested to know if DST was in effect?
  • In which case I'd be happy to draw a line here and say that this is outside the scope of the datetime type; a DataForms user can add their own Locale field, but I suppose in future it's not impossible for us to eventually add a datetime+locality subtype or similar

I hadn't considered leap-seconds previously, I guess that's a minor +1 for storing the iso-date-string rather than epoch seconds

-- PaulHarvey - 29 Feb 2012

Personally, all my Wiki apps so far have used 'pure date' fields, not 'date/time' fields. So this is what I am really interested in getting cleaned up. It seems from his comments that Paul has more actual experience with wiki apps that use date/time, and he is apparently not bothered with the problems that I have raised regarding sorting/searching when we store datetime with user's local TZ. So I am almost prepared to remove my concern. I'm still waiting for some answers:

  1. Arthur has specified that these fields follow an exact convention. Does this mean that wiki app authors are expected to follow the convention, or will there be some helper code to ensure that the field values get set to the correct format?
  2. For datetime+date fields, it's important to not store the time portion at all (or to make sure that it gets stored as all zeroes). This will let us compare two dates using =. Will there be helper code that recognizes 'pure dates' (perhaps via using a datatype of 'datetime+date') to truncate user inputs? (think of a wiki app that uses a JSCalendar which sends a date/time value, we will need to convert it to ISO date format.)

-- KipLubliner - 29 Feb 2012

Paul, no obnoxiousness detected, but I understand the fear of being obnoxious in these discussions. Some of the language needed to focus in on various points can appear to be somewhat robust. Something in text can appear really harsh that in a face-to-face discussion would not be noticed. That's why we have emoticons smile (which I don't use enough)

I wasn't clear enough anyway wink

My Europe/London type suggestion was not to do with storing this in the datetime data-type, but in formatting the result back to the logged-in user in their time zone. Therefore each user would need to have their time zone in their userform, the offset would not suffice.

Imagine a list of datetimes from around the world pulled back from a database. Suppose that it was appropriate to show each datetime as-if it were local — the user wishes to see when various events occured (in sequence) and the original local-time and time zone is not relevant.

Some of the datetimes returned will be GMT and some DST (and always Europe/London), to adjust correctly you need a database of the DST changes for Europe/London or Europe/Amsterdam or whatever. If the userform only contained +0:00, then all datetimes would be formatted to UTC+0:00, which will not always be right — possibly good enough everything is effectively shown as GMT and will always be in the right sequence.

As for leap seconds, neither epoch time or an ISO-Date format is perfect (but ISO-Date is better), simply because no underlying OS I am aware of (Unix/Linux/Windows/Whatever) cares about leap-seconds. So while a user may enter a datetime with leap seconds or capure data from another source with leap seconds

I am not an expert either. Dave Rolsky put together the perl datetime project (datetime.perl.org) specifically to solve the lack of complete and comprehensive support for datetime within perl. Foswiki is at that same point, doing this really right might mean pushing out to 2.0 (but hopefully earlier). Using the experience gained here may save us many headaches in the future.

Adding DateTime was mooted in the past as being added to the core distribution. It was not done as analysis of existing FW date time parsing/formatting etc was in some ways more powerful. However, now we are looking at proper ISO/UTC support this should probably be revisited. DateTime has a built in time zone database (Europe/Amsterdam et al). This database includes the old style OLSEN database (GMT EST etc, which is deprecated due to lack of uniqeness, but cannot quite be ignored).

Apart from doing a lot of reading, an email to Dave Rolsky to see if he is willing to answer any questions would seem to be a good idea. He may well be able to give us some quick definitive answers, which none of us seem be able to do. Crucially, other gotchas may be pointed out to us.

A quick look at the DateTime POD suggests that storing the Europe/Amsterdam type stuff is crucial for accurate datetime maths. There also appears to be epoch support, but I do not know how it handles leap seconds.

I have also considered whether things like datetime; datetime+locale or datetime+UTC or whatever makes sense. The danger is that we end up with many alternatives to choose from (choice is not always good) and significantly more support issues.

I'd prefer to get this absolutely right, it's quite a big deal, and never have to worry about it again smile

-- JulianLevens - 29 Feb 2012

Ah, yes. I agree with what you say. I personally use CPAN:Time::ParseDate; quite extensively for parsing date strings in my own code; did we really reject including a CPAN thing to do this crucial yet boring job for us? Time::ParseDate is a brilliant thing which seems to DWIM (or do-what-my-users-meant) more reliably than Foswiki::Time, though I get the feeling it's a lot heavier CPU-wise in a tight loop.

Reading DateTime, I'm still not convinced we need to store the locale of the recorded time: storing localtime+gmtoffset means we already have the local(ized) wall-time, DST or not, so having a Europe/London locale datum seems unnecessary to understand & convert the datetime into other locales (in my own datetime wiki-data, we record a bunch of locale stuff separately: locality name, geocode, lat/long)

But, as you suggest, we do need Europe/London locale strings populated in user topics where we want to perform localization of these datetimes when they are viewed

So I am hoping that my difficulty in understanding your argument is that I failed to see that you were talking about localizing times on view, per-user (or per-whatever-else), not the actual storage; so we can make that a view issue which we solve separately (Foswiki is desperately missing per-user time localization, but I think doing all that work is way beyond the scope of this proposal, but should certainly support that work).

Or am I still missing your point?

-- PaulHarvey - 29 Feb 2012

Hi Kip,
  • I think wiki-app'ers need to conform to the format exactly (it shouldn't be hard). If you want free-form-strings that kinda look like dates, then that's what the old date type was for. Having said that, there's a lot we can do to help wiki-app users: make Foswiki complain loudly and usefully when malformed strings are detected. Speaking for myself, silent failures are a terrible mis-feature/usability shortcut which I don't want to see continue in Foswiki.
  • I guess it's possible to do this kind of transformation on save, but it's still a kind of "silent magic" that's acting as a band-aid to compensate for some badly-behaving external agent (the wiki-app or JS code). I don't know about the others but personally I'd rather help users of datetime fix their stuff, and/or make sure JSCal behaves properly by default (it shouldn't be hard to get it right), rather than second-guess intentions by re-writing the user input

-- PaulHarvey - 29 Feb 2012

Kip: I had problems with dates when I first set-up (tm)wiki here (now Foswiki of course). Some notes I dug out (2007/2008 ish):

  1. Inconsistent date usage across Wiki -- date does not work in Forms and ActionTrackerPlugin edit
    1. JSCalendarContrib
      1. Added values in LocalSite.cfg to set default date to CCYY-MM-DD
    2. DateFieldPlugin
      1. Changed DateFieldPlugin.pm so default date format is CCYY-MM-DD
    3. ActionTrackerPlugin does not work with date pop-up when editing an Action
      1. Changed Format.pm to use CCYY-MM-DD as standard date format for JSCalenderContrib
      2. Changed Action.pm function formatTime to use Foswiki::Func::formatTime to use CCYY-MM-DD format

Basically across the board it ensures all dates are CCYY-MM-DD, everything always sorts correctly and nobody is ever confused about whether its MM-DD or DD-MM. As it's an ISO8601 date format its quite portable in general, e.g. it's Excel & MySQL friendly.

There's almost no actual need for this proposal for CCYY-MM-DD type formats, although it would be better if the equivalent of the above hacks were standard.

Paul: there were two main points
  1. Being able to view in the correct locale (does not require Europe/London etc to be stored for each datetime)
  2. Being able to do datetime maths correctly (doesrequire Europe/London etc to be stored for each datetime)
    • So, this is where I'm confused - I don't see why the locale of the stored datetime is necessary, if you already know unambiguously what the exact point in time was at GMT: don't you already have everything you need to localize it into any other locale? What am I missing?

I believe that we can probably get away without storing time zone, but just tz_offset. We do need to ask an expert about this.

Most of the time with SW Eng (or any sort of development) the 80/20 rule applies. We solve can solve 80% of the current problem with 20% of the effort. A couple of years later we revisit the problem and shave another 80% off the remaining problem for another 20% effort (ie. 96% of the original problem is now solved). However, sometimes you discover that your 80% solution made solving the next chunk much harder. Therefore, sometimes you need to do 100% (or at least as close as you can) first time — or at least prove that the 80% first go will not compromise the complete solution. I do not think that any of us really know that not storing time zone=Europe/Amsterdam is not going to cause significant further grief/rework. I think this is my ultimately my sticking point: it's probably OK but I'm just not certain (and is the cost of being wrong too high).

Note that MySQL 5.5: http://dev.mysql.com/doc/refman/5.5/en/datetime.html, converts to UTC when storing on the DB and converts back to the local server time on retrieval, which will be consistent if the servers time zone is the same when storing and retrieving. IOW, it's not really complete coverage (I think).

One of my VDBI headaches is going to be the datetime inconsistencies/shortcomings across the different flavours of SQL. I can circumvent a lot of this by doing this myself and storing a string, which as long as it sorts appropriately (or an indexable version anyway) will still give most of the benefits. We will lose the abilities of the SQL engines to do datetime math on our behalf. However is that really common usage?

-- JulianLevens - 29 Feb 2012

Okay, so that's where my understanding isn't the same as yours.

I wanted to keep GMT offset not for accurate datetime maths, but to preserve important contextual information about a datetime so that users can still work with them intuitively.

For datetime maths, I was under the impression that you don't even need the GMT offset: if you already know exactly, unambiguously and repeatably what the time was in GMT - down to the second (or finer) - why do you need the stored time's locale to then localize the GM time into any other locale?
  1. You just need the target locale, to grab the offset for that year/month/day
  2. Apply the offset (which would include any possible DST component)
  3. Profit? smile

I'll try to do some research to learn about this further, but I couldn't find a clear answer in the CPAN:DateTime POD, but perhaps I skimmed too carelessly.

I'm sure there should be some information out there somewhere to educate me, but if you are in contact with the author, asking him to point us in the right direction would be great!

Perhaps it would also make a good stackoverflow question (if there isn't something already there).

-- PaulHarvey - 29 Feb 2012

I did a check with Apple's Cocoa framework. To my surprise NSDate only stores seconds internally (with a reference point of 1 January 2001, GMT). So developers store the date-time as absolute time, and display using a NSTimeZone object.

So according to Apple you don't need the GMT / UTC offset. You need to make sure to not store dates in computer time or webserver time but in GMT only.

-- ArthurClemens - 01 Mar 2012

To bring this proposal back on track, I think it makes sense to bring in the earlier mentioned DateTime. It is a mature and extensive library for working with dates and time zones. It seems that it stores dates in floats (nanoseconds): If the epoch value is not an integer, the part after the decimal will be converted to nanoseconds. ( source

The proposal then becomes:
  • Next to date, add a new date type: datetime.
  • datetime stores dates in fractional seconds and always uses this same format.
  • Handling of dates: comparison, getting time zone values, storing the datetime seconds relative to the user's time zone can all be done with the library DateTime. Therefore:
  • Add DateTime to included CPAN libs
  • Rendering to javascript for an interactive date picker is out of scope of this proposal. But getting parsable dates for javascript rendering can be faciliated by:
    • creating an interface from datetime Georgia nanoseconds to epoch seconds that Javascript can handle
    • adding (in handler renderForEdit) the html attribute data-format='datetime', so that jQuery code could add a date picker

-- ArthurClemens - 07 Mar 2012

The DateTime library looks very nice. Arthur, can you please let me know what is your vision for handling 'pure dates'? Would you recommend that wiki apps store them as 'date' types YYYYMMDD? Or as 'datetime', with the wiki app author responsible for truncating out the hours / minutes / seconds? Or as 'datetime+date', with (hopefully someday) renderforedit datepickers that produce correctly truncated iso8601 dates.

I'm removing my concern because I don't have an objection to this proposal per se - I just want a nice, consistent way that 'pure dates' should be stored in Foswiki and it seems that now would be an ideal time to specify that. But this is technically asking for more than what Arthur is proposing to implement.

-- KipLubliner - 08 Mar 2012

Created PureDateHandlingRequirements, to write up my thoughts about what is important to me.

-- KipLubliner - 13 Mar 2012

As the per datetime project has much more experience than us I sent off an email to Dave Rolsky to see what we can learn.

An important question he answered directly is:
So the question is: does DateTime use any C? If so, is this restricted to
some of the (optional?) sub-modules or the main module?
 
DateTime has some optional XS code, but it should install without a compiler.
 
I think the same goes for its dependency chain. If not, I'd consider that a bug, since I've tried to keep it working on systems without a compiler.

He also directed me to the mailing list. See here: http://www.nntp.perl.org/group/perl.datetime/2012/03/msg7805.html for my original question and the reply here: http://www.nntp.perl.org/group/perl.datetime/2012/03/msg7806.html

Based on that one reply, we need at least to give the user options.

Base Leap seconds? TZ offset TZ name
2012-03-20T16:34 Not likely with accuracy to minutes Not required if only recording UT for historical event. Still an option if you want to know the local time of the actor Necessary for future events
2012-03-20T16:34:23 Optional to allow user to track if required " "
2012-03-20T16:34:23.034923 Implied when you're recording accurately " "
I'm still left thinking the TZ name is also important in some cases for datetime maths. Maybe I should go back and ask more questions.

It boils down to:
  • CCYY-MM-DDTHH:MM [(+epoch_secs|+leap_secs) [+fractional_secs]] [+TZ offset] [+TZ name]

With appropriate docs to explain why and when you should use the various options.

As ArthurClemens suggested bringing DateTime into the project would make a lot of sense. Indeed it supports all of the above options and maybe more. It would seem appropriate to more or less map what DateTime give us as various Foswiki types. We can also lean on their existing documentation to provide guidance.

Does the committed developer wish to do everything at once, or pick a few useful starting formats and then build.

It's also worth bearing in mind that some of these alternatives will not be compatible with each other, whereas some will. They will in fact be distinct types.

-- JulianLevens - 20 Mar 2012

Cool - that certainly clarifies things for us. Is there any opinion on how we should allow the user to customize their datetime?

My preference is that we should (ab)use the existing subtypes syntax as per select+multi, select+multi+values etc.

So, datetime+[ISO 8601 datetime format] ?
  • Eg. datetime+CCYY, datetime+CCYY-MM-DD, datetime+CCYY-MM-DDThh:mm:ssTZD

Arthur wouldn't have to implement all 8601 formats or permutations thereof, but settling on the syntax would be a huge start.

I'm not sure how we'd set the leap-seconds flavour from here. I'd very much prefer we somehow avoided making the user think about that; when it comes to information exchange, everything should be UTC.

-- PaulHarvey - 21 Mar 2012

I have updated the proposal above and am working on an implementation.

-- ArthurClemens - 05 May 2012

Yes, I know... this comment is completely out of scope.

Looking at the issues exposed by Item11869, and across versions of perl, it might be worth considering moving all Foswiki date handling to CPAN modules:
  • Use CPAN:DateTime for formatting and calculating dates, durations,
  • Use CPAN:DateTime::Format::Natural to handle human formatted dates on input. (or one of the many DateTime::Format::* date parser modules). Originally suggested Flexible, but that is not part of the DateTime project, and is not recommended.
Spreadsheet plugin date calculations have similar issues to the core date routines. These all need to be brought together so that you don't get different results from different uses.

-- GeorgeClark - 27 Sep 2012

CPAN:DateTime is now included in DateTimeContrib (which is perhaps not yet a default extension).

CPAN:DateTime::Format::Natural is not yet included in the contrib (CPAN:DateTime::Format::Strptime is).

-- ArthurClemens - 28 Sep 2012

 
Topic revision: r45 - 19 Nov 2015, GeorgeClark - This page was cached on 16 Jan 2020 - 14:12.

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy