Time to dust off this old blog again.. I’ve been using the brilliantly light-weight SQLite 3 database on several different projects lately, but there is always some tricks I easily forget. To avoid wasting time with the documentation I’ll write them down here.
So far there is only one:
Unix time to human readable time:
SELECT datetime(1361865600, 'unixepoch'); 2013-02-26 08:00:00
Unix time utility site: http://www.epochconverter.com/
Resetting autoincrement columns / fields
SQLite maintains the current largest counter value in the special SQLITE_SEQUENCE table. So really all you have to do is set the “seq” column to zero on the row for your table in SQLITE_SEQUENCE.
E.g. deleting all rows and resetting the counter value:
DELETE FROM MyTable; UPDATE SQLITE_SEQUENCE SET seq = 0 WHERE name = 'MyTable';