Manually convert PostgreSQL binary format of timestamp to integer

Question

I've read that PostgreSQL internally stores timestamps as an 8-byte/64-bit integer.

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.

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?

Why would you want to do that? What's wrong with `extract(epoch from ...)`? — , Feb 13 '19 at 19:48
I need to identify a range of raw pages of a large index (millions of entries) containing timestamps to apply some optimizations, like prewarming the buffer_cache etc. Besides that I'm interested in how this works, as I plan to write some code that produces binary files using this format — user35934, Feb 13 '19 at 20:14

score 3 · Accepted Answer · answered Feb 14 '19 at 09:51

Your analysis is almost perfect, and you must be on a little-endian machine.

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 seconds are 600253200000000 microseconds.

The integer you see in the table is 0x000221ed56c2e400, which corresponds to decimal 600253200000000:

bash> printf "%ld\n" 0x000221ed56c2e400
600253200000000

Doh! That was way too simple :) Thanks! – user35934 Feb 14 '19 at 12:37 — user35934, Feb 14 '19 at 12:37

Manually convert PostgreSQL binary format of timestamp to integer

1 Answers1

Linked