/*-------------------------------------------------------------------------
*
* Copyright (c) 2005, PostgreSQL Global Development Group
*
* IDENTIFICATION
*   $PostgreSQL: pgjdbc/org/postgresforest/test/jdbc2/TimezoneTest.java,v 1.3 2005/09/14 00:06:19 jurka Exp $
*
*-------------------------------------------------------------------------
*/
package org.postgresforest.test.jdbc2;

import org.postgresforest.test.TestUtil;
import junit.framework.TestCase;

import java.sql.*;
import java.util.TimeZone;
import java.util.Calendar;
import java.util.GregorianCalendar;

import org.postgresforest.PGStatement;
import org.postgresforest.jdbc2.TimestampUtils;

/*
 * Tests for time and date types with calendars involved.
 * TimestampTest was melting my brain, so I started afresh. -O
 *
 * Conversions that this code tests:
 *
 *   setTimestamp -> timestamp, timestamptz, date, time, timetz
 *   setDate      -> timestamp, timestamptz, date
 *   setTime      -> time, timetz
 *
 *   getTimestamp <- timestamp, timestamptz, date, time, timetz
 *   getDate      <- timestamp, timestamptz, date
 *   getTime      <- timestamp, timestamptz, time, timetz
 *
 * (this matches what we must support per JDBC 3.0, tables B-5 and B-6)
 */
public class TimezoneTest extends TestCase
{
    private Connection con;
    private TimeZone saveTZ;

    //
    // We set up everything in different timezones to try to exercise many cases:
    //
    // default JVM timezone: GMT+0100
    // server timezone:      GMT+0300
    // test timezones:       GMT+0000 GMT+0100 GMT+0300 GMT+1300 GMT-0500

    private Calendar cUTC;
    private Calendar cGMT03;
    private Calendar cGMT05;
    private Calendar cGMT13;

    private boolean min73;
    private boolean min74;

    public TimezoneTest(String name)
    {
        super(name);

        TimeZone UTC   = TimeZone.getTimeZone("UTC");    // +0000 always
        TimeZone GMT03 = TimeZone.getTimeZone("GMT+03"); // +0300 always
        TimeZone GMT05 = TimeZone.getTimeZone("GMT-05"); // -0500 always
        TimeZone GMT13 = TimeZone.getTimeZone("GMT+13"); // +1000 always

        cUTC   = Calendar.getInstance(UTC);
        cGMT03 = Calendar.getInstance(GMT03);
        cGMT05 = Calendar.getInstance(GMT05);
        cGMT13 = Calendar.getInstance(GMT13);
    }

    protected void setUp() throws Exception
    {
        // We must change the default TZ before establishing the connection.
        saveTZ = TimeZone.getDefault();
        TimeZone.setDefault(TimeZone.getTimeZone("GMT+01")); // Arbitary timezone that doesn't match our test timezones

        con = TestUtil.openDB();
        TestUtil.createTable(con, "testtimezone",
                             "seq int4, tstz timestamp with time zone, ts timestamp without time zone, t time without time zone, tz time with time zone, d date");
        
        // This is not obvious, but the "gmt-3" timezone is actually 3 hours *ahead* of GMT
        // so will produce +03 timestamptz output
        con.createStatement().executeUpdate("set timezone = 'gmt-3'");
        
        min73 = TestUtil.haveMinimumServerVersion(con, "7.3");
        min74 = TestUtil.haveMinimumServerVersion(con, "7.4");
        
        //System.err.println("++++++ TESTS START (" + getName() + ") ++++++");
    }
    
    protected void tearDown() throws Exception
    {
        //System.err.println("++++++ TESTS END (" + getName() + ") ++++++");
        TimeZone.setDefault(saveTZ);

        TestUtil.dropTable(con, "testtimezone");
    }

