001: // jTDS JDBC Driver for Microsoft SQL Server and Sybase
002: // Copyright (C) 2005 The jTDS Project
003: //
004: // This library is free software; you can redistribute it and/or
005: // modify it under the terms of the GNU Lesser General Public
006: // License as published by the Free Software Foundation; either
007: // version 2.1 of the License, or (at your option) any later version.
008: //
009: // This library is distributed in the hope that it will be useful,
010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
012: // Lesser General Public License for more details.
013: //
014: // You should have received a copy of the GNU Lesser General Public
015: // License along with this library; if not, write to the Free Software
016: // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
017: //
018: package net.sourceforge.jtds.test;
019:
020: import java.sql.*;
021: import java.util.GregorianCalendar;
022: import java.util.TimeZone;
023:
024: import net.sourceforge.jtds.jdbc.Support;
025:
026: /**
027: * Tests timezone conversions when setting and getting data to and from the
028: * database.
029: *
030: * @author Mike Hutchinson
031: */
032: public class TimeZoneTest extends TestBase {
033:
034: public TimeZoneTest(String name) {
035: super (name);
036: }
037:
038: /**
039: * Test timezone calendar conversions. This test produces the same results
040: * when run with both jConnect 6.0 and the MS JDBC driver.
041: */
042: public void testTimeZone() throws Exception {
043: TimeZone zone = TimeZone.getDefault();
044: try {
045: Statement stmt = con.createStatement();
046: stmt
047: .execute("CREATE TABLE #TEST (d datetime, t datetime, ts datetime)");
048: PreparedStatement pstmt = con
049: .prepareStatement("INSERT INTO #TEST VALUES(?, ?, ?) ");
050: TimeZone tz1 = TimeZone.getTimeZone("America/New_York");
051: GregorianCalendar calNY = new GregorianCalendar(tz1);
052: GregorianCalendar originalCalNY = new GregorianCalendar(tz1);
053: originalCalNY.setTime(calNY.getTime());
054: //
055: // Store date/times with local time zone
056: //
057: Date date = Date.valueOf("2005-04-06");
058: Time time = Time.valueOf("09:55:30");
059: Timestamp ts = Timestamp.valueOf("2005-04-06 09:55:30.123");
060: pstmt.setDate(1, date);
061: pstmt.setTime(2, time);
062: pstmt.setTimestamp(3, ts);
063: assertEquals(1, pstmt.executeUpdate());
064: //
065: // Store date/times with other time zone
066: //
067: pstmt.setDate(1, date, calNY);
068: assertEquals(originalCalNY, calNY);
069: pstmt.setTime(2, time, calNY);
070: assertEquals(originalCalNY, calNY);
071: pstmt.setTimestamp(3, ts, calNY);
072: assertEquals(originalCalNY, calNY);
073: assertEquals(1, pstmt.executeUpdate());
074: assertEquals(1, pstmt.executeUpdate());
075: //
076: // Read back
077: //
078: ResultSet rs = stmt.executeQuery("SELECT * FROM #TEST");
079: assertTrue(rs.next());
080: //
081: // Check local time zone gets back what we stored
082: //
083: assertEquals("2005-04-06", rs.getDate(1).toString());
084: assertEquals("09:55:30", rs.getTime(2).toString());
085: assertEquals("2005-04-06 09:55:30.123", rs.getTimestamp(3)
086: .toString());
087: assertTrue(rs.next());
088: //
089: // Check date/times stored with other zone are changed when read
090: // back with local.
091: // The date changes because the JDBC Date has time set to 0:0:0 so
092: // the change of zone moves us back a day.
093: // The time moves for me because the JDBC Time has the date set to
094: // 1970-01-01 and in 1970 the "europe/london" time zone was
095: // experimenting with permanent daylight saving time! If you are
096: // running this test anywhere other than the UK you will find that
097: // the time is the same as the time component of the timestamp.
098: // Note both the other drivers I tested exhibit the same behaviour.
099: //
100: assertEquals(new Date(Support.timeFromZone(date, calNY))
101: .toString(), rs.getDate(1).toString());
102: assertEquals(originalCalNY, calNY);
103: assertEquals(new Time(Support.timeFromZone(time, calNY))
104: .toString(), rs.getTime(2).toString());
105: assertEquals(originalCalNY, calNY);
106: assertEquals(new Timestamp(Support.timeFromZone(ts, calNY))
107: .toString(), rs.getTimestamp(3).toString());
108: assertEquals(originalCalNY, calNY);
109: assertTrue(rs.next());
110: //
111: // Check date/times stored with other zone are unchanged when read
112: // back with other zone
113: //
114: assertEquals("2005-04-05", rs.getDate(1, calNY).toString());
115: assertEquals(originalCalNY, calNY);
116: assertEquals("09:55:30", rs.getTime(2, calNY).toString());
117: assertEquals(originalCalNY, calNY);
118: assertEquals("2005-04-06 09:55:30.123", rs.getTimestamp(3,
119: calNY).toString());
120: assertEquals(originalCalNY, calNY);
121: } finally {
122: TimeZone.setDefault(zone);
123: }
124: }
125:
126: public static void main(String[] args) {
127: junit.textui.TestRunner.run(TimeZoneTest.class);
128: }
129: }
|