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 anINTEGER
you can useCAST
to convert it, e.g. - 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 withSUBSTR
. For example, you can convert a timestamp to a month: - Getting the first and last day of the month
CAST(text_column AS INTEGER) as integer_column
substr('2020-05-21 23:22', 1, 7) as created_on
-- 2020-05
SELECT
DATE('now',
'start of month',
'+1 month',
'-1 day');