    public void testGetTimestamp() throws Exception
    {
        con.createStatement().executeUpdate("INSERT INTO testtimezone(tstz,ts,t,tz,d) VALUES('2005-01-01 15:00:00 +0300', '2005-01-01 15:00:00', '15:00:00', '15:00:00 +0300', '2005-01-01')");

        ResultSet rs = con.createStatement().executeQuery("SELECT tstz,ts,t,tz,d from testtimezone");

        assertTrue(rs.next());
        assertEquals("2005-01-01 15:00:00+03", rs.getString(1));
        assertEquals("2005-01-01 15:00:00", rs.getString(2));
        assertEquals("15:00:00", rs.getString(3));
        assertEquals("15:00:00+03", rs.getString(4));
        assertEquals("2005-01-01", rs.getString(5));

        Timestamp ts;
        
        // timestamptz: 2005-01-01 15:00:00+03
        ts = rs.getTimestamp(1);                      // Represents an instant in time, timezone is irrelevant.
        assertEquals(1104580800000L, ts.getTime());   // 2005-01-01 12:00:00 UTC
        ts = rs.getTimestamp(1, cUTC);                // Represents an instant in time, timezone is irrelevant.
        assertEquals(1104580800000L, ts.getTime());   // 2005-01-01 12:00:00 UTC
        ts = rs.getTimestamp(1, cGMT03);              // Represents an instant in time, timezone is irrelevant.
        assertEquals(1104580800000L, ts.getTime());   // 2005-01-01 12:00:00 UTC
        ts = rs.getTimestamp(1, cGMT05);              // Represents an instant in time, timezone is irrelevant.
        assertEquals(1104580800000L, ts.getTime());   // 2005-01-01 12:00:00 UTC
        ts = rs.getTimestamp(1, cGMT13);              // Represents an instant in time, timezone is irrelevant.
        assertEquals(1104580800000L, ts.getTime());   // 2005-01-01 12:00:00 UTC

        // timestamp: 2005-01-01 15:00:00
        ts = rs.getTimestamp(2);                     // Convert timestamp to +0100
        assertEquals(1104588000000L, ts.getTime());  // 2005-01-01 15:00:00 +0100
        ts = rs.getTimestamp(2, cUTC);               // Convert timestamp to UTC
        assertEquals(1104591600000L, ts.getTime());  // 2005-01-01 15:00:00 +0000
        ts = rs.getTimestamp(2, cGMT03);             // Convert timestamp to +0300
        assertEquals(1104580800000L, ts.getTime());  // 2005-01-01 15:00:00 +0300
        ts = rs.getTimestamp(2, cGMT05);             // Convert timestamp to -0500
        assertEquals(1104609600000L, ts.getTime());  // 2005-01-01 15:00:00 -0500
        ts = rs.getTimestamp(2, cGMT13);             // Convert timestamp to +1300
        assertEquals(1104544800000L, ts.getTime());  // 2005-01-01 15:00:00 +1300

        // time: 15:00:00
        ts = rs.getTimestamp(3);
        assertEquals(50400000L, ts.getTime());        // 1970-01-01 15:00:00 +0100
        ts = rs.getTimestamp(3, cUTC);
        assertEquals(54000000L, ts.getTime());        // 1970-01-01 15:00:00 +0000
        ts = rs.getTimestamp(3, cGMT03);
        assertEquals(43200000L, ts.getTime());        // 1970-01-01 15:00:00 +0300
        ts = rs.getTimestamp(3, cGMT05);
        assertEquals(72000000L, ts.getTime());        // 1970-01-01 15:00:00 -0500
        ts = rs.getTimestamp(3, cGMT13);
        assertEquals(7200000L, ts.getTime());         // 1970-01-01 15:00:00 +1300

        // timetz: 15:00:00+03
        ts = rs.getTimestamp(4);
        assertEquals(43200000L, ts.getTime());        // 1970-01-01 15:00:00 +0300 -> 1970-01-01 13:00:00 +0100
        ts = rs.getTimestamp(4, cUTC);
        assertEquals(43200000L, ts.getTime());        // 1970-01-01 15:00:00 +0300 -> 1970-01-01 12:00:00 +0000
        ts = rs.getTimestamp(4, cGMT03);
        assertEquals(43200000L, ts.getTime());        // 1970-01-01 15:00:00 +0300 -> 1970-01-01 15:00:00 +0300
        ts = rs.getTimestamp(4, cGMT05);
        assertEquals(43200000L, ts.getTime());        // 1970-01-01 15:00:00 +0300 -> 1970-01-01 07:00:00 -0500
        ts = rs.getTimestamp(4, cGMT13);
        assertEquals(43200000L, ts.getTime());        // 1970-01-01 15:00:00 +0300 -> 1970-01-02 01:00:00 +1300 (CHECK ME)

        // date: 2005-01-01
        ts = rs.getTimestamp(5);
        assertEquals(1104534000000L, ts.getTime());        // 2005-01-01 00:00:00 +0100
        ts = rs.getTimestamp(5, cUTC);
        assertEquals(1104537600000L, ts.getTime());        // 2005-01-01 00:00:00 +0000
        ts = rs.getTimestamp(5, cGMT03);
        assertEquals(1104526800000L, ts.getTime());        // 2005-01-01 00:00:00 +0300
        ts = rs.getTimestamp(5, cGMT05);
        assertEquals(1104555600000L, ts.getTime());        // 2005-01-01 00:00:00 -0500
        ts = rs.getTimestamp(5, cGMT13);
        assertEquals(1104490800000L, ts.getTime());        // 2005-01-01 00:00:00 +1300

        assertTrue(!rs.next());
    }

