QUERY(...) slightly changes datetime values

user_279806 0
Viewed 51

I've encountered a problem where QUERY slightly changes some datetime values. Here goes a minimal reproducible example.

A sheet to play with.

  • A:A has datetime values.
  • B:B repeats them using =FILTER(A2:A, SEQUENCE(ROWS(A2:A), 1, 1, 0)).
  • C:C shows row numbers for any values that do not match the original. There is N/A, everything matches.
  • D:D repeats original datetimes with =QUERY(A2:A,,0).
  • E:E shows row numbers for any values that do not match the original. There is a few.
  • F:F shows the original values and G:G shows the values after the QUERY for the non-matching pairs. 10th digit after the dot is different
  • H:H shows the difference between F:F and G:G. Always the same: -0.000000000007276...

Why is that? Any way to remedy that? Maybe it is a bug in Google Sheets?

P.S.: the data is from another question (removed everything except datetimes). I had to use a FILTER solution in the answer despite a QUERY solution being simpler.

google-sheets-formula google-sheets
1 Answers

this behavior is caused by "floating error" of time values where FILTER works with values while QUERY tries to internally convert it to text string which cases that end / decimal positions of value are cut off - hance the difference between QUERY and FILTER. in sense of "accuracy" the FILTER is more "precise", tho in sense of common sense and casual logic - who cares about time values represented in formatting with 15+ decimal places which is way beyond millisecond scale...

user_56326290
Related