Converting a TimestampTz into a C# DateTime

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.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello,
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.
Thanks for your help !

On Mon, Nov 14, 2016 at 1:12 PM, Albe Laurenz <laurenz.albe@wien.gv.at>
wrote:

Jerome Wagner <jerome.wagner@laposte.net> writes:

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 ?

You can inspect the integer_datetimes setting, via SHOW or
current_setting(). In a libpq client it's also directly available
from PQparameterStatus().

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jerome Wagner wrote:

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.

I guess you have to use the SPI interface to run the SQL statement

SHOW integer_datetimes;

and check if the result is 'on'.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Nov 14, 2016 12:53, "valeriof" <valerio_farruggio@hotmail.com> wrote:

Hi,
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?

Npgsql supports the binary COPY protocol. Their implementation is here:

https://github.com/npgsql/npgsql/blob/dev/src/Npgsql/TypeHandlers/DateTimeHandlers/TimeStampHandler.cs

I was able to make it work by reusing the code in TimeStampHandler.cs (in my
application I cannot directly reference Npgsql):

long datetime = GetInt64(buffer, ref pos);
// 8 bytes: datetime

if (datetime == long.MaxValue)
return DateTime.MaxValue;
else if (datetime == long.MinValue)
return DateTime.MinValue;

DateTime dt;
int date;
long time;

if (datetime >= 0)
{
date = (int)(datetime / 86400000000L);
time = datetime % 86400000000L;

date += 730119; // 730119 = days since era (0001-01-01)
for 2000-01-01
time *= 10; // To 100ns
}
else
{
datetime = -datetime;
date = (int)(datetime / 86400000000L);
time = datetime % 86400000000L;
if (time != 0)
{
++date;
time = 86400000000L - time;
}
date = 730119 - date; // 730119 = days since era
(0001-01-01) for 2000-01-01
time *= 10; // To 100ns
}

TimeSpan ts = new TimeSpan(date, 0, 0, 0);
dt = (new DateTime(ts.Ticks) + new
TimeSpan(time)).ToLocalTime();

return dt;

BTW, a comment says this about the floating point representation: "A
deprecated compile-time option of PostgreSQL switches to a floating-point
representation of some date/time
fields. Npgsql (currently) does not support this mode." Is it safe to say
that the floating point format is less in use compared to the long int? If
Npgsql doesn't support it, any application that uses Npgsql will have this
limitation anyway. Am I correct?

--
View this message in context: http://postgresql.nabble.com/Converting-a-TimestampTz-into-a-C-DateTime-tp5930221p5930394.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

valeriof wrote:

BTW, a comment says this about the floating point representation: "A
deprecated compile-time option of PostgreSQL switches to a floating-point
representation of some date/time
fields. Npgsql (currently) does not support this mode." Is it safe to say
that the floating point format is less in use compared to the long int? If
Npgsql doesn't support it, any application that uses Npgsql will have this
limitation anyway. Am I correct?

It looks that way.

64-bit integer representation for datetimes was introduced in 7.3 and
became the default in 8.4, over 7 years ago.
So I guess you won't encounter databases with floating point datetimes
very often these days.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Awesome. Thanks everybody for your help

--
View this message in context: http://postgresql.nabble.com/Converting-a-TimestampTz-into-a-C-DateTime-tp5930221p5930465.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general