You can filter timestamp in Query in Google Sheets and also can use comparison operators with the filtering. Suppose my column A contains the timestamp and I want to filter this column between two dates and time, you can do it using the function Query.
In Google Sheets, you can use comparison operators in a column containing the timestamps without modifying the column content. Without modifying the column here means without splitting the timestamp to date and time.
You may only need to split the timestamp date and time if you want to summarise your data month, year or quarter wise.
Similar:Â Create Month Wise Summary in Google Sheets Using Query Formula
How to Filter Timestamp in Query in Google Sheets
Sample Data:
In this Google Sheets Query example, I want to filter the data in Column A and B based on the criteria in the cells D2 and E2.
In D2 and E2 you can see a start time and end time. Actually, it’s date and time, means a timestamp.
The Formula to Filter by Date and Time (timestamp) in Google Sheets
Here it is.
=query(A1:B,"Select A,B where A>= datetime '"&TEXT(D2,"yyyy-mm-dd HH:mm:ss")&"' and A<= datetime '"&TEXT(E2,"yyyy-mm-dd HH:mm:ss")&"'")
You can follow this formula when you want to filter by date and time in Query in Google Sheets.
Formula Explanation
In this formula, that filter Timestamp in Query in Google Sheets, the Cells D2 (start date and time), and E2 (end date and time) contain the criteria.
To use a timestamp as Criteria in Query, you should convert this timestamp (criteria) to text format. The formatting should be as follows.
The year should be in the format,YYYY-MM-DD and the time in the formatHH:mm:ss.
The below formula adhere to this rule and that is what I’ve followed in the above Query filter formula.
=TEXT(D2,"yyyy-mm-dd HH:mm:ss")
Since it’s a text, follow the normal method that you use when you want to refer to a cell containing text in Query as criteria. That is as follows.
B='"&D2&"'"
Here the cell address D2 is replaced by the above text formula. To identify this text formula as a date and time value, in the Query formula use the keyword “DateTime” before it.
datetime '"&TEXT(D2,"yyyy-mm-dd HH:mm:ss")&"'
Hope this makes sense. Those who are looking for more details about how to use the date as the criterion in Query, please follow my below tutorial.
How to Use Date Criteria in Query Function in Google Sheets [Date in Where Clause]
That’s all about how to filter Timestamp in Query in Google Sheets. Enjoy!
