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 ...)? — user330315
– user330315, Commented Feb 13, 2019 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
– user35934, Commented Feb 13, 2019 at 20:14

Laurenz Albe · Accepted Answer · 2019-02-14 09:51:59Z

3

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

answered Feb 14, 2019 at 9:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user35934 Over a year ago

Doh! That was way too simple :) Thanks!

Collectives™ on Stack Overflow

Manually convert PostgreSQL binary format of timestamp to integer

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related