CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
experimenter
experimenter

Reputation: 778

Unable to figure out datetime query in Google Sheet

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

Upvotes: 1

Views: 1073

Answers (3)

player0
player0

Reputation: 1

It 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 - https://developers.google.com/chart/interactive/docs/querylanguage#literals

And G2 can't be a text string so multiply it by 1:

=(TEXT(DATE(LEFT(B2,4),MID(B2,5,2),
 RIGHT(B2,2)),"yyyy-MM-dd")&" "&TEXT(TIME(C2,D2,0),"HH:mm:ss"))*1

But it's better if you use arrayformula:

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

enter image description here

Upvotes: 2

Logan
Logan

Reputation: 2140

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:

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 37155

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.

Upvotes: 1

Related Questions