TIL: TIMESTAMP in sqlite

I was browsing documentation for a small project I was working on today, and happened to notice something I had overlooked previously. Sqlite allows you to automatically create a timestamp when you insert a record. You define the column like so:

CREATE TABLE foo(timestamp TEXT DEFAULT CURRENT_TIMSTAMP, ...)

A database with a built in timestamp function is hardly surprising. But Sqlite is more bare bones than other RDBMSs in this respect. There are no date/time data types, only strings (or integers if you are working with Unix timestamps).

Some older examples will declare the “type” of a column as TIMESTAMP. As far as I can tell this dates back to sqlite2, which was even looser about the types of columns. It still works, but given that the only reference to it in the documentation is in the “Obsolete Documents” section, I feel leery of doing it in new code.