Sqlite Cheatsheet

Table of contents

• Date
  • Sqlite date
    • Get month from date string
    • Get data between months
  • Export to CSV

---

Date

Sqlite date

I store date in sql in “YYYY-mm-dd” format, stored as a string. You can use sqlite to extract the year month or day.

strftime('%Y', '%m', '%d')

You can use any of them just as a variable.

Get month from date string

SELECT * FROM table WHERE strftime('%m', datefield) = '05'

Get data between months

SELECT * FROM bales WHERE (strftime("%m", date) between "09" and "10")

---

Export to CSV

To export a sqlite database to csv:

sqlite3 -header -csv balesDatabase.db "select * from bales;" > bales.csv

Or you can get more specific:

$ sqlite3 -header -csv balesDatabase.db "select * from bales where (strftime('%Y-%m-%d', date) between '2022-06-01' and '2023-05-31');" > bales2022.csv