import java.sql.*;
import java.util.TimeZone;
import java.util.SimpleTimeZone;
import java.util.Calendar;
import java.text.SimpleDateFormat;

public class cc_tz_test {
	public static void main(String args[]) throws SQLException {

//set the timezone to MST so that others can easily replicate, then
    //install a variant where daylight savings is turned off (this will allow us to
    //see the source dates un-munged, which is important here)
    TimeZone.setDefault(TimeZone.getTimeZone("MST"));
    TimeZone curTz = TimeZone.getDefault();
    TimeZone.setDefault(new SimpleTimeZone(curTz.getRawOffset(),
curTz.getID()));   //if you don't do this, t1 and t2 will get rolled forward because of DST...
    System.out.println("current tz:"+TimeZone.getDefault());

    //now we're going to write some sample data
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    System.out.println("starting t1: "+sdf.format(new Timestamp(1112511962000L)));     //2005-04-03 00:06:02
    System.out.println("starting t2: "+sdf.format(new Timestamp(1112520583000L)));     //2005-04-03 02:29:43
    System.out.println("starting t3: "+sdf.format(new Timestamp(1112522529000L)));     //2005-04-03 03:02:09

    //here we go...
    Connection conn = null;
    Statement stmt = null;
    PreparedStatement pstmt = null;
    Timestamp t = null;
    Calendar cal = Calendar.getInstance();
    boolean usepstmt = true;
    try {
	Class.forName("org.postgresql.Driver");
        conn = DriverManager.getConnection("jdbc:postgresql://localhost:5810/jurka","jurka","");
        stmt = conn.createStatement();
	stmt.execute("CREATE TEMP TABLE foo (uid serial, trxtime timestamp without time zone)");
        pstmt = conn.prepareStatement("INSERT INTO Foo (TrxTime) VALUES (?)");


        //insert some sample data
        //...2005-04-03 00:06:02 (before the DST cutover)
        t = new Timestamp(1112511962000L);        
        System.out.println("inserting t1: "+t+" (millis: "+t.getTime()+")");
        if (usepstmt) {
            pstmt.setTimestamp(1, t);
            pstmt.executeUpdate();
        } else {
            stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES ('"+sdf.format(t)+"')");
        }

        //...2005-04-03 02:29:43 (during the DST cutover)
        t = new Timestamp(1112520583000L);        
        System.out.println("inserting t2: "+t+" (millis: "+t.getTime()+")");
        if (usepstmt) {
            pstmt.setTimestamp(1, t);
            pstmt.executeUpdate();
        } else {
            stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES ('"+sdf.format(t)+"')");
        }

        //...2005-04-03 03:02:09 (after the DST cutover)
        t = new Timestamp(1112522529000L);        
        System.out.println("inserting t3: "+t+" (millis: "+t.getTime()+")");
        if (usepstmt) {
            pstmt.setTimestamp(1, t);
            pstmt.executeUpdate();
        } else {
            stmt.executeUpdate("INSERT INTO Foo (TrxTime) VALUES ('"+sdf.format(t)+"')");
        }

        if (!conn.getAutoCommit()) conn.commit();

        //now read the values back out
        ResultSet rs = stmt.executeQuery("SELECT * FROM Foo");
        int cntr = 0;
        while (rs.next()) {
            t = rs.getTimestamp(2);
            System.out.println("resulting t"+(++cntr)+": [UID]:"+rs.getObject(1)+" [TrxTime]:"+t+" (millis: "+t.getTime()+")");
        }
        rs.close();
    } catch (Exception e) {
        System.out.println("Unexpected Exception: "+e);
        e.printStackTrace();

    } finally {
        if (stmt!=null) try {stmt.close();} catch (SQLException e) {}
        if (pstmt!=null) try {pstmt.close();} catch (SQLException e) {}
        if (conn!=null) try {conn.close();} catch (SQLException e) {}
    }

	}
}