2

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.

player0
  • 124,011
  • 12
  • 67
  • 124
kishkin
  • 5,152
  • 1
  • 26
  • 40
  • I found a workaround: I add an extra column and put =N(A2) etc in that column. Then I use the filter and query on that column instead of A. – Ingeborg Apr 01 '22 at 03:56

1 Answers1

3

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • Who cares... Do you mind giving a try with `QUERY` [here](https://stackoverflow.com/questions/61925303/google-sheets-arrayformula-for-maxifs/62369912#62369912). `VLOOKUP` won't find some dt in the original column. At least w/o additional modifications. – kishkin Jun 17 '20 at 21:52
  • hard to guess how and where you wanted to apply QUERY but I checked your proposed VLOOKUP solution and it works great on every row emulating the array of "MAXIF" – player0 Jun 18 '20 at 01:01
  • I wanted to use `QUERY({K:K, E:E}, "SELECT Col1, MAX(Col2) GROUP BY Col1", 1)` instead of `SORT({K2:K, E2:E}, 2, False)`. That would've given a smaller range of unique datetimes to search through using `VLOOKUP`. And I actually did, but luckily was not lazy at that moment and checked with the original OP's solution - found that issue as a result. – kishkin Jun 18 '20 at 09:33
  • 1
    Thanks for this explanation, it was driving me mad. I have a table with, among other data, a timestamp and an member-ID. In another sheet I use FILTER, MATCH and QUERY to get only the newest row of each member. Because of your explanation I stopped looking for why one member disappeared from the newest-table, and instead made an extra column in the original table: =N(A2) where column A is the timestamp. – Ingeborg Apr 01 '22 at 04:01