SQLite

We use SQLite3 as the database engine when you query CSVs, Sheets, and other Blocks. Here are a few tips for working with SQLite:

  • Most of the columns will come through as TEXT by default. If one of the columns is an INTEGER you can use CAST to convert it, for example:

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. Since the dates are TEXT, you can truncate dates using SUBSTR. For example, you can convert a timestamp to a month:

substr('2020-05-21 23:22', 1, 7) as created_on
-- 2020-05

  • To get the first and last day of the month:

SELECT
    DATE('now',
        'start of month',
        '+1 month',
        '-1 day');
Was this page helpful?
×