    public void testGetDate() throws Exception
    {
        con.createStatement().executeUpdate("INSERT INTO testtimezone(tstz,ts,d) VALUES('2005-01-01 15:00:00 +0300', '2005-01-01 15:00:00', '2005-01-01')");

        ResultSet rs = con.createStatement().executeQuery("SELECT tstz,ts,d from testtimezone");

        assertTrue(rs.next());
        assertEquals("2005-01-01 15:00:00+03", rs.getString(1));
        assertEquals("2005-01-01 15:00:00", rs.getString(2));
        assertEquals("2005-01-01", rs.getString(3));

        Date d;

        // timestamptz: 2005-01-01 15:00:00+03
        d = rs.getDate(1);                           // 2005-01-01 13:00:00 +0100 -> 2005-01-01 00:00:00 +0100
        assertEquals(1104534000000L, d.getTime());
        d = rs.getDate(1, cUTC);                     // 2005-01-01 12:00:00 +0000 -> 2005-01-01 00:00:00 +0000
        assertEquals(1104537600000L, d.getTime());
        d = rs.getDate(1, cGMT03);                   // 2005-01-01 15:00:00 +0300 -> 2005-01-01 00:00:00 +0300
        assertEquals(1104526800000L, d.getTime());
        d = rs.getDate(1, cGMT05);                   // 2005-01-01 07:00:00 -0500 -> 2005-01-01 00:00:00 -0500
        assertEquals(1104555600000L, d.getTime());
        d = rs.getDate(1, cGMT13);                   // 2005-01-02 01:00:00 +1300 -> 2005-01-02 00:00:00 +1300
        assertEquals(1104577200000L, d.getTime());

        // timestamp: 2005-01-01 15:00:00
        d = rs.getDate(2);                           // 2005-01-01 00:00:00 +0100
        assertEquals(1104534000000L, d.getTime());
        d = rs.getDate(2, cUTC);                     // 2005-01-01 00:00:00 +0000
        assertEquals(1104537600000L, d.getTime());
        d = rs.getDate(2, cGMT03);                   // 2005-01-01 00:00:00 +0300
        assertEquals(1104526800000L, d.getTime());
        d = rs.getDate(2, cGMT05);                   // 2005-01-01 00:00:00 -0500
        assertEquals(1104555600000L, d.getTime());
        d = rs.getDate(2, cGMT13);                   // 2005-01-01 00:00:00 +1300
        assertEquals(1104490800000L, d.getTime());

        // date: 2005-01-01
        d = rs.getDate(3);                           // 2005-01-01 00:00:00 +0100
        assertEquals(1104534000000L, d.getTime());
        d = rs.getDate(3, cUTC);                     // 2005-01-01 00:00:00 +0000
        assertEquals(1104537600000L, d.getTime());
        d = rs.getDate(3, cGMT03);                   // 2005-01-01 00:00:00 +0300
        assertEquals(1104526800000L, d.getTime());
        d = rs.getDate(3, cGMT05);                   // 2005-01-01 00:00:00 -0500
        assertEquals(1104555600000L, d.getTime());
        d = rs.getDate(3, cGMT13);                   // 2005-01-01 00:00:00 +1300
        assertEquals(1104490800000L, d.getTime());

        assertTrue(!rs.next());
    }

