Sample Header Ad - 728x90

Parsing COPY's binary format to access a tsrange

4 votes
1 answer
4161 views
How is tsrange stored in binary? For example create table 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); It outputs: 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 One field is: 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 There: 00000019 - is 25 bytes length 02 - brackets 00000008 - subfield length 0001e808 0d771100 and 00021c9a dc4d0e00 - stored timestamp with miroseconds. How to convert it to integer timestamp?
Asked by eri (143 rep)
Nov 13, 2017, 03:41 PM
Last activity: Nov 13, 2017, 11:16 PM