Skip to main content

How to handle date and time?

Under the hood, Superintendent loads CSVs into a SQLite database. Every column is interpreted as string.

SQLite has a limited set of date and time functions.

In addition, Superintendent extends its functionality by offering date_parse(...) (see: doc), so it's more convenient to handle date and time.

TIP: The YYYY-MM-DD HH:mm:ss datetime format (e.g 2020-05-01 11:30:59.123) is the easiest form to work with because it's robust and sortable.

It will make your SQL simpler if you can convert any date and time to the above format before adding the CSV to Superintendent.

Example: Filter and sort by MM/DD/YYYY#

Let's assume there's a CSV that contains dates whose format is MM/DD/YYYY as shown below:

namebirthdate
John3/5/1985
Jane12/30/1984
Jack6/17/1985
Jill10/11/1984

We would like to query for people whose birthdate is after 1 Jan 1985 and sort the result in the ascending order.

We can utilize date_parse(...) (see: doc) to parse the dates as shown below:

SELECT name, date_parse('%m/%d/%Y', birthdate) as birthdate FROM table

The above SQL gives the below results:

namebirthdate
John1985-03-05
Jane1984-12-30
Jack1985-06-17
Jill1984-10-11

With the above result, we can filter the table easily with birthdate >= '1985-01-01 (string is sortable!) as shown below:

SELECT
*
FROM
query_1
WHERE
birthdate >= '1985-01-01'
ORDER BY
birthdate ASC

The above SQL gives the below results:

namebirthdate
John1985-03-05
Jack1985-06-17

And there we go! We've got the result that we want.

You can read more about date_parse(...) here: doc