    public void testGetTime() throws Exception
    {

        con.createStatement().executeUpdate("INSERT INTO testtimezone(tstz,ts,t,tz) VALUES('2005-01-01 15:00:00 +0300', '2005-01-01 15:00:00', '15:00:00', '15:00:00 +0300')");

        ResultSet rs = con.createStatement().executeQuery("SELECT tstz,ts,t,tz from testtimezone");

        assertTrue(rs.next());
        assertEquals("2005-01-01 15:00:00+03", rs.getString(1));
        assertEquals("2005-01-01 15:00:00", rs.getString(2));
        assertEquals("15:00:00", rs.getString(3));
        assertEquals("15:00:00+03", rs.getString(4));

        Time t;

        // timestamptz: 2005-01-01 15:00:00+03
        t = rs.getTime(1);
        assertEquals(43200000L, t.getTime());        // 2005-01-01 13:00:00 +0100 -> 1970-01-01 13:00:00 +0100
        t = rs.getTime(1, cUTC);
        assertEquals(43200000L, t.getTime());        // 2005-01-01 12:00:00 +0000 -> 1970-01-01 12:00:00 +0000
        t = rs.getTime(1, cGMT03);
        assertEquals(43200000L, t.getTime());        // 2005-01-01 15:00:00 +0300 -> 1970-01-01 15:00:00 +0300
        t = rs.getTime(1, cGMT05);
        assertEquals(43200000L, t.getTime());        // 2005-01-01 07:00:00 -0500 -> 1970-01-01 07:00:00 -0500
        t = rs.getTime(1, cGMT13);
        assertEquals(-43200000L, t.getTime());       // 2005-01-02 01:00:00 +1300 -> 1970-01-01 01:00:00 +1300

        // timestamp: 2005-01-01 15:00:00
        t = rs.getTime(2);
        assertEquals(50400000L, t.getTime());        // 1970-01-01 15:00:00 +0100
        t = rs.getTime(2, cUTC);
        assertEquals(54000000L, t.getTime());        // 1970-01-01 15:00:00 +0000
        t = rs.getTime(2, cGMT03);
        assertEquals(43200000L, t.getTime());        // 1970-01-01 15:00:00 +0300
        t = rs.getTime(2, cGMT05);
        assertEquals(72000000L, t.getTime());        // 1970-01-01 15:00:00 -0500
        t = rs.getTime(2, cGMT13);
        assertEquals(7200000L, t.getTime());         // 1970-01-01 15:00:00 +1300

        // time: 15:00:00
        t = rs.getTime(3);
        assertEquals(50400000L, t.getTime());        // 1970-01-01 15:00:00 +0100
        t = rs.getTime(3, cUTC);
        assertEquals(54000000L, t.getTime());        // 1970-01-01 15:00:00 +0000
        t = rs.getTime(3, cGMT03);
        assertEquals(43200000L, t.getTime());        // 1970-01-01 15:00:00 +0300
        t = rs.getTime(3, cGMT05);
        assertEquals(72000000L, t.getTime());        // 1970-01-01 15:00:00 -0500
        t = rs.getTime(3, cGMT13);
        assertEquals(7200000L, t.getTime());         // 1970-01-01 15:00:00 +1300

        // timetz: 15:00:00+03
        t = rs.getTime(4);
        assertEquals(43200000L, t.getTime());        // 1970-01-01 13:00:00 +0100
        t = rs.getTime(4, cUTC);
        assertEquals(43200000L, t.getTime());        // 1970-01-01 12:00:00 +0000
        t = rs.getTime(4, cGMT03);
        assertEquals(43200000L, t.getTime());        // 1970-01-01 15:00:00 +0300
        t = rs.getTime(4, cGMT05);
        assertEquals(43200000L, t.getTime());        // 1970-01-01 07:00:00 -0500
        t = rs.getTime(4, cGMT13);
        assertEquals(43200000L, t.getTime());        // 1970-01-01 01:00:00 +1300
    }

