Ask Your Question

timestamp calculations [closed]

asked 2018-11-15 20:21:27 +0200

cerr gravatar image

updated 2018-11-16 00:23:10 +0200

erAck gravatar image


i have timestamps like:



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,

edit retag flag offensive reopen merge delete

Closed for the following reason duplicate question by cerr
close date 2018-11-16 20:02:19.834646

3 Answers

Sort by » oldest newest most voted

answered 2018-11-15 20:33:49 +0200

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.

edit flag offensive delete link more


subtracting them actually gives me #VALUE, not 0

cerr gravatar imagecerr ( 2018-11-15 21:22:38 +0200 )edit

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 ?)

Lupp gravatar imageLupp ( 2018-11-16 00:33:31 +0200 )edit

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...

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-16 06:38:29 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-16 08:38:43 +0200 )edit

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

Lupp gravatar imageLupp ( 2018-11-16 09:25:46 +0200 )edit

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..

Lupp gravatar imageLupp ( 2018-11-16 09:29:00 +0200 )edit

Here is a screencast:

Mike Kaganski gravatar imageMike Kaganski ( 2018-11-16 09:42:12 +0200 )edit

answered 2018-11-15 23:32:06 +0200

cerr gravatar image

I just manually formattedthe strings in a text editor so that simple arithmetic was enough to figure out what I needed.

edit flag offensive delete link more

answered 2018-11-16 00:06:31 +0200

erAck gravatar image

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

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2018-11-15 20:21:27 +0200

Seen: 274 times

Last updated: Nov 16 '18