[ZODB-Dev] Transaction Timestamps

[Kai Lautaportti]

On 2011-02-03 17:37:54 +0200, Shane Hathaway said:

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

Thanks Shane!

Here's a slightly different version of the MySQL query that outputs 
ISO8601 formatted timestamps:

SELECT CONCAT(
    CAST(((tid >> 32) div 535680 + 1900) AS CHAR),
    '-',
    LPAD(CAST((1 + ((tid >> 32) % 535680) div 44640) AS CHAR), 2, '0'),
    '-',
    LPAD(CAST((1 + ((tid >> 32) % 44640) div 1440) AS CHAR), 2, '0'),
    'T',
    LPAD(CAST((((tid >> 32) % 1440) div 60) AS CHAR), 2, '0'),
    ':',
    LPAD(CAST(((tid >> 32) % 60) AS CHAR), 2, '0'),
    ':',
    CONCAT(LPAD(SUBSTRING_INDEX(CAST(((tid & 4294967295) * 60.0 / 
4294967296.0) AS CHAR), '.', 1), 2, '0'), '.', 
SUBSTRING_INDEX(CAST(((tid & 4294967295) * 60.0 / 4294967296.0) AS 
CHAR), '.', -1)),
    'Z') AS iso8601
FROM transaction;

- Kai