[ZODB-Dev] Transaction Timestamps
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 ___ 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
Re: [ZODB-Dev] Transaction Timestamps
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(CASTtid 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
Re: [ZODB-Dev] Transaction Timestamps
Am 03.02.2011, 22:39 Uhr, schrieb Kai Lautaportti kai.lautapor...@hexagonit.fi: 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(CASTtid 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; Glad I'm using an object-oriented database :-) -Matthias ___ 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
