[ZODB-Dev] Transaction Timestamps

[Shane Hathaway]

FWIW, here is a way to extract timestamps from transaction IDs stored 
with RelStorage.  Kai of HexagonIT suggested it.  The timestamps should 
be in UTC.

PostgreSQL:

select
   (tid >> 32) / 535680 + 1900 as year,
   1 + ((tid >> 32) % 535680) / 44640 as month,
   1 + ((tid >> 32) % 44640) / 1440 as day,
   ((tid >> 32) % 1440) / 60 as hour,
   ((tid >> 32) % 60) as minute,
   (tid & 4294967295) * 60.0 / 4294967296.0 as second
   from transaction;

MySQL:

select
   (tid >> 32) div 535680 + 1900 as year,
   1 + ((tid >> 32) % 535680) div 44640 as month,
   1 + ((tid >> 32) % 44640) div 1440 as day,
   ((tid >> 32) % 1440) div 60 as hour,
   ((tid >> 32) % 60) as minute,
   (tid & 4294967295) * 60.0 / 4294967296.0 as second
   from transaction;

Shane