Re: Bug in storing Timestamp

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;