OASIS Open Document Format for Office Applications (OpenDocument) TC

Here's draft text to clarify that timezone is NOT recorded
in the formula work; comments?  I intend to put this in the section
on Date and Datetime subtypes.  This will (hopefully) resolve the issue,
as discussed in the last TC teleconference.

=====

The Date and Datetime subtypes (number of days since the
beginning of the epoch) do not include a mechanism for recording the
time zone.  Instead, these subtypes only presume that all
date or datetime values use the same unspecified time zone.
Document creators CAN use formulas to do time zone
translations using formulas, and CAN decide on a particular single time zone
such as UTC (Coordinated Universal Time) when developing a particular document.
Similarly, the Date and Datetime subtypes do not include a mechanism
for distinguishing the value of a leap second and the following second,
and presume that every day is exactly 24 hours long.

--- David A. Wheeler 

2008/7/5 David A. Wheeler 

Dave Pawson:
> Outsider view.
> Not a solution. Just more of an interop problem.
> Use iso8601 inclusive of TZ.

We already use ISO 8601, which supports indicating timezones.
Timezone information is _optional_ in ISO 8601, though,
so _not_ indicating the timezone is _still_ compatible with ISO 8601.

There is no timezone data in the built-in "Date" type of spreadsheets.
Writing out a timezone marker (such as "Z" for UTC) would be WRONG,
because there is NO such information in a spreadsheet.

I think it'd be great to extend spreadsheets to provide proper
support for timezones, but that turns out to be:
1. Nontrivial.
2. Implemented by NO spreadsheets

I think it'd be inappropriate to do major surgery on a basic
type when there's no experience with such changes.  Let's standardize
on where there _IS_ agreement first.

I can imagine ways to add timezone information.
Perhaps there could be an additional value attached to the table
or sheet that would indicate the epoch and "default display"
timezones [+/-]xx:yy.  Then you could format displays to
use the "epoch timezone", "default display timezone", or
"UTC".  Another approach, which isn't
inconsistent with this, would be to add functions to do timezone
conversions into strings (so display can be controlled).
Others can probably imagine several more.

But I think it would _wrong_ to standardize what are, essentially,
unproven experiments.

--- David A. Wheeler

"Dave Pawson" <dave.pawson@gmail.com>
wrote on 07/05/2008 02:46:54 PM:


> 2008/7/5 David A. Wheeler <dwheeler@dwheeler.com>:

> > Here's draft text to clarify that timezone is NOT recorded

> > in the formula work; comments?  I intend to put this in
the section

> > on Date and Datetime subtypes.  This will (hopefully) resolve
the issue,

> > as discussed in the last TC teleconference.

>

>

> Outsider view.

> Not a solution. Just more of an interop problem.

> Use iso8601 inclusive of TZ.

>


Interop with what?  If no existing spreadsheet
handles time zones on datetime values, then how can doing the same thing
introduce interop problems?  

-Rob

For reference, here is how OOXML defines
it:

-----------

18.17.4
Dates and Times

Each unique instant in SpreadsheetML
time is stored as an ISO 8601-formatted string, which is made up of a date
component, a time component, and a timezone component. Numerous functions
take dates and/or times as arguments. Functions that care only about the
date shall ignore any time information that is provided. Functions that
care only about the time shall ignore any date information
that is provided. 

[Example: The date
22 November 1976 at exactly 08:30 Pacific Standard Time (+08:00 UTC) could
be represented in the following (non-exhaustive list of) ways within SpreadsheetML:

1976-11-22T08:30:00,000+08:00

1976-11-22T16:30Z
end example]

For compatibility with existing
spreadsheet applications, a consuming application should allow certain
numeric serial values to be interpreted as dates and times for display
or for use in calculations. These values should behave as defined in §18.17.4.1,
§18.17.4.2, and §18.17.4.3.

-----------

However, it then says in 18.17.4.1:

-----------

"A serial value represents a UTC date
and time, and, as such, has no timezone information."

