SQLite

SQLite Connector Details

Overview

SQLite is a popular embedded SQL database engine. It is great s a portable lightweight analytics engine.

Date Behavior

SQLite does not have a native date/time types, yet ChartSQL expects dates to detect date/time series charts. The ChartSQL SQLite datasource connector will convert any columns that look like dates to a Java timestamp/datetime after executing the query.

For very large data sets this may add significant processing. Consider using a database like MySQL or Postgres that has native date types if visualizing a significant number of dates becomes necessary.

Common SQLite Query Tips

Month Floor

Use strftime('%Y-%m-01', DateClosed) to format a date to the floor of the month

SELECT
	strftime('%Y-%m-01', DateClosed) as MonthStart,
	sum(amount) as Sales
FROM sales
GROUP BY MonthStart
ORDER BY MonthStart ASC;