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
_______________________________________________
For more information about ZODB, see the ZODB Wiki:
http://www.zope.org/Wikis/ZODB/
ZODB-Dev mailing list - ZODB-Dev@zope.org
https://mail.zope.org/mailman/listinfo/zodb-dev
