Description: I want to produce an hourly timeseries of date in the format YYYY-MM-DD HH:MM:SS. I've tried too ways to do this and both are unreliable. At some point the next cell displays 59m and 59s added instead of 60m. Steps to Reproduce: Scenario 1. 1. enter "2018-01-05 00:00:00" in a cell (without quotes) 2. enter "2018-01-05 01:00:00" in the cell below 3. Select both cells then do a drag fill downward. Scenario 2. 1. enter "2018-01-05 00:00:00" in a cell (Say A1) 2. in the cell below (A2) enter a formula "=A1+TIMEVALUE("01:00")" 3. copy A2 and paste to a to a few tens of cells below Actual Results: In some cases the value is displayed not 60m incremented but rather 59m and 59s. Expected Results: I'd expect a nicely incrementing timeseries with hour steps for both scenarios. Reproducible: Sometimes User Profile Reset: No Additional Info: [Information automatically included from LibreOffice] Locale: en-GB Module: SpreadsheetDocument [Information guessed from browser] OS: Windows (All) OS is 64bit: no
Created attachment 173069 [details] Screen cap
Created attachment 173070 [details] Offending sheet
I've tried generating a series in reverse and this seems to work more reliably
Created attachment 173071 [details] Updated the offending sheet
This is not a bug, but inevitable limitation of hardware calculations. Time in LibreOffice is internally represented as a fraction of a day; 1 hour is 1/12 = 0.041666666666666... (not representable precisely in machine floating-point format). When you create series like you did, you always introduce inaccuracies: 1. When filling the two initial values and dragging, you want Calc to count the difference between the two initial values, and use it as increment. You have two dates with times, having values 43105 and 43105,0416666667. Their difference is 0,041666666656965, not 0.041666666666666, because only ~16 decimal digits fit into the machine number. Adding this value incrementally naturally starts to deviate very quickly. 2. When you use =A1+TIMEVALUE("01:00") formula, the additional inaccuracies result because of accumulating error from repeated summation. The most precise (but not perfect!) calculation could be using the formula =A$1+TIMEVALUE("01:00")*(ROW()-1), where all intermediate calculations do not affect result in following cells. The same result you would get, if you use Sheet->Fill Cells->Fill Series (and put 2018-01-05 00:00:00 to "Start value", and 01:00:00 to "Increment"). The only drawback of using Fill Series in this case is that it will need applying Datetime format manually after the operation. See also FAQ: https://wiki.documentfoundation.org/Faq/Calc/Accuracy
(In reply to Mike Kaganski from comment #5) > 1 hour is 1/12 = 0.041666666666666... Of course, 1 hour is 1/24; I provided correct decimal value, but made a thinko typing the symbolical value.
Thanks Mike. I figured it was something like that. It seems that libre office should really be storing date/time in a more standard format like number of seconds since the epoch. Storing fractions of a day seems ripe for all sort of issues... I guess it would be a horrible backward compatibility issue though. It would be interesting to know why the original design decision went this way.
(In reply to Felix Collins from comment #7) > I guess it would be a horrible backward compatibility issue though. True. There are billions of spreadsheets that do calculations like "DATEVALUE("2021-06-22")+1/1440" to mean "add 1 minute to the date", or add 1 to add 1 day, etc. > It would be interesting to know why the original design decision went this way. I suppose that back in the day, in 1980s, computers didn't have a reasonable integer type capable of holding such numbers like *milli*seconds (the minimal reasonable resolution for time in spreadsheets, since people often need sub-second precision) since epoch - even seconds already approach unsigned 32-bit limit, and spreadsheets started even before 32-bit era.