手动将PostgreSQL二进制格式的时间戳转换为整数 [英] Manually convert PostgreSQL binary format of timestamp to integer
问题描述
我读到 PostgreSQL 在内部将时间戳存储为 8 字节/64 位整数.
I've read that PostgreSQL internally stores timestamps as an 8-byte/64-bit integer.
为了检查这一点,我创建了一个表,插入了一些时间戳并查询了 heap_page_items
.
输出例如对于时间戳 2019-01-08 09:00:00
是 00 e4 c2 56 ed 21 02 00
.
To check this I've created a table, inserted some timestamps and queried the heap_page_items
.
The output e.g. for timestamp 2019-01-08 09:00:00
was 00 e4 c2 56 ed 21 02 00
.
鉴于此十六进制序列,我如何手动计算自 PostgreSQL 纪元 (2000-01-01) 以来的微秒数?使用时间戳转换器,我得到了值 600253200000
作为结果.我必须如何解包"字节才能接收此值?
Given this hex sequence, how do I manually compute the number of microseconds since PostgreSQL epoch (2000-01-01)? Using a timestamp converter I got value 600253200000
as result. How do I have to "unpack" the bytes to receive this value?
推荐答案
您的分析几乎是完美的,而且您必须在小端机器上.
Your analysis is almost perfect, and you must be on a little-endian machine.
您不必解压该值,因为在您的 64 位架构上,8 字节整数可以放入 PostgreSQL Datum
.
You don't have to unpack the value, because on your 64-bit architecture 8-byte integers can fit into a PostgreSQL Datum
.
SELECT extract(epoch FROM TIMESTAMP '2019-01-08 09:00:00')
- extract(epoch FROM TIMESTAMP '2000-01-01 00:00:00');
?column?
-----------
600253200
(1 row)
600253200 秒是 600253200000000 微秒.
600253200 seconds are 600253200000000 microseconds.
你在表格中看到的整数是0x000221ed56c2e400,对应十进制600253200000000:
The integer you see in the table is 0x000221ed56c2e400, which corresponds to decimal 600253200000000:
bash> printf "%ld\n" 0x000221ed56c2e400
600253200000000
这篇关于手动将PostgreSQL二进制格式的时间戳转换为整数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!