Re: "Timestamp out of range"

On 01/30/2014 05:41 AM, Sandeep Devan wrote:
> Hi,
> We have a .Net application which stores data in PostgreSQL. We have a
> perfectly working code with PostgreSQL 8.3 and now we are trying to
> upgrade our PostgreSQL server from version 8.3 to 9.3 and our code seems
> to break.
> For connecting PostgeSQL we are using ‘OLEDB’.
> The issue we are getting is “Timestamp out of range”. When looked
> through the logs receiving weird timestamp “152085-04-28 06:14:51.818821”.
>  From our application We are trying to pass a value from .Net code to
> postgreSQL function which is of type timestamp. As we are using OLEDB
> for connections, we are using parameter type as “OleDbType.DBTimeStamp”
> and sending date time value from .Net code. This code works in
> PostgreSQL 8.3 but breaks in 9.3. From the logs of Postgresql 9.3 the
> parameter value which we are receiving is “152085-04-28 06:14:51.818821”.
> We tried to execute the same function using ‘npgsql’ provider from
> sample .net code by passing Date time value and giving parameter type as
> “NpgsqlDbType.TimestampTZ” with this we are getting correct results.
>  From the logs of PostgreSQL the parameter values received at the
> function is shown as “E'2014-01-30 12:17:50.804220'::timestamptz”.
> Tried in other versions of postgresql i.e. 9.1, 9.2, 9.3 and was
> breaking in all these versions.
> Any Idea why this is breaking in other versions of PostgreSQL when
> perfectly working in 8.3?

A possible clue is that 8.3 was the last version in which the default 
storage for timestamps was double precision floating point. In 
subsequent versions the default storage is eight-byte integers. Could be 
OleDbType.DBTimeStamp and NpgsqlDbType.TimestampTZ differ in this regard.

> Thanks,
> Sandeep

-- 
Adrian Klaver
adrian.klaver@gmail.com