This draft documentation may be incomplete or inaccurate, and is subject to change until this release is generally available (GA).

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?
×