[Libreoffice-bugs] [Bug 121851] New: Calc loses time zone information for datetime cells

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=121851

Bug ID: 121851
Summary: Calc loses time zone information for datetime cells
Product: LibreOffice
Version: 6.2.0.0.beta1+
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-***@lists.freedesktop.org
Reporter: ddascalescu+***@gmail.com

Created attachment 147207
--> https://bugs.documentfoundation.org/attachment.cgi?id=147207&action=edit
Whatever time zone you're in, you'll see the same time, 23:30 on Nov 30

In today's age with geographically distributed teams, and people traveling
frequently, time zone information is critical when sharing spreadsheets. If
accuracy is important, then if a person in San Francisco has an invoice like

"Order received", "2018-11-30 23:00:00"
"Job completed", "2018-12-01 10:00:00"

and sends it to someone in Berlin, the person in Berlin should see

"Order received", "2018-12-01 08:00:00"
"Job completed", "2018-12-01 19:00:00"

This is not what happens. The person in Berlin will see the same local times as
the person in San Francisco, which is confusing (I don't send orders at 11pm),
and simply incorrect. Moreover, dates may be off, as in the example above, and
for monthly billing processes, this can push items from one month to the next
to one party, while to the other they're in the same month.

1. Is this really acceptable for Calc? Do we assume users no longer send
spreadhseets, but use Google Sheets or equivalent online systems?
2. What about people who travel? If I have a sheet in which I log
time-sensitive incidents, or if such a sheet is generated by another system,
and I look at it from a different timezone, the timestamp information will be
incorrect.

I know Excel doesn't have the concept of universal time, but that was 20 years
ago. Should we try to improve the situation in LibreCalc?

To test, I've opened the attached file with LibreCalc 6.2dev on Ubuntu 16,
after changing time zones and restarting Calc. The time shown was always the
same, 2018-11-30 23:30:00.

The way I generated the file was with this Node.js code:

```js
const XLSX = require('xlsx');

const wb = XLSX.utils.book_new();

const ws = XLSX.utils.json_to_sheet([]);

XLSX.utils.book_append_sheet(wb, ws, 'Bug sheet');

// Dec 1, 00:30 in Berlin
// Nov 30, 23:30 in London
// Nov 30, 17:30 in New York
// Nov 13, 14:30 in San Francisco
ws.A1 = { t: 'd', v: new Date('2018-11-30T23:30:00Z') };

XLSX.writeFile(wb, 'date.ods');
```

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=121851

Roman Kuznetsov <***@mail.ru> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@mail.ru,
| |***@redhat.com

--- Comment #1 from Roman Kuznetsov <***@mail.ru> ---
In this case your date & time in cell is just some data and it will be saved
anyway.

This is not a bug, but may be it's an enhancement.

Eike, what do you think?

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=121851

Eike Rathke <***@redhat.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
Severity|normal |enhancement
Status|UNCONFIRMED |NEW
Ever confirmed|0 |1
Version|6.2.0.0.beta1+ |unspecified

--- Comment #2 from Eike Rathke <***@redhat.com> ---
There is no way time zone information can be stored at current datetime cells,
as a date+time value is just a date serial number with fraction as time (0.5 ==
12h). That is during runtime, ODF .ods is different. We would have to come up
with a new cell type for this that preserves time zone information, probably
plus number format codes to display in different time zones or a conversion
function.

It would be interesting how Excel treats datetime with time zone information.
ECMA L.2.16.9 Dates and Times says timezone can be stored, but gives no hint
what to do with it. Contrary to L.2.16.9.1 Date Conversion for Serial Values
that says it has no timezone information and represents UTC (which is quite
nonsense as any datetime value a user entered has no time zone information at
all, not even UTC, and mostly is the user's local time). Also 18.17.4
Dates and Times does not mention time zones at all.

As is, with date+time serial numbers we could only convert datetime with time
zone to an UTC datetime and throw the time zone away. But as this would discard
information and result in unexpected times we don't. It is also the reason why
entering an ISO 8601 string with time zone does not result in a numeric
datetime cell but content of type Text instead.

Specifically one does not want to see different times just because viewing data
someone entered in a different time zone, especially not if that time has no
time zone information.

As an interim solution we maybe could come up with a new datetime+timezone
function that parses an ISO 8601 string and converts it to any other time zone,
or converts to/from date+timme serial numbers without time zone information
with some option to specify whether UTC or (which) local time they represent.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=121851

Roman Kuznetsov <***@mail.ru> changed:

What |Removed |Added
----------------------------------------------------------------------------
Blocks| |107977


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=107977
[Bug 107977] [META] Calc feature enhancements