SQLite timestamps with floating point seconds#

Today I learned about this:

1
select strftime('%Y-%m-%dT%H:%M:%f')

Which outputs:

1
2024-03-14T04:23:25.087Z

Note the seconds component which reads 25.087 - that’s what you get from the %f format string.

This is useful because it provides a string which captures timestamp information at the millisecond level but can still be sorted alphabetically to sort by date.

I spotted this in the SQL schema for goqite by Markus Wüstenberg, who uses it for recording created and updated timestamps:

1
create table goqite (
2
  id text primary key default ('m_' || lower(hex(randomblob(16)))),
3
  created text not null default (strftime('%Y-%m-%dT%H:%M:%fZ')),
4
  updated text not null default (strftime('%Y-%m-%dT%H:%M:%fZ')),
5
  queue text not null,
6
  body blob not null,
7
  timeout text not null default (strftime('%Y-%m-%dT%H:%M:%fZ')),
8
  received integer not null default 0
9
) strict;
10

11
create trigger goqite_updated_timestamp after update on goqite begin
12
  update goqite set updated = strftime('%Y-%m-%dT%H:%M:%fZ') where id = old.id;
13
end;

Another neat trick in that schema:

1
select lower(hex(randomblob(16)))

Which returns random strings like this one, suitable for use as IDs:

1
b4496695399120dfa999bff9981467b1