CODEWITHSUNDEEP
google-sheets-formula
Andrew Learned
Andrew Learned

Reputation: 11

Google Sheet Query: Order by DateTime

I am trying to combine multiple sheets and order by a DateTime column (col10). The formula I have is ordering by the date portion of the column, but the times seem to be ignored.

This is what I have: =QUERY({Sheet1; Sheet2; Sheet3}, "select * where Col9 < 5 and Col1 is not null order by Col10 desc limit 1000")

It orders by date correctly, but ignores the time.

I tried this, but get errors: =QUERY({Sheet1; Sheet2; Sheet3}, "SELECT * WHERE Col9 < 5 AND Col1 IS NOT NULL ORDER BY datetime text(Col10, 'M/D/YYYY HH:MM:SS') DESC LIMIT 1000")

Here's three values from the result (in the returned order) so you can see it's sorting by the date, but not the time:

3/6/2023 9:50:18 3/6/2023 9:39:14 3/6/2023 9:18:00

BONUS: Eventually I want to ignore any subsequent duplications of the same Date/Time, but that's a problem for another day...

Upvotes: 0

Views: 477

Answers (1)

Andrew Learned
Andrew Learned

Reputation: 11

Got it working! Hope this helps someone:

=QUERY(
    SORT({
      'Logz: App Added'!A:I,ARRAYFORMULA(DATEVALUE('Logz: App Added'!J:J)+TIMEVALUE('Logz: App Added'!J:J));
      'Logz Extract'!A2:I,ARRAYFORMULA(DATEVALUE('Logz Extract'!J2:J)+TIMEVALUE('Logz Extract'!J2:J));
      IMPORTRANGE("xxx", "Logz!A2:I"),
      ARRAYFORMULA(DATEVALUE(IMPORTRANGE("xxx", "Logz!J2:J"))+TIMEVALUE(IMPORTRANGE("xxx", "Logz!J2:J")))
    }, 2, 0),
    "SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10 
     WHERE Col9 < 5 AND Col1 IS NOT NULL 
     ORDER BY Col10 DESC 
     LIMIT 1000"
  )

Upvotes: 0

Related Questions