incorrect handling of UTC date/time constants
Expected and desired behavior:
Consider the following script. It calculates the current time via the now() spreadsheet function, and compares that to six seemingly-reasonable constants that should represent the same thing.
#! /bin/bash
export TZ=EST
: ${GG:=gnumeric}
(
echo ,,with T,,,with space
echo 'now,=c2-$c$2,=now()'
echo 'local,=c3-$c$2,'$(date +"%FT%T")',,=f3-$c$2,'$(date +"%F %T")
echo 'UTC Z,=c4-$c$2,'$(date -u +"%FT%TZ")',,=f4-$c$2,'$(date -u +"%F %TZ")
echo 'UTC 00,=c5-$c$2,'$(date -u -Iseconds)',,=f4-$c$2,'$(date -u +"%F %T+00:00")
) | $GG fd://0
I would expect the result to look like this, with lots of zeros in columns B and E:
with T with space
now 0 44117.47195601852
local 0 2020-10-13T11:16:05 0 2020-Oct-13 11:16:05
UTC Z 0 2020-10-13T16:16:05Z 0 2020-10-13 16:16:05Z
UTC 00 0 2020-10-13T16:16:05+00:00 0 2020-10-13 16:16:05+00:00
Observed behavior:
Instead I observe this:
with T with space
now 0 44117.4740625
local #VALUE! 2020-10-13T11:22:39 0 2020-Oct-13 11:22:39
UTC Z 0.208 2020-10-13T16:22:39Z #VALUE! 2020-10-13 16:22:39Z
UTC 00 #VALUE! 2020-10-13T16:22:39+00:00 #VALUE! 2020-10-13 16:22:39+00:00
The most important thing to note is that the ISO date constant 2020-10-13T16:22:39Z is interpreted incorrectly. It should be synonymous with now(). It is wrong by exactly 5 hours. Evidently the explicit timezone (Z) is butchered and the local timezone is used instead.
To say the same thing another way: AFAICT the numerical value of the date/timestamp is always in the local timezone. That's OK, so long as it is consistent. Meanwhile, when somebody puts a date/time string in a .csv file, when they say UTC they mean UTC. When such a string is read in, it must be converted to the internal representation correctly. The current behavior is to give it a custom format that makes it look OK so long as you don't do any calculations with it, which just rubs salt in the wound, by concealing the error. To repeat: It is important that the numerical representation be correct. All six of the constants in this example should have the same numerical representation.
The second thing to notice is that four of the six constants are not handled at all; rather than being interpreted as dates they are left as uninterpreted strings. It is particularly weird that when the Z is present the time must be preceded by T, whereas when the Z is absent the time must be preceded by space. This seems inconsistent and unnecessarily strict. Also it would be super-easy to recognize timezone offsets in the form ±hh:mm.