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?


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.