主页 / user-26530

eri's questions

Martin Hope
eri
Asked: 2017-11-14 07:41:42 +0800 CST
  • 4

tsrange 如何以二进制形式存储？

例如创建表

CREATE TABLE public.test (t tsrange);
INSERT INTO test VALUES ('[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT INTO test VALUES ('[2011-01-01 14:31, 2015-11-01 15:30)');
INSERT INTO test VALUES ('[2017-01-01 14:31, 2018-11-01 15:30)');
COPY test TO '/tmp/pgcopy' WITH (FORMAT binary);
COPY test TO '/tmp/pgcopy.csv' WITH (FORMAT csv);

它输出：

 cat /tmp/pgcopy.csv                                                                                                                                                                                                  
"[""2010-01-01 14:30:00"",""2010-01-01 15:30:00"")"
"[""2011-01-01 14:31:00"",""2015-11-01 15:30:00"")"
"[""2017-01-01 14:31:00"",""2018-11-01 15:30:00"")"


hexdump -C /tmp/pgcopy
00000000  50 47 43 4f 50 59 0a ff  0d 0a 00 00 00 00 00 00  |PGCOPY..........|
00000010  00 00 00 00 01 00 00 00  19 02 00 00 00 08 00 01  |................|
00000020  1f 19 f9 a9 aa 00 00 00  00 08 00 01 1f 1a d0 3d  |...............=|
00000030  4e 00 00 01 00 00 00 19  02 00 00 00 08 00 01 3b  |N..............;|
00000040  c8 89 51 11 00 00 00 00  08 00 01 c6 7b 1a 3a 0e  |..Q.........{.:.|
00000050  00 00 01 00 00 00 19 02  00 00 00 08 00 01 e8 08  |................|
00000060  0d 77 11 00 00 00 00 08  00 02 1c 9a dc 4d 0e 00  |.w...........M..|
00000070  ff ff                                             |..|
00000072

一个领域是：

00 00 00 19 02 00 00 00 08 00 01 e8 08 0d 77 11 00 00 00 00 08  00 02 1c 9a dc 4d 0e 00

那里：

00000019- 长度为 25 个字节

02- 括号

00000008- 子字段长度

0001e808 0d771100和00021c9a dc4d0e00- 以微秒为单位的存储时间戳。

如何将其转换为整数时间戳？

postgresql dump