Adjusting toDate(now()) for non-GMT time zone

Question

I know that, according to this, now() gives datetime in GMT. I want to get the date from that with toDate(), but everything in my sheet and what I'm comparing to it is in EDT, so I need to subtract 4 hours from GMT.

I can't seem to figure out how to subtract 4 hours from now() before grabbing the date then comparing.

The query is basically

=QUERY('Sheet1'!A1:Z,"select A,B,C,D,E,F,G,H where D matches 'Some Text' and A >= toDate(now())",1)

Answer 1

First be sure that the spreadsheet timezone setting is EDT: File > Spreadsheet settings...

Then you could use the built-in function NOW(). In contrast with the scalar function now() of Query Language, it returns a date-time value in the timezone of the spreadsheet.

Then use TEXT(NOW(),"yyyy-mm-dd") to get the date on the required format and use it after the date keyword.

The final formula is

=QUERY('Sheet1'!A1:Z,"select A,B,C,D,E,F,G,H where D matches 'Some Text' and A >= date '"&TEXT(NOW(),"yyyy-mm-dd")&"'",1)