Created attachment 147207 [details] 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'); ```
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?
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.
I've had a second look at the underlying content.xml file of the .ODS that I've attached to my first ticket. The timezone information is included as "Z" (Zulu/UTC time) in the office:value-type="date" attribute, then also as text in the <text:p> field: <table:table-cell office:value-type="date" office:date-value="2018-11-30T23:30:00.000Z" table:style-name="ce1"><text:p>2018-11-30T23:30:00.000Z</text:p></table:table-cell> Should the "Z" time zone specifier be respected when displaying the cell's value in a different timezone? In regards to this comment, > Specifically one does not want to see different times just because viewing data someone entered in a different time zone How about this use case: A business person living in the US has customers in the US, and an outsourced contractor in Asia who takes support calls. The contract logs each call with its timestamp and a brief summary. Due to the timezone difference, the local times in Asia corresponding to business hours in the US will tend to be during the US night. That doesn't make sense. The US person will want to see the time in the US timezone when the contractor answered the support call.
Created attachment 148321 [details] The timezone is expressed as "Z" in content.xml
*** Bug 121781 has been marked as a duplicate of this bug. ***
I suspect this would turn into a nightmare to maintain, because time zones and daylight savings time date changes are subject to constant change on political whim. Expecting these events all to be detected, and finding a way to implement the updates automatically would be no trivial task. I think the onus has to remain with the spreadsheet designer to record the time zone in a separate cell, or to find a way to use GMT (or a designated local time) as a standard where event sequence is important. Where contracts are concerned, these will specify the time zone to be used if it is critical e.g. "by 17:00 hours London time". Where collaborative editing is involved, the edits will presumably be timestamped by the hosting server.
I'm a developer, albeit not a Java one, so maybe I'm wrong. Why would it be so complicated to store timestamps in UTC? I've already shown in my previous comment that date fields already have a 'Z' tacked onto them. Is that used? If not, how could it be used? Can Calc convert timestamps from the local timezone into UTC on save, and form UTC into the local timezone on load? Both these conversions should be provided by the OS or the runtime, so there should be no reason to mess with timezones in Calc code. https://stackoverflow.com/questions/19431234/converting-between-java-time-localdatetime-and-java-util-date
Not sure that works. Consider a timestamp inserted the day before a change from daylight savings time to standard time, viewed the day after. Consider a timestamp made at 01:45 the day that the time changes: which time is it in? what happens if the file is saved later at 01:01 new time (02:01 old time) with the time having gone back an hour?
Everyone talking about a maybe theoretically possible time zone support, please be aware that in spreadsheet context a date+time value is one single numeric floating point value in days since null date (with 0.5 == 12h ...), it does not hold any time zone information whatsoever. Please see comment 2. This is completely different from what *could* be possible in the file format where an ISO 8601 date+time string is stored (at least for ODF and optionally for OOXML, the old binary .xls file format doesn't allow any such thing).
Also hit by this bug. I use Calc to record brokerage transactions. These are executed in the time zone of the New York Stock Exchange (UTC-5). My broker sends a list of transactions in the NYSE time zone, and I import it in Calc. Because I live in Denver, Colorado (UTC-7), all transactions are two hours off.
Check out this site : https://timely.is/
To begin supporting time zones, it would be necessary to first support them in the internal structures. I have been exploring how I can insert the current UTC time into a spreadsheet. The Basic expression "Now" returns a value of type "Variant/Date" which shows no time zone information in the Basic debugger. I tried "CDateToUnoDateTime(Now)" which returns a struct with a "isUTC" field. Since this is a boolean, it is not sufficient for me convert to UTC myself. The IDL file is here: https://github.com/LibreOffice/core/blob/master/offapi/com/sun/star/util/DateTime.idl Irregardless of what the file formats support, it would be nice to at least have an enriched UTCDateTime type available in Basic. Right now, I am resorting to calling into Python just to get the UTC time, which seems a bit ridiculous.