    /**
     * This test is broken off from testSetTimestamp because it does not work
     * for pre-7.4 servers and putting tons of conditionals in that test makes
     * it largely unreadable.  The time data type does not accept timestamp
     * with time zone style input on these servers.
     */
    public void testSetTimestampOnTime() throws Exception
    {
        // Pre-7.4 servers cannot convert timestamps with timezones to times.
        if (!min74)
                return;

        PreparedStatement insertTimestamp = con.prepareStatement("INSERT INTO testtimezone(seq,t) VALUES (?,?)");
        int seq = 1;

        Timestamp instant = new Timestamp(1104580800000L); // 2005-01-01 12:00:00 UTC

        // +0100 (JVM default)
        insertTimestamp.setInt(1, seq++);
        insertTimestamp.setTimestamp(2,instant);  // 13:00:00
        insertTimestamp.executeUpdate();

        // UTC
        insertTimestamp.setInt(1, seq++);
        insertTimestamp.setTimestamp(2,instant,cUTC); // 12:00:00
        insertTimestamp.executeUpdate();

        // +0300
        insertTimestamp.setInt(1, seq++);
        insertTimestamp.setTimestamp(2,instant,cGMT03); // 15:00:00
        insertTimestamp.executeUpdate();

        // -0500
        insertTimestamp.setInt(1, seq++);
        insertTimestamp.setTimestamp(2,instant,cGMT05); // 07:00:00
        insertTimestamp.executeUpdate();

        if (min73) {
            // +1300
            insertTimestamp.setInt(1, seq++);
            insertTimestamp.setTimestamp(2,instant,cGMT13); // 01:00:00
            insertTimestamp.executeUpdate();
        }

        seq = 1;
        ResultSet rs = con.createStatement().executeQuery("SELECT seq,t FROM testtimezone ORDER BY seq");

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("13:00:00", rs.getString(2));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("12:00:00", rs.getString(2));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("15:00:00", rs.getString(2));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("07:00:00", rs.getString(2));
        
        if (min73) {
            assertTrue(rs.next());
            assertEquals(seq++, rs.getInt(1));
            assertEquals("01:00:00", rs.getString(2));
        }

        assertTrue(!rs.next());
    }


    public void testSetTimestamp() throws Exception
    {
        PreparedStatement insertTimestamp = con.prepareStatement("INSERT INTO testtimezone(seq,tstz,ts,tz,d) VALUES (?,?,?,?,?)");
        int seq = 1;

        Timestamp instant = new Timestamp(1104580800000L); // 2005-01-01 12:00:00 UTC

        // +0100 (JVM default)
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setTimestamp(2,instant);  // 2005-01-01 13:00:00 +0100
        insertTimestamp.setTimestamp(3,instant);  // 2005-01-01 13:00:00
        insertTimestamp.setTimestamp(4,instant);  // 13:00:00 +0100
        insertTimestamp.setTimestamp(5,instant);  // 2005-01-01
        insertTimestamp.executeUpdate();

        // UTC
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setTimestamp(2,instant,cUTC); // 2005-01-01 12:00:00 +0000
        insertTimestamp.setTimestamp(3,instant,cUTC); // 2005-01-01 12:00:00
        insertTimestamp.setTimestamp(4,instant,cUTC); // 12:00:00 +0000
        insertTimestamp.setTimestamp(5,instant,cUTC); // 2005-01-01
        insertTimestamp.executeUpdate();

        // +0300
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setTimestamp(2,instant,cGMT03); // 2005-01-01 15:00:00 +0300
        insertTimestamp.setTimestamp(3,instant,cGMT03); // 2005-01-01 15:00:00
        insertTimestamp.setTimestamp(4,instant,cGMT03); // 15:00:00 +0300
        insertTimestamp.setTimestamp(5,instant,cGMT03); // 2005-01-01
        insertTimestamp.executeUpdate();

        // -0500
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setTimestamp(2,instant,cGMT05); // 2005-01-01 07:00:00 -0500
        insertTimestamp.setTimestamp(3,instant,cGMT05); // 2005-01-01 07:00:00
        insertTimestamp.setTimestamp(4,instant,cGMT05); // 07:00:00 -0500
        insertTimestamp.setTimestamp(5,instant,cGMT05); // 2005-01-01
        insertTimestamp.executeUpdate();

        if (min73) {
            // +1300
            insertTimestamp.setInt(1,seq++);
            insertTimestamp.setTimestamp(2,instant,cGMT13); // 2005-01-02 01:00:00 +1300
            insertTimestamp.setTimestamp(3,instant,cGMT13); // 2005-01-02 01:00:00
            insertTimestamp.setTimestamp(4,instant,cGMT13); // 01:00:00 +1300
            insertTimestamp.setTimestamp(5,instant,cGMT13); // 2005-01-02
            insertTimestamp.executeUpdate();
        }

        insertTimestamp.close();

        //
        // check results
        //

        seq = 1;
        ResultSet rs = con.createStatement().executeQuery("SELECT seq,tstz,ts,tz,d FROM testtimezone ORDER BY seq");

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("2005-01-01 15:00:00+03", rs.getString(2));
        assertEquals("2005-01-01 13:00:00", rs.getString(3));
        assertEquals("13:00:00+01", rs.getString(4));
        assertEquals("2005-01-01", rs.getString(5));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("2005-01-01 15:00:00+03", rs.getString(2));
        assertEquals("2005-01-01 12:00:00", rs.getString(3));
        assertEquals("12:00:00+00", rs.getString(4));
        assertEquals("2005-01-01", rs.getString(5));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("2005-01-01 15:00:00+03", rs.getString(2));
        assertEquals("2005-01-01 15:00:00", rs.getString(3));
        assertEquals("15:00:00+03", rs.getString(4));
        assertEquals("2005-01-01", rs.getString(5));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("2005-01-01 15:00:00+03", rs.getString(2));
        assertEquals("2005-01-01 07:00:00", rs.getString(3));
        assertEquals("07:00:00-05", rs.getString(4));
        assertEquals("2005-01-01", rs.getString(5));

        if (min73) {
            assertTrue(rs.next());
            assertEquals(seq++, rs.getInt(1));
            assertEquals("2005-01-01 15:00:00+03", rs.getString(2));
            assertEquals("2005-01-02 01:00:00", rs.getString(3));
            assertEquals("01:00:00+13", rs.getString(4));
            assertEquals("2005-01-02", rs.getString(5));
        }

        assertTrue(!rs.next());
    }

