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';

Advertisements