Adding per-row creation time and modification time in GeoPackage geometry table?

Question

I need to add ctime and mtime to a GPKG sqlite database in table with geometry.

Apparently logic solution: add ctime column with DEFAULT CURRENT_TIMESTAMP and add trigger ON UPDATE to set mtime = CURRENT_TIMESTAMP.

Problem: this works perfectly for non-geometric tables. With geometric tables QGIS complains "Warning 1: geometry column '[ctime|mtime]' of type 'TIMESTAMP' ignored" and moreover when I add a geometric feature using qgis the trigger on mtime seems ignored (probably due to gpkg driver)

Is there a correct way to perform this at db level?

Answer 1

I've just found that:

• if I use DATETIME as column type instead of TIMESTAMP
• I use (strftime('%Y-%m-%dT%H:%M:%fZ','now')) instead of CURRENT_TIMESTAMP
• I use triggers AFTER UPDATE and AFTER INSERT instead of DEFAULT value

basically I've been following the same format used in the specification format here (http://www.geopackage.org/spec/) to express datetime

seems to work