    public void testSetDate() throws Exception
    {
        PreparedStatement insertTimestamp = con.prepareStatement("INSERT INTO testtimezone(seq,tstz,ts,d) VALUES (?,?,?,?)");

        int seq = 1;
        
        Date d;

        // +0100 (JVM default)
        d = new Date(1104534000000L);    // 2005-01-01 00:00:00 +0100
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setDate(2,d);    // 2005-01-01 00:00:00 +0100
        insertTimestamp.setDate(3,d);    // 2005-01-01 00:00:00
        insertTimestamp.setDate(4,d);    // 2005-01-01
        insertTimestamp.executeUpdate();

        // UTC
        d = new Date(1104537600000L);      // 2005-01-01 00:00:00 +0000
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setDate(2,d,cUTC); // 2005-01-01 00:00:00 +0000
        insertTimestamp.setDate(3,d,cUTC); // 2005-01-01 00:00:00
        insertTimestamp.setDate(4,d,cUTC); // 2005-01-01
        insertTimestamp.executeUpdate();

        // +0300
        d = new Date(1104526800000L);        // 2005-01-01 00:00:00 +0300
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setDate(2,d,cGMT03); // 2005-01-01 00:00:00 +0300
        insertTimestamp.setDate(3,d,cGMT03); // 2005-01-01 00:00:00
        insertTimestamp.setDate(4,d,cGMT03); // 2005-01-01
        insertTimestamp.executeUpdate();

        // -0500
        d = new Date(1104555600000L);        // 2005-01-01 00:00:00 -0500
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setDate(2,d,cGMT05); // 2005-01-01 00:00:00 -0500
        insertTimestamp.setDate(3,d,cGMT05); // 2005-01-01 00:00:00
        insertTimestamp.setDate(4,d,cGMT05); // 2005-01-01
        insertTimestamp.executeUpdate();

        if (min73) {
            // +1300
            d = new Date(1104490800000L);        // 2005-01-01 00:00:00 +1300
            insertTimestamp.setInt(1,seq++);
            insertTimestamp.setDate(2,d,cGMT13); // 2005-01-01 00:00:00 +1300
            insertTimestamp.setDate(3,d,cGMT13); // 2005-01-01 00:00:00
            insertTimestamp.setDate(4,d,cGMT13); // 2005-01-01
            insertTimestamp.executeUpdate();
        }
        
        insertTimestamp.close();

        //
        // check results
        //

        seq = 1;
        ResultSet rs = con.createStatement().executeQuery("SELECT seq,tstz,ts,d FROM testtimezone ORDER BY seq");

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("2005-01-01 02:00:00+03", rs.getString(2));
        assertEquals("2005-01-01 00:00:00", rs.getString(3));
        assertEquals("2005-01-01", rs.getString(4));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("2005-01-01 03:00:00+03", rs.getString(2));
        assertEquals("2005-01-01 00:00:00", rs.getString(3));
        assertEquals("2005-01-01", rs.getString(4));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("2005-01-01 00:00:00+03", rs.getString(2));
        assertEquals("2005-01-01 00:00:00", rs.getString(3));
        assertEquals("2005-01-01", rs.getString(4));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("2005-01-01 08:00:00+03", rs.getString(2));
        assertEquals("2005-01-01 00:00:00", rs.getString(3));
        assertEquals("2005-01-01", rs.getString(4));

        if (min73) {
            assertTrue(rs.next());
            assertEquals(seq++, rs.getInt(1));
            assertEquals("2004-12-31 14:00:00+03", rs.getString(2));
            assertEquals("2005-01-01 00:00:00", rs.getString(3));
            assertEquals("2005-01-01", rs.getString(4));
        }

        assertTrue(!rs.next());        
    }

