Google sheets - How to query a custom date range?

0
votes

Trying to work out how to query a custom date range within google sheets - I get it working with a standard date range (YYYY-MM-DD) but with the below being a custom range I don't know how to query custom dates & times

Ideally, I'm trying to query all the data between the start date and 3 hours of the start date

I tried to amend a working query with how the custom DateTime would look but it errors out 

=query(B6:B9, "select B where B => date '"&TEXT(B3,"yyyy-mm-ddThh:mm:ss.mmmZ")&"'  ",0)

Start Date  2019-04-01T09:32:07.148Z

Dates:  
    2019-04-01T10:35:01.152Z
    2019-05-01T09:42:27.200Z
    2019-04-01T12:32:27.250Z
    2019-07-11T13:32:07.148Z
1
google-sheets-query

1 Answers

0
votes

There is rather a lot wrong with your attempt, including:

  • => should be >=
  • date should be datetime
  • for a range (eg 3 hours) you need both an upper and a lower limit
  • when processing dates and times strings are not suitable inputs

Please convert your text strings (eg by moving what is in B3:B9 to say F6:F9) and instead in B3 (copied down to B6:B9):

=value(substitute(left(F3,23),"T"," "))

then try:

=query(B6:B12,"select B where B >= datetime '"&TEXT(B3-1/8,"yyyy-mm-dd hh:mm:ss.sss")&"' AND  B <= datetime '"&TEXT(B3+1/8,"yyyy-mm-dd hh:mm:ss.sss")&"' ",0)