Re: Bug #651: Time difference of 30 in while reading in timestamp

Ravishankar Rajagopalan (rravishankar(at)sify(dot)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(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:

> pgsql-bugs(at)postgresql(dot)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(at)sify(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
>
> > pgsql-bugs(at)postgresql(dot)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
>

-------------------------------------------------
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(at)sify(dot)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