Thread: Bug in storing Timestamp

Short description :
Storing java.sql.Timestamp with specified milliseconds value results to
wrong value stored in db.

System details :
OS : WindowsXP
PostgreSQL version : 8.0.1
Driver version : 8.0 build 310

Detailed description :
In order to reproduce the bug run the following code.
Create the table in the db.
The code creates a timestamp of "0001-01-01 02:02:02.0" and stores it
into the db.
The actual value stored is "02:22:42".


CREATE TABLE TYPES_EXTENDED (
  id char(10),
  t_time time
)
WITHOUT OIDS;

long milliseconds = Timestamp.valueOf("0001-01-01 02:02:02.0").getTime();
Timestamp javaTimestamp = new Timestamp(milliseconds);
System.out.println("Timestamp stored : " + javaTimestamp);
PreparedStatement stmt = connection.prepareStatement("insert into
types_extended(ID,t_time) values ('aaa',?)");
stmt.setTimestamp(1, javaTimestamp);
stmt.execute();


Thank you in advance,

--
Boris Kirzner
Mainsoft Corporation
http://www.mainsoft.com

Hi !

I have never had any problems with timestamps, if I remember correct the
resolution gets worse when you get far away from 1900 or so, is it possible
that using year 0001 gives that bad resolution maybe ?

Mikael

----- Original Message -----
From: "Boris Kirzner" <borisk@mainsoft.com>
To: <pgsql-jdbc@postgresql.org>
Sent: Monday, April 11, 2005 2:39 PM
Subject: [JDBC] Bug in storing Timestamp


> Short description :
> Storing java.sql.Timestamp with specified milliseconds value results to
> wrong value stored in db.
>
> System details :
> OS : WindowsXP
> PostgreSQL version : 8.0.1
> Driver version : 8.0 build 310
>
> Detailed description :
> In order to reproduce the bug run the following code.
> Create the table in the db.
> The code creates a timestamp of "0001-01-01 02:02:02.0" and stores it into
> the db.
> The actual value stored is "02:22:42".
>
>
> CREATE TABLE TYPES_EXTENDED (
>  id char(10),
>  t_time time
> )
> WITHOUT OIDS;
>
> long milliseconds = Timestamp.valueOf("0001-01-01 02:02:02.0").getTime();
> Timestamp javaTimestamp = new Timestamp(milliseconds);
> System.out.println("Timestamp stored : " + javaTimestamp);
> PreparedStatement stmt = connection.prepareStatement("insert into
> types_extended(ID,t_time) values ('aaa',?)");
> stmt.setTimestamp(1, javaTimestamp);
> stmt.execute();
>
>
> Thank you in advance,
>
> --
> Boris Kirzner
> Mainsoft Corporation
> http://www.mainsoft.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly

"mikael-aronsson" <mikael-aronsson@telia.com> writes:
> I have never had any problems with timestamps, if I remember correct the
> resolution gets worse when you get far away from 1900 or so, is it possible
> that using year 0001 gives that bad resolution maybe ?

Good thought, but no; the error at 0001 is still down around the sixth
decimal place:

regression=# select '0001-01-01 02:02:02.123456'::timestamp;
         timestamp
----------------------------
 0001-01-01 02:02:02.123459
(1 row)

I was wondering about the fact that he was storing into a time column
rather than a timestamp.  That works if the conversion is done on the
server, modulo the sixth decimal place again:

regression=# select '0001-01-01 02:02:02.123456'::timestamp::time;
      time
-----------------
 02:02:02.123459
(1 row)

but maybe it's confusing the JDBC driver into doing the wrong thing
entirely.

            regards, tom lane

On Mon, 11 Apr 2005, Boris Kirzner wrote:

> Short description :
> Storing java.sql.Timestamp with specified milliseconds value results to
> wrong value stored in db.
>
> PostgreSQL version : 8.0.1
> Driver version : 8.0 build 310
>
> Detailed description :
> In order to reproduce the bug run the following code.
> Create the table in the db.
> The code creates a timestamp of "0001-01-01 02:02:02.0" and stores it
> into the db.
> The actual value stored is "02:22:42".

I am not able to reproduce this problem.  The attached test case shows a
problem with storing java.sql.Time into a timetz type because the correct
offset is not saved, but that's not what you've described.  Perhaps it is
something specific to your timezone?

Kris Jurka

Kris Jurka wrote:

>I am not able to reproduce this problem.  The attached test case shows a
>problem with storing java.sql.Time into a timetz type because the correct
>offset is not saved, but that's not what you've described.  Perhaps it is
>something specific to your timezone?
>
>Kris Jurka
>

Hello Kris
After more investigation I found that the bug I'm talking about can be
reproduced on timestamp column also.

The configuration is as follows :
PostgeSQL runs on WindowsXP that has "Automatically adjust clock for dst
savings" OFF, timezone is (GMT+02:00) Jerusalem
Client runs on WindowsXP that has "Automatically adjust clock for dst
savings" OFF , timezone is (GMT+02:00) Jerusalem

The test stores 2004-08-09 20:30:15.50 timestamp into a table and
immediately fetches it.
Actual result : the value fetched is 2004-08-09 21:30:15.5
Expected result : the value fetched is 2004-08-09 20:30:15.5

Note : if you set client's "Automatically adjust clock for dst savings"
to ON, the test passes.

Attached are test and table sql.

Thanks,
Boris

--
Boris Kirzner
Mainsoft Corporation
http://www.mainsoft.com

package tests;

import java.sql.*;
import java.util.Properties;

/*
 * @(#)PostgreSQL_Timestamp_Test.java    1.0 Created on Apr 21, 2005 2:19:54 PM
 *
 * Copyright 2002-2003 Mainsoft Corporation. All Rights Reserved.
 *
 * This software is the proprietary information of Mainsoft Corporation.
 * Use is subject to license terms.
 *
 */

public class PostgreSQL_Timestamp_Test
{
    public static void main(String[] args)
    {
        Connection connection = connect("xp050", "", "GHTDB", "postgres", "postgres", false);
        Timestamp ts = Timestamp.valueOf("2004-08-09 20:30:15.50");

        try
        {
            PreparedStatement pstmt = connection.prepareStatement("delete from borisk_timestamp");
            pstmt.execute();

            pstmt = connection.prepareStatement("insert into borisk_timestamp values(?)");
            pstmt.setTimestamp(1, ts);

            pstmt.execute();

            pstmt = connection.prepareStatement("select * from borisk_timestamp");
            pstmt.execute();

            ResultSet rs = pstmt.getResultSet();
            rs.next();
            Timestamp ts1 = rs.getTimestamp(1);
            System.out.println(ts);
            System.out.println(ts1);

            pstmt = connection.prepareStatement("delete from borisk_timestamp");
            pstmt.execute();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    private static Connection connect(String host, String port, String catalog, String user, String pass, boolean
trace)
    {
        String conStr = buildUrl(host, port, catalog);

        try
        {
            activateJDBCDriver(getDriverName());
            Properties info = new Properties();

            info.put("user", user);
            info.put("password", pass);

            if (trace)
            {
                System.out.println("Getting connection to host " + host + "  port " + port + " catalog " + catalog);
            }
            DriverManager.setLoginTimeout(15);
            Connection con = DriverManager.getConnection(conStr, info);
            if (trace)
            {
                System.out.println("Connected to host " + host);
            }
            return con;
        }
        catch (SQLException e)
        {
            if (trace)
            {
                System.out.println("Error connecting to host " + host + " : " + e.getMessage());
            }
        }

        return null;
    }

    private static void activateJDBCDriver(String _driver)
    {
        try
        {
            Class.forName(_driver).newInstance();
        }
        catch (InstantiationException e)
        {
            e.printStackTrace();
        }
        catch (IllegalAccessException e)
        {
            e.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
    }

    private static String buildUrl(String host, String port, String catalog)
    {
        return "jdbc:postgresql://" + host + "/" + catalog;
    }

    private static String getDriverName()
    {
        return "org.postgresql.Driver";
    }
}
CREATE TABLE borisk_timestamp
(
  t_timestamp timestamp
)
WITHOUT OIDS;
ALTER TABLE borisk_timestamp OWNER TO postgres;

On Thu, 21 Apr 2005, Boris Kirzner wrote:

>
> Kris Jurka wrote:
>
> >I am not able to reproduce this problem.  The attached test case shows a
> >problem with storing java.sql.Time into a timetz type because the correct
> >offset is not saved, but that's not what you've described.  Perhaps it is
> >something specific to your timezone?
> >
> After more investigation I found that the bug I'm talking about can be
> reproduced on timestamp column also.
>
> The configuration is as follows :
> PostgeSQL runs on WindowsXP that has "Automatically adjust clock for dst
> savings" OFF, timezone is (GMT+02:00) Jerusalem
> Client runs on WindowsXP that has "Automatically adjust clock for dst
> savings" OFF , timezone is (GMT+02:00) Jerusalem
>
> The test stores 2004-08-09 20:30:15.50 timestamp into a table and
> immediately fetches it.
> Actual result : the value fetched is 2004-08-09 21:30:15.5
> Expected result : the value fetched is 2004-08-09 20:30:15.5


This is a known bug in the 8.0 driver and seems different than your
original complaint.  Being off by an hour (the difference between the two
timezones) is the expected (and incorrect) behavior that I expected to
see.  Your original complaint was off by 20:40 which is an unusual number,
are you still seeing that?

Kris Jurka