Home > Software engineering >  Unable to figure out datetime query in Google Sheet
Unable to figure out datetime query in Google Sheet

Time:09-02

Hi Im unable to figure out whats wrong with my datetime query =QUERY(A:G,"SELECT * WHERE G < datetime '2022-08-24 08:45:00'",1) in my Google Sheet it should return 2 rows on the left whos datetimes in G are < 2022-08-24 08:45:00 (also supplied screenshot) enter image description here

CodePudding user response:

should be:

=QUERY(A:G, "WHERE G < datetime '2022-08-24 08:45:00'", 1)

enter image description here

yyyy-mm-dd hh:mm:ss is the correct format - enter image description here

CodePudding user response:

I have made changes to formula to your G column =DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2)) (TIME(C2,D2,0)) to make it pure datetime value. Then use below query formula.

=QUERY(A:G,"SELECT * WHERE G < datetime '2022-08-24 08:45:00'",1)

See your google sheet harun24hr.

CodePudding user response:

Suggestion:

Since the QUERY() function runs a Google Visualization API Query Language query across data it runs slower than the usual formulas in google sheets. This will be very noticeable if you have large data. So another way is using the FILTER() function to filter only the date less than '2022-08-24 08:45:00'. This runs much faster, almost instantly. The only key difference here is you have to manually copy the headers and start on row 2.

You first have to convert your Column G to an actual Date & Time Format using:

=ARRAYFORMULA(IF(B2:B="", "", DATE(LEFT(B2:B,4),MID(B2:B,5,2),RIGHT(B2:B,2)) (TIME(C2:C,D2:D,0))))

enter image description here


Then using Filter:

=FILTER(A1:G, G1:G < DATE(2022,8,24) time(8,45,0))

Result:

Headers manually copied. enter image description here **Just added some test data to see that the filter works.

References:

  • Related