Hi,
i have timestamps like:
1970-01-01T00:00:45.890622+00:00
1970-01-01T00:00:45.891422+00:00
and I need to find the difference between them, in this example, I would be looking for something like 0.008000.
How do I do this?
I’ve already adjusted the format of my cells to YYYY-MM-DDTHH:MM:SS.000000+00:00,
As far as I understand, we only import fractions of seconds with precision of 0.001 s (I hope @erAck would correct me), which is ~0.000000011574074 of a day - and I don’t think it’s possible to increase the precision, unless we start using some larger floats than double. So, in your case, given that you have imported the timestamps as true datetimes (not texts, regardless of cell format), your both cells would contain the same value of 01.01.70 00:00:45,89100001612678200000 (which is the representation that results from keeping dates and times as floating-point values based on day unit). And subtracting them results in 0.
subtracting them actually gives me #VALUE, not 0
That’s because it’s text.
After splitting off the UTC relation the two date-time stamps are converted to numeric representation.
The values shown based on the format string YYYY-MM-DD"T"HH:MM:SS.000000 are then exactly as given by the OP.
The difference is calculated to 9.26 E-9 d = 0.8 ms.
For a day in 1970 the absolute resolution of ‘Double’ should be about 1/2 µs.
(Am I correct with a relative resolution of 2^-52 which is about 2.22E-16 ?)
Ok; I only tested the import case - when you, e.g., select the two dates from the OP, and paste them as plain text, which gives you the import dialog, where you set the Date (YMD) to the column. In this case, I got the result I mentioned, which is what I supposed to be the import limitation. Of course, being a simple number, we can construct a date with any precision fitting into double, but I assumed that 0.001 is a reasonable precision for ~10000 years or so…
Just confirmed that import code (lcl_PutString() in sc/source/ui/docshell/impex.cxx) indeed only imports milliseconds (see nMilli assignment as fractional part multiplied by 1000 and rounded). This is true only for date+time input; if importing a column with times only, the “SetString / EditCell” code path is activated, and fractions of second import correctly. So to get the correct fractional part, one needs to split by T and +, and set date format for columns.
Thanks. I didn’t know (and don’t understand) the “tradition” in this point.
I also imported the strings via the dialog, but I only split by “+”. (ISO “T” is accepted by Calc.) and without “space merged” and “special numbers”. Thus I got texts with 4 leading spaces due to format of the OQ for the d-t-s.
When I removed the spaces automatic recognition returned what I reported.
Just tested again with the mentioned stettings and got directly numeric import. Result unchanged..
V6.1.3.2
To be clear: Also in the last mentioned case I imported without splittingg by “T”, and got date and time (with full precision) in one go as one numerical DateTime…
I just manually formattedthe strings in a text editor so that simple arithmetic was enough to figure out what I needed.
Given the comment to Mike’s answer, you do not have numeric date+time values in the cells but text instead. The reason is that the timestamp has the timezone appended, which is not supported to be converted to a numeric value (on purpose, because the timezone can’t be stored along after conversion to Number). Applying a number format to text doesn’t help (apart from that the trailing +00:00 in the format code is nonsense).
If you enter the values without timezone 1970-01-01T00:00:45.890622 and 1970-01-01T00:00:45.891422 and format them as YYYY-MM-DD HH:MM:SS.000000 you’ll see the values are maintained, and subtracting them results in 0.000800 if formatted as [S].000000