# SQLite

## 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

```sql

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

```