    public void testSetTime() throws Exception
    {
        if (!min74) {
            // We can't do timezones properly for time/timetz values before 7.4
            System.err.println("Skipping TimezoneTest.testSetTime on a pre-7.4 server");
            return;
        }

        PreparedStatement insertTimestamp = con.prepareStatement("INSERT INTO testtimezone(seq,t,tz) VALUES (?,?,?)");

        int seq = 1;
        
        Time t;

        // +0100 (JVM default)
        t = new Time(50400000L);         // 1970-01-01 15:00:00 +0100
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setTime(2,t);    // 15:00:00
        insertTimestamp.setTime(3,t);    // 15:00:00+03
        insertTimestamp.executeUpdate();

        // UTC
        t = new Time(54000000L);           // 1970-01-01 15:00:00 +0000
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setTime(2,t,cUTC); // 15:00:00
        insertTimestamp.setTime(3,t,cUTC); // 15:00:00+00
        insertTimestamp.executeUpdate();

        // +0300
        t = new Time(43200000L);             // 1970-01-01 15:00:00 +0300
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setTime(2,t,cGMT03); // 15:00:00
        insertTimestamp.setTime(3,t,cGMT03); // 15:00:00+03
        insertTimestamp.executeUpdate();

        // -0500
        t = new Time(72000000L);             // 1970-01-01 15:00:00 -0500
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setTime(2,t,cGMT05); // 15:00:00
        insertTimestamp.setTime(3,t,cGMT05); // 15:00:00-05
        insertTimestamp.executeUpdate();

        // +1300
        t = new Time(7200000L);              // 1970-01-01 15:00:00 +1300
        insertTimestamp.setInt(1,seq++);
        insertTimestamp.setTime(2,t,cGMT13); // 15:00:00
        insertTimestamp.setTime(3,t,cGMT13); // 15:00:00+13
        insertTimestamp.executeUpdate();
        
        insertTimestamp.close();

        //
        // check results
        //

        seq = 1;
        ResultSet rs = con.createStatement().executeQuery("SELECT seq,t,tz FROM testtimezone ORDER BY seq");

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("15:00:00", rs.getString(2));
        assertEquals("15:00:00+01", rs.getString(3));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("15:00:00", rs.getString(2));
        assertEquals("15:00:00+00", rs.getString(3));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("15:00:00", rs.getString(2));
        assertEquals("15:00:00+03", rs.getString(3));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("15:00:00", rs.getString(2));
        assertEquals("15:00:00-05", rs.getString(3));

        assertTrue(rs.next());
        assertEquals(seq++, rs.getInt(1));
        assertEquals("15:00:00", rs.getString(2));
        assertEquals("15:00:00+13", rs.getString(3));

        assertTrue(!rs.next());        
    }

    public void testHalfHourTimezone() throws Exception
    {
        // Check our parser handles a timezone like e.g. 03:30 correctly
        ResultSet rs = con.createStatement().executeQuery("SELECT '1969-12-31 20:30:00-03:30'::text");

        assertTrue(rs.next());
        assertEquals(0L, rs.getTimestamp(1).getTime());
    }
}
