seeing you answer I have a question for which I found no answer a few weeks ago : is there a way to know at runtime which internal representation timestamps have ?
I am trying to deal with the COPY binary protocol with only SQL access to the remote server and would like to find a way to know the internal representation to read / write the correct timestamps.
valeriof wrote: > I'm handling a TimestampTz value inside a plugin to stream WAL changes to a > .NET client application. What I'm trying to do is to return all possible > column changes as binary (don't like to have Postgres handle the conversion > to string as I may need to have access to the bytes at the client level). In > case of a TimestampTz, is it possible to return the 8-bytes long integer and > then from the C# application convert the value to Ticks?
Sure, if you know how it is stored internally.
One of your problems will be that the format depends on whether PostgreSQL was configured with --disable-integer-datetimes or not.
With that switch, a timestamp is a double precision value, otherwise a 64-bit integer value. In the former case, it measures seconds after midnight 2000-01-01, while in the latter case it measures microseconds after that timestamp.