A full specification of a (non-UTC) date-and-time/timestamp, according to ISO standard 8601, should specify the timezone (as an offset from UTC). For example, one of the standard-defined formats for a combined specification looks like: 2024-11-01T18:42:40+01:00 that is, Date: 2024-11-01 Time: 18:42:40 Timezone: +01:00 Unfortunately, the date and time format lists in our Format > Cells... dialog do not offer any items involving timezones. While I suppose one can specify them as a custom format - users would need to know how to do so (if it's at all possible) - and they would not be able to avail themselves of any examples in the dialog. We need to add a few formats involving timeszones, at the very least those for full combined date and time specifications, to the list in the dialog. As these are standard formats (if not _the_ standard format), not having any of them should be considered as a bug.
UX Team -- please take a look at this enhancement. Thanks!
The timezone is a relative information that needs additional data about the offset. I don't see how this should work.
Right, internally a date+time is just a floating point number expressing the serial offset from the null date (1899-12-30T00:00:00). It can not carry any timezone information whatsoever. Displaying timezones or accepting them in input would only be possible if the date+time type and its calculation was changed, and timezone information be discarded when storing to alien file formats like Excel's.
(In reply to Heiko Tietze from comment #2) > The timezone is a relative information that needs additional data about the > offset. I don't see how this should work. The timezone _is_ the offset. Look at my example. The user enters year, month, day, hour, minute, second, and TZ offset. This is a way to specify time, including the timezone - and it's an ISO standard. We just refuse to acknowledge it and display it. But - what if the user didn't specify the timezone, but the format calls for a timezone? Well, in that case, we use some sort of fallback. It could be UTC; or it could be the default zone for locale LC_TIME; or it could be the value in /etc/timezone - whatever. This is not problematic - it's just like when we don't have the date, only time information, we implictly choose January 1st, 1900 (but don't show it).
(In reply to Eike Rathke from comment #3) > Right, internally a date+time is just a floating point number expressing the > serial offset from the null date (1899-12-30T00:00:00). It can not carry any > timezone information whatsoever. Ah, so it seems it's an ODF compliance bug! ODF 1.2 §18.3.14 already requires support for timezoned values (based on XML Schema 2). > Displaying timezones or accepting them in > input would only be possible if the date+time type and its calculation was > changed, and timezone information be discarded when storing to alien file > formats like Excel's. Fair enough. It wouldn't be the only case of us having to discard information because Excel doesn't support some ODF feature... In fact, if we add this capability - it's something we can tout in marketing, an improvement over MS Office :-)
TZ is a second information, period. You may ask to read and interpret this string or to convert a datetime value into something with TZ information but not to modify/add format. This is just invalid.
(In reply to Heiko Tietze from comment #6) > TZ is a second information, period. What is "second information"? > You may ask to read and interpret this > string or to convert a datetime value into something with TZ information but > not to modify/add format. I don't understand this sentence either. TZ info is part of the information about a date and time, both materially and formally. There must be a way to present the TZ information when formatting date and time. You can't will TZ info out of existence because LO has not taken them into account so far.
(In reply to Eyal Rozenberg from comment #7) > What is "second information"? =now() - (1/24) (for GMT, given I'm on UTC+1) > I don't understand this sentence either. You talk about a string while datetime is a floating number with integer for the date since the dawn of time and a fractional part for 24h. This number has no imaginary part for the offset. => INV
(In reply to Heiko Tietze from comment #8) > (In reply to Eyal Rozenberg from comment #7) > > What is "second information"? > =now() - (1/24) (for GMT, given I'm on UTC+1) Sorry for being a bit dense, but I still don't understand what you meant to say in this sentence. > You talk about a string while datetime is a floating number with integer for > the date since the dawn of time There is nothing in the semantics of date-and-time which involves limited precision. So it's certainly not floating-point anything (except perhaps as an implementation detail). But I understand the desire to keep the semantics of just-a-number. And yet - people need, and the ODF demands, the ability to write dates in ODF documents in ISO timezoned format, to have it recognized, and to maintain it when saving & loading. (Ok, the maintaining it is not an ODF demand as such, it just stands to reason for an ODF editing application like LibreOffice). So what can we do?
(In reply to Eyal Rozenberg from comment #9) > So what can we do? In order to make =now() appear as 2024-11-15T14:38:20+01:00 you can change the number format to JJJJ-MM-TT"T"HH:MM:SS+"01:00".
(In reply to Heiko Tietze from comment #10) > In order to make =now() appear as 2024-11-15T14:38:20+01:00 you can change > the number format to JJJJ-MM-TT"T"HH:MM:SS+"01:00". What can we do so that we can, say: 1. Feed Calc an ODT file with cell A1 containing 2024-11-01T18:42:40+01:00 and cell A2 containing 2024-11-01T17:42:40+02:00 2. See those strings on the sheet 3. Enter a formula =A2-A1 into cell A3 4. Get a value of 0 in A3 5. Save the ODT file 6. Still see 2024-11-01T18:42:40+01:00 in A1 and 2024-11-01T17:42:40+02:00 in A2 after the save
Updating status to New due to reporter providing more information.
(In reply to Nicole A. from comment #12) > Updating status to New due to reporter providing more information. To mark this kind of bug as NEW, there should be wide enough agreement that it is a bug, or a missing feature / worthwhile enhancement. As as you can tell - that is not yet the case. So, it's UNCONFIRMED for now.
(In reply to Eyal Rozenberg from comment #11) > ...cell A1 containing 2024-11-01T18:42:40+01:00 Please share an example where this datetime format is valid and accepted. You probably have Excel in mind.
AFAIK also Excel does not support timezones in numeric date+time cells.
(In reply to Heiko Tietze from comment #14) > (In reply to Eyal Rozenberg from comment #11) > > ...cell A1 containing 2024-11-01T18:42:40+01:00 > Please share an example where this datetime format is valid and accepted. ISO 8601: https://en.wikipedia.org/wiki/ISO_8601 and consequently, ODT 1.2 §18.3.4 dateOrDateTime http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#__RefHeading__1416564_253892949 (which links to: https://www.w3.org/TR/2004/REC-xmlschema-2-20041028/ §3.2.7 dateTime) so, this is a valid and accepted format in ODF files.
In spreadsheet context, ODF Formula (ODFF) v1.3 (and please stop citing outdated ODF 1.2, though in this case information is the same) defines 4.3.4 DateTime DateTime is a subtype of Number. It is a Date plus Time. https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__74713_1363921367 There is no way to express and transport timezone information in a subtype of Number.
(In reply to Eike Rathke from comment #17) > In spreadsheet context, ODF Formula (ODFF) v1.3 We're not talking about "ODF Formula", but ODF generally. > (and please stop citing > outdated ODF 1.2, though in this case information is the same) I cited ODF 1.2 to show that the ODF support for timezones in datetime values is some recent novelty but has been around for quite a while. ODF 1.4 defines the same thing, in the same place: https://docs.oasis-open.org/office/OpenDocument/v1.4/csd01/part3-schema/OpenDocument-v1.4-csd01-part3-schema.html#__RefHeading__1416564_253892949 > §18.3.14 dateOrDateTime > > A dateOrDateTime value is either an ... date value or an > [XMLSchema-2](https://www.w3.org/TR/2004/REC-xmlschema-2-20041028/#dateTime) > dateTime value. And an XML2 dateTime value is > §3.2.7.1 Lexical representation > > ... finite-length sequences of characters of the form: > > '-'? yyyy '-' mm '-' dd 'T' hh ':' mm ':' ss ('.' s+)? (zzzzzz)? > and there is a bunch of additional text about timezones. > There is no way to express and transport timezone information in a subtype > of Number. A datetime is not a subtype of number. To quote the XML schema document again, > 3.2.7 dateTime > [Definition:] dateTime values may be viewed as objects with integer-valued > year, month, day, hour and minute properties, a decimal-valued second > property, and a boolean timezoned property. Each such object also has one > decimal-valued method or computed property, timeOnTimeline, whose value is > always a decimal number; the values are dimensioned in seconds, the integer > 0 is 0001-01-01T00:00:00 and the value of timeOnTimeline for other dateTime > values is computed using the Gregorian algorithm as modified for > leap-seconds. The timeOnTimeline values form two related "timelines", one > for timezoned values and one for non-timezoned values. Each timeline is a > copy of the ·value space· of decimal, with integers given units of seconds.
I'm just explaining why currently it is not possible to have timezones in date+time values. You can insist on a theoretical view that timezones are defined in XML 2, but that doesn't change anything.
(In reply to Eike Rathke from comment #19) > I'm just explaining why currently it is not possible to have timezones in > date+time values. I understand the the current implementation does not support it. But Heiko asked me to "share an example where this datetime format is valid and accepted." - so I've answered: It is a valid and accepted format in ODF files (and ODS files specifically). > You can insist on a theoretical view that timezones are defined in XML 2, > but that doesn't change anything. It is not a theoretical view, it is the ODF spec, which LibreOffice should support. You're right in the sense that this doesn't change the way things are right now, and that introducing this support might be problematic/difficult. It is still a valid expectation to be able to use this datetime format in Calc.
(In reply to Eyal Rozenberg from comment #20) > I understand the the current implementation does not support it. But Heiko > asked me to "share an example where this datetime format is valid and > accepted." Well, I meant an example document.