2
Copyright (C) 2002-2004 MySQL AB
4
This program is free software; you can redistribute it and/or modify
5
it under the terms of version 2 of the GNU General Public License as
6
published by the Free Software Foundation.
8
There are special exceptions to the terms and conditions of the GPL
9
as it is applied to this software. View the full text of the
10
exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
11
software distribution.
13
This program is distributed in the hope that it will be useful,
14
but WITHOUT ANY WARRANTY; without even the implied warranty of
15
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16
GNU General Public License for more details.
18
You should have received a copy of the GNU General Public License
19
along with this program; if not, write to the Free Software
20
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
25
package testsuite.simple;
27
import testsuite.BaseTestCase;
29
import java.sql.Connection;
31
import java.sql.PreparedStatement;
32
import java.sql.SQLException;
33
import java.sql.Statement;
35
import java.sql.Timestamp;
37
import java.text.DateFormat;
38
import java.text.SimpleDateFormat;
40
import java.util.Calendar;
41
import java.util.Locale;
42
import java.util.Properties;
43
import java.util.TimeZone;
45
import com.mysql.jdbc.SQLError;
49
* @author Mark Matthews
52
public class DateTest extends BaseTestCase {
54
// -----------------------------------------------------------
57
* Creates a new DateTest object.
62
public DateTest(String name) {
67
// ----------------------------------------------------------------
70
* Runs all test cases in this test suite
74
public static void main(String[] args) {
75
junit.textui.TestRunner.run(DateTest.class);
84
public void setUp() throws Exception {
92
* @throws SQLException
95
public void testTimestamp() throws SQLException {
96
this.pstmt = this.conn
97
.prepareStatement("INSERT INTO DATETEST(tstamp, dt, dtime, tm) VALUES (?, ?, ?, ?)");
99
// TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
100
Calendar cal = Calendar.getInstance();
101
cal.set(Calendar.MONTH, 6);
102
cal.set(Calendar.DAY_OF_MONTH, 3);
103
cal.set(Calendar.YEAR, 2002);
104
cal.set(Calendar.HOUR, 7);
105
cal.set(Calendar.MINUTE, 0);
106
cal.set(Calendar.SECOND, 0);
107
cal.set(Calendar.MILLISECOND, 0);
108
cal.set(Calendar.AM_PM, Calendar.AM);
110
System.out.println(cal);
112
// DateFormat df = SimpleDateFormat.getInstance();
113
DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss z");
115
Calendar calGMT = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
116
// df.setTimeZone(TimeZone.getTimeZone("GMT"));
117
Timestamp nowTstamp = new Timestamp(cal.getTime().getTime());
118
java.sql.Date nowDate = new java.sql.Date(cal.getTime().getTime());
119
Timestamp nowDatetime = new Timestamp(cal.getTime().getTime());
120
java.sql.Time nowTime = new java.sql.Time(cal.getTime().getTime());
122
.println("** Times with given calendar (before storing) **\n");
123
System.out.println("TIMESTAMP:\t" + nowTstamp.getTime() + " -> "
124
+ df.format(nowTstamp));
125
System.out.println("DATE:\t\t" + nowDate.getTime() + " -> "
126
+ df.format(nowDate));
127
System.out.println("DATETIME:\t" + nowDatetime.getTime() + " -> "
128
+ df.format(nowDatetime));
129
System.out.println("DATE:\t\t" + nowDate.getTime() + " -> "
130
+ df.format(nowDate));
131
System.out.println("TIME:\t\t" + nowTime.getTime() + " -> "
132
+ df.format(nowTime));
133
System.out.println("\n");
134
this.pstmt.setTimestamp(1, nowTstamp, calGMT);
135
// have to use the same TimeZone as used to create or there will be
137
this.pstmt.setDate(2, nowDate, cal);
138
this.pstmt.setTimestamp(3, nowDatetime, calGMT);
139
// have to use the same TimeZone as used to create or there will be
141
this.pstmt.setTime(4, nowTime, cal);
142
this.pstmt.execute();
144
this.pstmt.getUpdateCount();
145
this.pstmt.clearParameters();
146
this.rs = this.stmt.executeQuery("SELECT * from DATETEST");
148
java.sql.Date thenDate = null;
150
while (this.rs.next()) {
151
Timestamp thenTstamp = this.rs.getTimestamp(1, calGMT);
152
thenDate = this.rs.getDate(2, cal);
154
java.sql.Timestamp thenDatetime = this.rs.getTimestamp(3, calGMT);
156
java.sql.Time thenTime = this.rs.getTime(4, cal);
158
.println("** Times with given calendar (retrieved from database) **\n");
159
System.out.println("TIMESTAMP:\t" + thenTstamp.getTime() + " -> "
160
+ df.format(thenTstamp));
161
System.out.println("DATE:\t\t" + thenDate.getTime() + " -> "
162
+ df.format(thenDate));
163
System.out.println("DATETIME:\t" + thenDatetime.getTime() + " -> "
164
+ df.format(thenDatetime));
165
System.out.println("TIME:\t\t" + thenTime.getTime() + " -> "
166
+ df.format(thenTime));
167
System.out.println("\n");
174
public void testNanosParsing() throws SQLException {
176
this.stmt.executeUpdate("DROP TABLE IF EXISTS testNanosParsing");
178
.executeUpdate("CREATE TABLE testNanosParsing (dateIndex int, field1 VARCHAR(32))");
180
.executeUpdate("INSERT INTO testNanosParsing VALUES (1, '1969-12-31 18:00:00.0'), "
181
+ "(2, '1969-12-31 18:00:00.90'), "
182
+ "(3, '1969-12-31 18:00:00.900'), "
183
+ "(4, '1969-12-31 18:00:00.9000'), "
184
+ "(5, '1969-12-31 18:00:00.90000'), "
185
+ "(6, '1969-12-31 18:00:00.900000'), "
186
+ "(7, '1969-12-31 18:00:00.')");
189
.executeQuery("SELECT field1 FROM testNanosParsing ORDER BY dateIndex ASC");
190
assertTrue(this.rs.next());
191
assertTrue(this.rs.getTimestamp(1).getNanos() == 0);
192
assertTrue(this.rs.next());
193
assertTrue(this.rs.getTimestamp(1).getNanos() + " != 90", this.rs
194
.getTimestamp(1).getNanos() == 90);
195
assertTrue(this.rs.next());
196
assertTrue(this.rs.getTimestamp(1).getNanos() + " != 900", this.rs
197
.getTimestamp(1).getNanos() == 900);
198
assertTrue(this.rs.next());
199
assertTrue(this.rs.getTimestamp(1).getNanos() + " != 9000", this.rs
200
.getTimestamp(1).getNanos() == 9000);
201
assertTrue(this.rs.next());
202
assertTrue(this.rs.getTimestamp(1).getNanos() + " != 90000",
203
this.rs.getTimestamp(1).getNanos() == 90000);
204
assertTrue(this.rs.next());
205
assertTrue(this.rs.getTimestamp(1).getNanos() + " != 900000",
206
this.rs.getTimestamp(1).getNanos() == 900000);
207
assertTrue(this.rs.next());
210
this.rs.getTimestamp(1);
211
} catch (SQLException sqlEx) {
212
assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
216
this.stmt.executeUpdate("DROP TABLE IF EXISTS testNanosParsing");
220
private void createTestTable() throws SQLException {
222
// Catch the error, the table might exist
225
this.stmt.executeUpdate("DROP TABLE DATETEST");
226
} catch (SQLException SQLE) {
231
.executeUpdate("CREATE TABLE DATETEST (tstamp TIMESTAMP, dt DATE, dtime DATETIME, tm TIME)");
235
* Tests the configurability of all-zero date/datetime/timestamp handling in
241
public void testZeroDateBehavior() throws Exception {
244
.executeUpdate("DROP TABLE IF EXISTS testZeroDateBehavior");
246
.executeUpdate("CREATE TABLE testZeroDateBehavior(fieldAsString VARCHAR(32), fieldAsDateTime DATETIME)");
248
.executeUpdate("INSERT INTO testZeroDateBehavior VALUES ('0000-00-00 00:00:00', '0000-00-00 00:00:00')");
249
Properties props = new Properties();
250
props.setProperty("zeroDateTimeBehavior", "round");
251
Connection roundConn = getConnectionWithProps(props);
252
Statement roundStmt = roundConn.createStatement();
254
.executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
257
assertEquals("0001-01-01", this.rs.getDate(1).toString());
258
assertEquals("0001-01-01 00:00:00.0",
259
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this.rs.getTimestamp(1)));
260
assertEquals("0001-01-01", this.rs.getDate(2).toString());
261
assertEquals("0001-01-01 00:00:00.0",
262
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this.rs.getTimestamp(2)));
264
PreparedStatement roundPrepStmt = roundConn
265
.prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
266
this.rs = roundPrepStmt.executeQuery();
269
assertEquals("0001-01-01", this.rs.getDate(1).toString());
270
assertEquals("0001-01-01 00:00:00.0",
271
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this.rs.getTimestamp(1)));
272
assertEquals("0001-01-01", this.rs.getDate(2).toString());
273
assertEquals("0001-01-01 00:00:00.0",
274
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this.rs.getTimestamp(2)));
276
props = new Properties();
277
props.setProperty("zeroDateTimeBehavior", "convertToNull");
278
Connection nullConn = getConnectionWithProps(props);
279
Statement nullStmt = nullConn.createStatement();
281
.executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
285
assertTrue(null == this.rs.getDate(1));
286
assertTrue(null == this.rs.getTimestamp(1));
287
assertTrue(null == this.rs.getDate(2));
288
assertTrue(null == this.rs.getTimestamp(2));
290
PreparedStatement nullPrepStmt = nullConn
291
.prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
292
this.rs = nullPrepStmt.executeQuery();
296
assertTrue(null == this.rs.getDate(1));
297
assertTrue(null == this.rs.getTimestamp(1));
298
assertTrue(null == this.rs.getDate(2));
299
assertTrue(null == this.rs.getTimestamp(2));
300
assertTrue(null == this.rs.getString(2));
302
props = new Properties();
303
props.setProperty("zeroDateTimeBehavior", "exception");
304
Connection exceptionConn = getConnectionWithProps(props);
305
Statement exceptionStmt = exceptionConn.createStatement();
306
this.rs = exceptionStmt
307
.executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
313
fail("Exception should have been thrown when trying to retrieve invalid date");
314
} catch (SQLException sqlEx) {
315
assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
320
this.rs.getTimestamp(1);
321
fail("Exception should have been thrown when trying to retrieve invalid date");
322
} catch (SQLException sqlEx) {
323
assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
329
fail("Exception should have been thrown when trying to retrieve invalid date");
330
} catch (SQLException sqlEx) {
331
assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
336
this.rs.getTimestamp(2);
337
fail("Exception should have been thrown when trying to retrieve invalid date");
338
} catch (SQLException sqlEx) {
339
assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
343
PreparedStatement exceptionPrepStmt = exceptionConn
344
.prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
347
this.rs = exceptionPrepStmt.executeQuery();
350
fail("Exception should have been thrown when trying to retrieve invalid date");
351
} catch (SQLException sqlEx) {
352
assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
358
.executeUpdate("DROP TABLE IF EXISTS testZeroDateBehavior");
362
public void testReggieBug() throws Exception {
364
this.stmt.executeUpdate("DROP TABLE IF EXISTS testReggieBug");
365
this.stmt.executeUpdate("CREATE TABLE testReggieBug (field1 DATE)");
367
PreparedStatement pStmt = this.conn
368
.prepareStatement("INSERT INTO testReggieBug VALUES (?)");
369
pStmt.setDate(1, new Date(2004 - 1900, 07, 28));
370
pStmt.executeUpdate();
371
this.rs = this.stmt.executeQuery("SELECT * FROM testReggieBug");
373
System.out.println(this.rs.getDate(1));
374
this.rs = this.conn.prepareStatement("SELECT * FROM testReggieBug")
377
System.out.println(this.rs.getDate(1));
380
this.stmt.executeUpdate("DROP TABLE IF EXISTS testReggieBug");
384
public void testNativeConversions() throws Exception {
385
Timestamp ts = new Timestamp(System.currentTimeMillis());
386
Date dt = new Date(ts.getTime());
387
Time tm = new Time(ts.getTime());
389
createTable("testNativeConversions", "(time_field TIME, date_field DATE, datetime_field DATETIME, timestamp_field TIMESTAMP)");
390
this.pstmt = this.conn.prepareStatement("INSERT INTO testNativeConversions VALUES (?,?,?,?)");
391
this.pstmt.setTime(1, tm);
392
this.pstmt.setDate(2, dt);
393
this.pstmt.setTimestamp(3, ts);
394
this.pstmt.setTimestamp(4, ts);
395
this.pstmt.execute();
398
this.pstmt = this.conn.prepareStatement("SELECT time_field, date_field, datetime_field, timestamp_field FROM testNativeConversions");
399
this.rs = this.pstmt.executeQuery();
400
assertTrue(this.rs.next());
401
System.out.println(this.rs.getTime(1));
402
System.out.println(this.rs.getTime(2));
403
System.out.println(this.rs.getTime(3));
404
System.out.println(this.rs.getTime(4));
405
System.out.println();
406
System.out.println(this.rs.getDate(1));
407
System.out.println(this.rs.getDate(2));
408
System.out.println(this.rs.getDate(3));
409
System.out.println(this.rs.getDate(4));
410
System.out.println();
411
System.out.println(this.rs.getTimestamp(1));
412
System.out.println(this.rs.getTimestamp(2));
413
System.out.println(this.rs.getTimestamp(3));
414
System.out.println(this.rs.getTimestamp(4));
added
removed
src/testsuite/simple/DateTest.java
