We use SQLite3 as the database engine when you query CSVs, Sheets, and other Blocks. Here's a few tips for working with SQLite
Pretty much all the columns will come thru as TEXT
by default. If one of the column is an INTEGER
you can use CAST
to convert it, e.g.
CAST(text_column AS INTEGER) as integer_column
SQLite does not have a storage class set aside for storing dates and/or times. Most dates will be converted to a TEXT
field.
SQLite does however have several functions to help manipulate dates and since it's just TEXT
, you can truncate dates with SUBSTR
. For example, you can convert a timestamp to a month:
substr('2020-05-21 23:22', 1, 7) as created_on
-- 2020-05
Getting the first and last day of the month
SELECT
DATE('now',
'start of month',
'+1 month',
'-1 day');