Cannot retrieve timestamp filed value from db (PostgreSQLDirect .NET 2.50.16)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Wojtek
Posts: 14
Joined: Sun 20 Nov 2005 18:51
Location: Warsaw

Cannot retrieve timestamp filed value from db (PostgreSQLDirect .NET 2.50.16)

Post by Wojtek » Mon 21 Aug 2006 18:14

Dear Colleagues

I have been using PostgreSQLDirect .NET 2.50.16 + VS2005 Prof.
I am experiencing strange problem with getting data from timestamp field.
Initially I have been developing small application using Windows based Postgres 8.1 server (localhost). All was OK.
When I moved db to production Linux machine the problem surfaced.

Sample query to show the problem:
"SELECT id, date FROM orders WHERE id=6900"

Whne I query db directly in PgAdmin I get results:

id ; date
-------------------------------------------
6900 ; 2006-06-05 09:22:44.437441


Then I use following code in C#:

myPgConn = new PgSqlConnection(cnnStr);
myPgConn.Open();
PgSqlCommand cmd = new PgSqlCommand("SELECT id, date FROM orders WHERE id=6900");
cmd.Connection = myPgConn;
PgSqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
textBox1.AppendText( "Fld 1 type :" + rdr.GetDataTypeName(0).ToString());
textBox1.AppendText( "\nFld 1 value:" + rdr.GetValue(0).ToString());
textBox1.AppendText( "\nFld 2 type :" + rdr.GetDataTypeName(1).ToString());
textBox1.AppendText( "\nFld 2 value:" + rdr.GetValue(1).ToString() + "\n");
}
rdr.Close();


This code returns following results:
Fld 1 type :BIGINT
Fld 1 value:6900
Fld 2 type :TIMESTAMP
Fld 2 value:2000-01-01 00:00:00 <-- Not correct !

I use protocol version ProtocolVersion.Ver30 (in PgSqlConnectionStringBuilder - not shown in example)

When I switch to Ver20 problem is eliminated - the code returns values:
Fld 1 type :BIGINT
Fld 1 value:6900
Fld 2 type :TIMESTAMP
Fld 2 value:2006-06-05 09:22:44 <-- Correct !


Db is ver. Postgres 8.1 on Linux (Ubuntu 6) machine.
Entries in postgres.conf:
...
datestyle = 'iso, dmy'
client_encoding = unicode
lc_messages = 'pl_PL.UTF-8'
lc_monetary = 'pl_PL.UTF-8' # locale for monetaryformatting
lc_numeric = 'pl_PL.UTF-8' # locale for number formatting
lc_time = 'pl_PL.UTF-8' # locale for time formatting

Suppose if I do not want to switch to protocol Ver30 how can I get rid of this problem ?

Appreciate your comments.

Best regards,
Wojtek Pawlowski

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Tue 22 Aug 2006 07:41

We are investigating this problem.
Please perform a bit more testing with different timestamp values (e.g., 1999-18-05 23:59:02, etc.)

gabrielnasser
Posts: 15
Joined: Wed 25 Jan 2006 19:57

Similar Problem With Postgres Installed on Linux

Post by gabrielnasser » Thu 03 May 2007 15:03

Hello,

I have been using an application I developed with driver version 2.55.23 with various versions of Postgres installed on Windows - no problems.

We just installed Postgres 8.1.8 on i486-pc-linux-gnu (Debian 4.1.1-21). We tried connecting our application (running on Windows) to the same exact database on Linux but we are facing problems with timestamps.

The query is akin to

select commissioned_time::timestamp, location_code, item_guid
from serial_item, item_master
where commissioned_time >= ('2007-05-01')::timestamp
AND commissioned_time < ('2007-05-04')::timestamp
AND item_master.full_code = serial_item.item_full_code

we have records that match 3-May-2007 but they are not being returned when running agains Postgres on Linux. When were remove the WHERE clause that compares the time range then it returns all the records on the Linux install but the 'commissioned_time' field shows

'2000-01-01 00:00:00'

for each record even though the records have all been entered the past few days.

Any guidance is appreciated.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 07 May 2007 11:20

Is it possible to open your database to the internet, so we could investigate this problem?

gabrielnasser
Posts: 15
Joined: Wed 25 Jan 2006 19:57

Post by gabrielnasser » Mon 07 May 2007 12:18

I am afraid it is not possible to expose the database.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 07 May 2007 12:31

This problem can be OS-specific. We might be unable to reproduce it with our linux box.
Is it possible to create a dummy database for testing purposes and open it to me?

Zoran
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia

Post by Zoran » Tue 15 May 2007 13:53

We have the same problem. Using the same IIS 6.0 web server and a Windows 2003 Postgres 8 db, all the results below are correct (no "1.1.2000" or "151953-08-13"). When we switch to Ubuntu Linux Postgres server, the results are as follows after "//" at the and of each example.

// Example 1, CURRENT_TIMESTAMP:

cnn.CommandText = "UPDATE MetaStore SET Moment=CURRENT_TIMESTAMP WHERE Id=32";
cnn.executeNonQuery();

cnn.CommandText = "SELECT Moment FROM MetaStore WHERE Id=32";
System.DateTime dt1 = (System.DateTime)cnn.executeScalar(); //C#: "1.1.2000 0:00:00", pgAdmin: "2007-05-15 12:46:14.145087"


// Example 2, hardcoded parameter, the results the same as in example 1:

cnn.CommandText = "UPDATE MetaStore SET Moment='2007-12-31 17:50' WHERE Id=32";
cnn.executeNonQuery();

cnn.CommandText = "SELECT Moment FROM MetaStore WHERE Id=32";
System.DateTime dt2 = (System.DateTime)cnn.executeScalar(); //C#: "1.1.2000 0:00:00", pgAdmin: "2007-12-31 17:50:00"


// Example 3, external parameter:

cnn.CommandText = "UPDATE MetaStore SET Moment=$1 WHERE Id=32";
cnn.appendParameter(OdbcType.DateTime, Microline.Type.DateTime.Now);
cnn.executeNonQuery();

cnn.clearParameters();
cnn.CommandText = "SELECT Moment FROM MetaStore WHERE Id=32";
System.DateTime dt3 = (System.DateTime)cnn.executeScalar(); //C#: "15.5.2007 12:39:50", pgAdmin: "151953-08-13 08:08:47.07328"


It seems that the 2 formats are incopatible but each method can read correctly update it made.
Our locale for date is "dd.MM.yyyy." (including "." after yyyy).

There are also problems with TIME data type which I can document.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Wed 16 May 2007 06:17

Alexey wrote:Is it possible to open your database to the internet, so we could investigate this problem?

Zoran
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia

Post by Zoran » Wed 16 May 2007 14:08

This is our production db. I will check if we will be able to make a ghost copy of the disk and then open it for the external access.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 17 May 2007 06:32

Try to do your best.

Zoran
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia

A new database

Post by Zoran » Sun 20 May 2007 23:56

We made a new database. You will be able to reproduce the problem. Please send me your e-mail to priba@microline.hr so that I can give you the URL and user account.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 21 May 2007 07:43

Sent.

Zoran
Posts: 44
Joined: Thu 28 Apr 2005 21:55
Location: Zagreb, Croatia

Problem solved

Post by Zoran » Thu 07 Jun 2007 22:39

The last version of Corelab driver works fine. There are no more problems with either TIMESTAMP or TIME type.

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Mon 11 Jun 2007 07:45

Thanks for the information.

Post Reply