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

[Wojtek]

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]

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]

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]

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

[gabrielnasser]

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

[Alexey]

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]

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]

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

[Zoran]

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]

Try to do your best.

[Zoran]

We made a new database. You will be able to reproduce the problem. Please send me your e-mail to [email protected] so that I can give you the URL and user account.

[Alexey]

Sent.

[Zoran]

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

[Alexey]

Thanks for the information.