Thread: Converting a TimestampTz into a C# DateTime

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?

Thanks,
Valerio



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

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

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

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

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.

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

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.