Re: [office] Date and Timezone: draft text

[robert_weir@us.ibm.com]

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
>