A Google Apps Script, function, or external application can fill cells in Google Docs Sheets with DateTime (timestamp). My topic is not about inserting timestamps. Let’s learn how to handle DateTime in Query in Google Sheets. The custom function onEdit and the built-in NOW() are the two functions that insert DateTime in Google Sheets. Also, If you have connected Google Docs Forms with Google Sheets, you can see that the first column in your connected Spreadsheet is filled with timestamp/DateTime values. Here, we will learn how to use the DateTime keyword in Google Sheets Query. I mean, filter, group, and format a timestamp column.
How to Use DateTime in Query Where Clause in Google Sheets
Before going to learn the use of DateTime in the Query Where clause, you must know about the Query language element called Literals. Literals are values used for comparisons/assignments. For date/time types of literals, there are three keywords: date, timeofday, and datetime or timestamp. That means, to compare a column that contains date and time, you can use the keyword datetime or timestamp as below.
Examples of the Use of DateTime in Query in Google Sheets:
Formula when column A contains timestamp/DateTime.
=query(A1:D,"select A,B,C,D where A>= timestamp '2019-1-11 12:00:00'",1)
In the above filter, column A contains the timestamp/DateTime. Below is an alternative Query.
=query(A1:D,"select A,B,C,D where A>= datetime '2019-1-11 12:00:00'",1)
Both formulas would return the same result.
DateTime Criterion as Cell Reference in Query
Please refer to cell A2 in the above image. How to use that value as the criterion? The following example shows how to specify a timestamp as a cell reference in the Where clause in Google Sheets Query.
DateTime Criterion as Cell Reference:
=query(A1:D,"select A,B,C,D where A>= datetime '"&TEXT(A1,"yyyy-mm-dd HH:mm:ss")&"'",1)
Again, you can replace the keyword datetime in the formula with the timestamp keyword.
How to Format DateTime in Query Output
When you format a DateTime column using the Query format clause, it won’t affect the actual DateTime, or we can say, the underlying value in the cell. It helps us to display the timestamp in date format while keeping the value intact.
Similar: How to Format Date, Time, and Number in Google Sheets Query.
The Use of DateTime in Format Clause:
=query(A1:D,"select A,B,C,D where A>= datetime '"&TEXT(F1,"yyyy-mm-dd HH:mm:ss")&"' format A 'DD-MM-YY'",1)
See the value on the formula bar. It is not a date in DD-MM-YY as displayed in column F. It contains time values. This formula masks the time elements with formatting. Again the underlying value in the cells will be the same.
=query(A1:D,"select A,B,C,D where A>= datetime '"&TEXT(F1,"yyyy-mm-dd HH:mm:ss")&"' format A 'DD-MM-YY HH:MM'",1)
DateTime in Query Group By Clause in Google Sheets
For this example, I am not again posting the screenshot. See the columns A, B, C, and D in the first screenshot of this tutorial. In that, the following formula will group the data based on DateTime in column A and then by the Product in column B.
=query(A1:D,"select A,B, Sum(C) where A is not null group by A,B")
Take a look at the DateTime in column F in the above result. It won’t be the output that you were expecting. Because it doesn’t group by dates. So, you may want to exclude the time value in the grouping. The solution is to use the toDate scalar function both in the Select and Where clauses as below.
=query(A1:D,"select todate(A),B, Sum(C) where A is not null group by todate(A),B")
That’s all. Thanks for the stay. Enjoy!
For more Google Sheets tips and tricks, visit Crawlan.com!
