Sqlite datatypes

Documentation

sqlite datatypes	https://sqlite.org/datatype3.html
sqlite datehelpers	https://www.sqlite.org/lang_datefunc.html

Basic Types

NULL     # null
TEXT     # a string
INTEGER  # integer (no bool!)
REAL     # floating point number
BLOB     # blob of binary data, stored exactly

Dates

SQLite does not have a type for datetime objects.
There are helper functions available to help format dates.

https://www.sqlite.org/lang_datefunc.html

SELECT datetime(1092941466, 'unixepoch', 'localtime');  # parse a unix timestamp, and present in local timezone
#> 2004-08-19 14:51:06

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');  # get timedelta (in seconds)
#> 2004-01-01 02:34:56

SELECT DATETIME( '2018-03-29T21:31:13.472158+00:00' )
#> 2018-03-29 21:31:13

UUID

SQLite does not have a type for UUIDs.
The most compact way of storing them is as a 16-byte binary BLOB.
This is how databases like mysql treat UUID types under the hood.
See https://wtanaka.com/node/8106

import uuid

# to/from 16-byte binary
id = uuid.uuid4()
id.bytes                   # to 16-byte binary
uuid.UUID(bytes=id.bytes)  # from 16-byte binary

# save to sqlite
conn.execute('INSERT INTO table (uuid_blob) VALUES (?)', (id.bytes,))