Bug #651: Time difference of 30 in while reading in timestamp
Ravishankar Rajagopalan (rravishankar@sify.com) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
Time difference of 30 in while reading in timestamp
Long Description
I am using Java 1.3.1 and Postgres 7.1.2 and am writing timestamp without zone info to the database using JDBC (my zone is IST or +5:30GMT). I find that there is always a time difference of 30 minutes when i read from the database.
When i query the postgres database from the SQL prompt, the time is as i had written it. But when i read it from the database into my java code it is more exactly by 30 mins.
I have pasted the code herewith (in which i have used a 30 min offset) . Any solution for this ?
Sample Code
String databaseURL = "jdbc:postgresql:am";
Timestamp ts_ls_dn =null, ts_current_time=null;
long tld =0, tct=0, diff = 0, tot_fails=0;
int rec_fails=0;
boolean fatal_err = false;
try{
Class.forName("org.postgresql.Driver");
Connection conn1 = DriverManager.getConnection(databaseURL,"abc","");
PreparedStatement p = conn1.prepareStatement("select last_dn_time from mon where name=?");
p.setInt(1, serv_id);
ResultSet rs = p.executeQuery();
rs.next();
ts_ls_dn = rs.getTimestamp("last_dn_time");
ts_current_time = new Timestamp(System.currentTimeMillis());
tld = ts_ls_dn.getTime();
tct = ts_current_time.getTime();
diff = ((tct-tld)/60000)+30;//Time in minutes. 30 added to makeup for error
}
catch (Exception x){
x.printStackTrace();
}
No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
I am using Java 1.3.1 and Postgres 7.1.2 and am writing timestamp without zone info to the database using JDBC (my zone is IST or +5:30GMT). I find that there is always a time difference of 30 minutes when i read from the database.
When i query the postgres database from the SQL prompt, the time is as i had written it. But when i read it from the database into my java code it is more exactly by 30 mins.
I assume the datatype of the table column is "timestamp"? The internal
storage is in GMT, which is converted to the timezone specified by your
current TimeZone setting whenever you SELECT the value. If it works in
psql then the database side of things seems to be okay. I suspect that
in the JDBC case, the backend's TimeZone variable is being set to
something different than what you think it is (either +5 or +6, not
5:30). Can you try a "SET TimeZone" to set the zone explicitly within
your JDBC program, and see whether the results change?
regards, tom lane
Actually the Timestamp class does not use Timezone. Timezone in Java 1.3 can be set only on Calendar class objects. Timestamp inherits some methods from the Date class but none which concerned with setting timezones :-(
Ravi
Tom Lane <tgl@sss.pgh.pa.us>:
pgsql-bugs@postgresql.org writes:
I am using Java 1.3.1 and Postgres 7.1.2 and am writing timestamp
without zone info to the database using JDBC (my zone is IST or
+5:30GMT). I find that there is always a time difference of 30 minutes
when i read from the database.When i query the postgres database from the SQL prompt, the time is as
i had written it. But when i read it from the database into my java code
it is more exactly by 30 mins.I assume the datatype of the table column is "timestamp"? The internal
storage is in GMT, which is converted to the timezone specified by your
current TimeZone setting whenever you SELECT the value. If it works in
psql then the database side of things seems to be okay. I suspect that
in the JDBC case, the backend's TimeZone variable is being set to
something different than what you think it is (either +5 or +6, not
5:30). Can you try a "SET TimeZone" to set the zone explicitly within
your JDBC program, and see whether the results change?regards, tom lane
-------------------------------------------------
Sify Mail - now with Anti-virus protection powered by Trend Micro, USA.
Know more at http://mail.sify.com
Take the shortest route to success!
Click here to know how http://education.sify.com
Is it safe to assume that this difference of 30 mins would be consistent even if our implementation in used across different timezones ?
Moreover it was found that if we retrieve the resultset as a getString instead of a getTimestamp, it returns correctly.
But the valueOf operator to convert this String into a Timestamp cannot be used as the String format returned required by valueOf and that got by resultSet.getString are not the same.
Is there any official fix for this ?
Thanks.
Ravi
R Ravishankar <rravishankar@sify.com>:
Actually the Timestamp class does not use Timezone. Timezone in Java 1.3
can be set only on Calendar class objects. Timestamp inherits some
methods from the Date class but none which concerned with setting
timezones :-(Ravi
Tom Lane <tgl@sss.pgh.pa.us>:
pgsql-bugs@postgresql.org writes:
I am using Java 1.3.1 and Postgres 7.1.2 and am writing timestamp
without zone info to the database using JDBC (my zone is IST or
+5:30GMT). I find that there is always a time difference of 30minutes
when i read from the database.
When i query the postgres database from the SQL prompt, the time is
as
i had written it. But when i read it from the database into my java
code
it is more exactly by 30 mins.
I assume the datatype of the table column is "timestamp"? The
internal
storage is in GMT, which is converted to the timezone specified by
your
current TimeZone setting whenever you SELECT the value. If it works
in
psql then the database side of things seems to be okay. I suspect
that
in the JDBC case, the backend's TimeZone variable is being set to
something different than what you think it is (either +5 or +6, not
5:30). Can you try a "SET TimeZone" to set the zone explicitlywithin
your JDBC program, and see whether the results change?
regards, tom lane
-------------------------------------------------
Sify Mail - now with Anti-virus protection powered by Trend Micro, USA.
Know more at http://mail.sify.comTake the shortest route to success!
Click here to know how http://education.sify.com
-------------------------------------------------
Sify Mail - now with Anti-virus protection powered by Trend Micro, USA.
Know more at http://mail.sify.com
Take the shortest route to success!
Click here to know how http://education.sify.com
R Ravishankar <rravishankar@sify.com> writes:
Moreover it was found that if we retrieve the resultset as a getString instead of a getTimestamp, it returns correctly.
Oh? That suggests that the problem is in JDBC or the underlying JVM.
I'd suggest asking the pgsql-jdbc list about it.
regards, tom lane