-----------


No Excel versions (that I'm aware of) support
ISO 8601 dates as described in 18.17.4.  They all use the date serial
numbers and treat these serial numbers as described in 17.17.4.1, where
date serial numbers represent a UTC data/time with no timezone info.

Consider what would happen if dates were
treated as having serial numbers.  I enter annual, sales forcastes
in a spreadsheet, 1/1/2000, 1/1/2001, 1/1/2002, etc.  I format them
to show only the date, not the  time.  But internally they are
stored as 2000-01-01T00:00:00+5:00.  I send the spreadsheet to someone
in Chicago, one time zone to the West from me.  If displayed in local
time, the dates will all display a year earlier.  And all calculations
looking at just the year value would be wrong. Why? Because midnight January
1st corresponds to 11pm December 31st the prior year.

This can be fixed.   It would require
that we also add a setting for dates, orthogonal to the display format,
that tells what timezone to show the date value in.  Local time zone?
 A specific time zone?  Original time zone?

However, I do not recommend doing this
for ODF 1.2.  First, no existing spreadsheet application does timezone
calculations with datetime values.  Second, our goal for the first
release of OpenFormula was to precisely and accurately describe the way
spreadsheet formulas currently work.   

David, I wonder if we want to explicitly
state that the OpenFormula dates are explictly in UTC instead of "unspecified
time zone"?  This would
give us something definite to work with if we decided to add timezone sensitivity
in a future version.

-Rob

"David A. Wheeler" <dwheeler@dwheeler.com>
wrote on 07/05/2008 01:39:48 PM:


> Here's draft text to clarify that timezone is NOT recorded

> in the formula work; comments?  I intend to put this in the section

> on Date and Datetime subtypes.  This will (hopefully) resolve
the issue,

> as discussed in the last TC teleconference.

>

> =====

>

> The Date and Datetime subtypes (number of days since the

> beginning of the epoch) do not include a mechanism for recording the

> time zone.  Instead, these subtypes only presume that all

> date or datetime values use the same unspecified time zone.

> Document creators CAN use formulas to do time zone

> translations using formulas, and CAN decide on a particular single
time zone

> such as UTC (Coordinated Universal Time) when developing a

> particular document.

> Similarly, the Date and Datetime subtypes do not include a mechanism

> for distinguishing the value of a leap second and the following second,

> and presume that every day is exactly 24 hours long.

>

> --- David A. Wheeler

>

> ---------------------------------------------------------------------

> To unsubscribe from this mail list, you must leave the OASIS TC that

> generates this mail.  You may a link to this group and all your
TCs in OASIS

> at:

> https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php

>

Rob Weir:
>However, I do not recommend doing this for ODF 1.2.  First, no existing
> spreadsheet application does timezone calculations with datetime values.
> Second, our goal for the first release of OpenFormula was to precisely
> and accurately describe the way spreadsheet formulas currently work.

Agree.

> David, I wonder if we want to explicitly state that the OpenFormula dates
> are explictly in UTC instead of "unspecified time zone"?  This would give
> us something definite to work with if we decided to add timezone
> sensitivity in a future version.

I think that's WORSE.  Almost no spreadsheet documents actually
use UTC, so it would be a lie.  It'd also make it hard to add real
timezone support later.


--- David A. Wheeler

>

> > David, I wonder if we want to explicitly state that the OpenFormula
dates

> > are explictly in UTC instead of "unspecified time zone"?
 This would give

> > us something definite to work with if we decided to add timezone

> > sensitivity in a future version.

>

> I think that's WORSE.  Almost no spreadsheet documents actually

> use UTC, so it would be a lie.  It'd also make it hard to add
real

> timezone support later.

>

> 

Thinking out loud here.

My main concern is that functions like HOUR()and MINUTE()
which extract the hour or minute value from a datetime value are unambiguous.
 If we throw in time zones, then I think we lose that.  (What
is HOUR(15:27:46+01:00) in Bangalore, India?)


ISO 8601 states that the notation "18:30:00"
expresses local time.  You can make it expressly UTC by writing "18:30:00Z".
 Similarly, you can give a specific time zone offset by writing "18:30:00+05".
 Each of this notations can be combined with dates to give strings
like "2008-12-24T18:30:00+05".

So if we merely leave it as-is, then the date/time
values are stored in local time, in an application-dependent fashion, in
what may be a time-zone dependent fashion as well.  That is how the
spec reads to me now.

But I don't think implementations work that way.  If
I enter the NOW() function into a spreadsheet in Boston, and at the same
instant someone enters NOW() into a spreadsheet in London, we do not see
the equivalent times recorded.  We each record our local times, without
specifying a time zone. 

That is how it is done today, by all spreadsheets
that I'm aware off.

So let's look at your proposed text:



>The Date and Datetime subtypes (number of days since the

>beginning of the epoch) do not include a mechanism for recording the

>time zone.  Instead, these subtypes only presume that all

>date or datetime values use the same unspecified time zone.

>Document creators CAN use formulas to do time zone

>translations using formulas, and CAN decide on a particular single
time zone

>such as UTC (Coordinated Universal Time) when developing a particular
document.

>Similarly, the Date and Datetime subtypes do not include a mechanism

>for distinguishing the value of a leap second and the following second,

>and presume that every day is exactly 24 hours long.

In first sentence, can we simply say something like
"Date values shall be stored using the Extended 'Complete Calendar
date' notation defined in ISO 8601:2004, clause 4.1.2.2.    Datetime
values shall be stored using the Complete 'Date and time of day' notation
defined in ISO 8601:2004, clause 4.3.2, with empty zone designator in accordance
with local time provisions of ISO 8601:2004, clause 4.2.2.2."

I think that restates your main thought, in ISO 8601
terms.

As for leap seconds, ISO 8601 notates them as "23:59:60"
followed by "00:00:00".  So we could handle them if we wanted,
by modifying SECONDS() to return a value from 0 - to 60, instead of 0-59.
 But I doubt any spreadsheet treats these two values as different.


-Rob

2008/7/7  

"Dave Pawson" <dave.pawson@gmail.com>
wrote on 07/07/2008 02:49:20 AM:


> 2008/7/7  <robert_weir@us.ibm.com>:

>

> > Thinking out loud here.

> >

> > My main concern is that functions like HOUR()and MINUTE() which
extract the

> > hour or minute value from a datetime value are unambiguous.

>

> No, they are ambiguous as to TZ.

> Anything beyond that is a presumption.

> 

You are wrong.  These functions are not ambiguous.
 They give the same answer on every spreadsheet, in a location/time
zone independent fashion.  This is not presumption.  This is
accurate observation.  This is what the users see today, what they
are accustomed to, what they expect.


>

> >

> > ISO 8601 states that the notation "18:30:00" expresses
local time.  You can

> > make it expressly UTC by writing "18:30:00Z".  Similarly,
you can give a

> > specific time zone offset by writing "18:30:00+05".
 Each of this notations

> > can be combined with dates to give strings like "2008-12-24T18:30:00+05".

> >

> > So if we merely leave it as-is, then the date/time values are
stored in

> > local time, in an application-dependent fashion, in what may
be a time-zone

> > dependent fashion as well.  That is how the spec reads to
me now.

>

> That's one interpretation.

> 

Care to offer another interpretation?  ISO 8601
seems clear on this point.


>

> >

> > But I don't think implementations work that way.  If I enter
the NOW()

> > function into a spreadsheet in Boston, and at the same instant
someone

> > enters NOW() into a spreadsheet in London, we do not see the
equivalent

> > times recorded.  We each record our local times, without
specifying a time

> > zone.

>

> Fine if Boston users never communicate with London users.

> They do.

>

> That' the interop problem.

> 

If we did what you suggest, every user of an ODF spreadsheet
would get answers that contradict what Excel and every other spreadsheet
does, and would generally cause chaos as financial spreadsheets gave different
answers as they were mailed around the world.

Is this interoperability?

> http://www.w3.org/TR/xpath-functions/#func-current-dateTime

>

> provides a reasonably comprehensive set of thought out datetime functions.

>


And there is a place for this.  But current practice,
and current user expectations is that date calculations are not time zone
sensitive in spreadsheets.

-Rob

2008/7/7  

"Dave Pawson" <dave.pawson@gmail.com>
wrote on 07/07/2008 08:55:11 AM:


> 2008/7/7  <robert_weir@us.ibm.com>:

>

> >   But current practice, and current user

> > expectations is that date calculations are not time zone sensitive
in

> > spreadsheets.

>

> So who makes the first move to improve things?

> Wait for Excel?

>


As I said before, I'd like to advance the state of
the art in spreadsheet analytics in ODF 1.3.  We're certainly collecting
a good set of ideas in that area, from numbers with units, to stronger
type checking, to time-zone sensitive dates, etc.

I don't care which application implements it first.
 We're dealing with the format here.

-Rob

> So who makes the first move to improve things?
> Wait for Excel?

No, nor has anyone claimed that.  All that's needed is
that SOMEONE demonstrate a reference model.

ANYONE can take an existing open source software
implementation, improve on it, and demonstrate that the
approach works.  It need not be an "official" release by
the project, though of course that would add weight.
If there's a demonstrated way to add that functionality,
and it gains rough consensus, then of course it's in
and I'd be delighted.

But humility in standards development is _critical_.
There is a very, very long list of "standards" that failed
because the committee wrote a standard or added an
"obvious little extension" in it, and THEN (after ratification)
someone tried to implement it.  The universal result has been that it
couldn't be implemented, or at least not in a way anyone
wanted.  Standards co-developed with
implementations is, in my view, the best approach.

At the least, no specification should be
accepted until there is at least one demonstration of
its effectiveness (e.g., a reference implementation),
and we are trying to wrap up OpenFormula 1.0.

I know timezones look easy, but adding this capability
is not as trivial as it appears.
The date and datetime representations of spreadsheets,
which users have found adequate for decades, do not
easily support them.  Numbers don't include a time zone.
Adding a mechanism that is unimplementable,
or makes it impossible to exchange existing documents, is unacceptable.

I'm not saying "timezones never go in".  But we're trying to
wrap up a spec that enables exchange of _existing_ documents,
none of which have timezones.  Let's get some experimentation/
reference model work going, and once a working solution
has achieved rough consensus, let's get that added to the NEXT
release of OpenFormula.

"Rough consensus and running code" is still the best way
to achieve useful standards; just compare OSI vs. TCP/IP.

--- David A. Wheeler

On Sun, 2008-07-06 at 11:49 -0400, robert_weir@us.ibm.com wrote:
> 
> For reference, here is how OOXML defines it: 
> 
> ----------- 
> 
> 18.17.4 Dates and Times 
> 
> Each unique instant in SpreadsheetML time is stored as an ISO
> 8601-formatted string, which is made up of a date component, a time
> component, and a timezone component. Numerous functions take dates
> and/or times as arguments. Functions that care only about the date
> shall ignore any time information that is provided. Functions that
> care only about the time shall ignore any date information 
> that is provided. 
> 
> [Example: The date 22 November 1976 at exactly 08:30 Pacific Standard
> Time (+08:00 UTC) could be represented in the following
> (non-exhaustive list of) ways within SpreadsheetML: 
> 
> 1976-11-22T08:30:00,000+08:00 
> 
> 1976-11-22T16:30Z 
> end example] 

Given that a date-time has at least 25 different representations (and
likely many more), the statement "Functions that care only about the
time shall ignore any date information that is provided" means that
those functions would not  well defined.

How is this ever supposed to make sense?

Andreas


-- 
"Liberty consists less in acting according to
one's own pleasure, than in not being subject 
to the will and pleasure of other people. It 
consists also in our not subjecting the wills 
of other people to our own."  Rousseau


Prof. Dr. Andreas J. Guelzow
Dept. of Mathematical & Computing Sciences
Concordia University College of Alberta

> > For reference, here is how OOXML defines it: 
...
> Given that a date-time has at least 25 different representations (and
> likely many more), the statement "Functions that care only about the
> time shall ignore any date information that is provided" means that
> those functions would not  well defined.
> 
> How is this ever supposed to make sense?

It doesn't, and it can't.

This is actually the problem I'm trying to _avoid_.
It's clear that, as part of the ISO process, somebody said
"OOXML should support timezones", so somebody added some text about
timezones.  But that material is clearly incompatible with actual spreadsheets,
and it's incompatible with other parts of the same spec :-(.
Saying "timezones would be nice to have" must NOT
translate into some random text that is unimplementable or is
incompatible with existing spreadsheets.

I'm sure that what they meant was "if you only need time-of-day,
use X-INT(X); if you only need the day, use INT(X)."

Please understand, I'm not against timezones.  But since timezones
have never been supported by spreadsheets, extreme caution is warranted.
Unless there's an "obviously correct" solution for supporting timezones,
that can also support existing spreadsheets,
it's better to leave them out in this version.

BTW, _not_ all timezones only increment the "hour" value; some
have different values of "minutes".  Kabul, Afghanistan, is
UTC/GMT +4:30 hours. Katmandu, Nepal, is UTC/GMT +5:45 hours.
And that's only an instantaneous timezone; daylight savings time
creates MANY more complications.

You CANNOT simply store
everything as UTC and then do simple timezone corrections, while
having existing spreadsheets actually work correctly.
To store everything as UTC (as Unix and Linux systems do) requires
sophisticated corrections for daylight savings time (which vary depending
on geographical locations and the whims of legislatures).
A Unix/Linux system can do this, and in fact it's a remarkably elegant
solution for their time systems.  However, this works because these
systems _DO_ have information on which timezone is selected.
This information that is not available in a numbers-only encoding
(which is what a spreadsheet has).  What's more existing spreadsheets
do NOT expect to deal with this.

There may be an elegant way to deal with timezones.  But such
an elegant way must be defined in great detail, support existing
documents, and be "obviously right".  Otherwise, let's not bother.
There's no point in specifying a complicated system that gets it wrong.

BTW, I used to lead work on doing time-related standards.

--- David A. Wheeler

Hi,

On Sunday, 2008-07-06 23:03:27 -0400, David A. Wheeler wrote:

> BTW, _not_ all timezones only increment the "hour" value; some
> have different values of "minutes".  Kabul, Afghanistan, is
> UTC/GMT +4:30 hours. Katmandu, Nepal, is UTC/GMT +5:45 hours.
> And that's only an instantaneous timezone; daylight savings time
> creates MANY more complications.

Just for the records: historical timezone changes create even more
complications, many countries did not stick with their timezone all the
time. Plus timezone offset may even include seconds (or milliseconds),
not just minutes. For example, TZ=Europe/Moscow 1916-07-03 <= date <=
1919-07-01 has a time zone offset of +2:30:48 instead of +3:00, for date
<= 1916-07-03 it is +2:30, TZ=America/St_Johns date <= 1935-03-30 has
a time zone offset of -3:30:52 instead of -3:30

For sources of more confusion see for example
http://unicode.org/cldr/data/tools/java/org/unicode/cldr/util/data/europe
http://unicode.org/cldr/data/tools/java/org/unicode/cldr/util/data/northamerica

Any application claiming to implement timezones probably does not.

  Eike

-- 
 OpenOffice.org / StarOffice Calc core developer and i18n transpositionizer.
 SunSign   0x87F8D412 : 2F58 5236 DB02 F335 8304  7D6C 65C9 F9B5 87F8 D412
 OpenOffice.org Engineering at Sun: http://blogs.sun.com/GullFOSS