Thread: "Timestamp out of range"
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?
Thanks,
Sandeep
Visit our Website at www.rmesi.co.in
This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.
Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI’s commercial interests.
This email has been scanned for viruses by Trend ScanMail.
This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications.
Freedom of Information Act 2000
This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI’s commercial interests.
This email has been scanned for viruses by Trend ScanMail.
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