datetime.out

derby database source code.good for you.

ij> --
-- Test the builtin date/time types
-- assumes these builtin types exist:
--	int, smallint, char, varchar, real
--
-- other things we might test:
-- interaction with UUID and other user defined types
-- compatibility with dynamic parameters and JDBC getDate etc. methods
--
-- Test the arithmetic operators
--
create table t (i int, s smallint, c char(10), v varchar(50), 
	d double precision, r real, e date, t time, p timestamp);
0 rows inserted/updated/deleted
ij> insert into t values (null, null, null, null, null, null, null, null, null);
1 row inserted/updated/deleted
ij> insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0,
	300.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx));
1 row inserted/updated/deleted
ij> insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0,
	-300.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx));
1 row inserted/updated/deleted
ij> -- date/times don't support math, show each combination
select e + e from t;
ERROR 42Y95: The '+' operator with a left operand type of 'DATE' and a right operand type of 'DATE' is not supported.
ij> select i + e from t;
ERROR 42Y95: The '+' operator with a left operand type of 'INTEGER' and a right operand type of 'DATE' is not supported.
ij> select p / p from t;
ERROR 42Y95: The '/' operator with a left operand type of 'TIMESTAMP' and a right operand type of 'TIMESTAMP' is not supported.
ij> select p * s from t;
ERROR 42Y95: The '*' operator with a left operand type of 'TIMESTAMP' and a right operand type of 'SMALLINT' is not supported.
ij> select t - t from t;
ERROR 42Y95: The '-' operator with a left operand type of 'TIME' and a right operand type of 'TIME' is not supported.
ij> select -t from t;
ERROR 42X37: The unary '-' operator is not allowed on the 'TIME' type.
ij> select +e from t;
ERROR 42X37: The unary '+' operator is not allowed on the 'DATE' type.
ij> --
-- comparisons
--
-- select each one in turn
-- each pair gets the same result
select e from t where e = date('1992-01-01');
E         
----------
1992-01-01
1992-01-01
ij> select e from t where date('1992-01-01') = e;
E         
----------
1992-01-01
1992-01-01
ij> select t from t where t > time('09:30:15');
T       
--------
12:30:30
12:30:30
ij> select t from t where time('09:30:15') < t;
T       
--------
12:30:30
12:30:30
ij> select p from t where p < timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx);
P                         
--------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx
xxxxxxFILTERED-TIMESTAMPxxxxx
ij> select p from t where timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)> p;
P                         
--------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx
xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- now look for a value that isn't in the table
select e from t where e <> date('1992-01-01');
E         
----------
ij> select e from t where date('1992-01-01') <> e;
E         
----------
ij> -- now test null = null semantics
select e, t, p from t where e = e or t = t or p = p;
E         |T       |P                         
----------------------------------------------
1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx
1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- now test <=, >=, <>
-- each pair gets the same result
select e from t where e >= date('1990-01-01');
E         
----------
1992-01-01
1992-01-01
ij> select e from t where date('1990-01-01')<= e;
E         
----------
1992-01-01
1992-01-01
ij> select t from t where t <= time('09:30:15');
T       
--------
ij> select t from t where time('09:30:15') >= t;
T       
--------
ij> select p from t where p <> timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx);
P                         
--------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx
xxxxxxFILTERED-TIMESTAMPxxxxx
ij> select p from t where timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)<> p;
P                         
--------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx
xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- show comparisons with mixed types don't work
select e from t where e <= i;
ERROR 42818: Comparisons between 'DATE' and 'INTEGER' are not supported.
ij> select e from t where t < s;
ERROR 42818: Comparisons between 'TIME' and 'SMALLINT' are not supported.
ij> select e from t where p > d;
ERROR 42818: Comparisons between 'TIMESTAMP' and 'DOUBLE' are not supported.
ij> select e from t where e >= t;
ERROR 42818: Comparisons between 'DATE' and 'TIME' are not supported.
ij> select e from t where t <> p;
ERROR 42818: Comparisons between 'TIME' and 'TIMESTAMP' are not supported.
ij> select e from t where p = e;
ERROR 42818: Comparisons between 'TIMESTAMP' and 'DATE' are not supported.
ij> -- check limit values
values( date('0001-1-1'), date('9999-12-31'), date('2/29/2000'), date('29.2.2004'));
1         |2         |3         |4         
-------------------------------------------
0001-01-01|9999-12-31|2000-02-29|2004-02-29
ij> values( time('00:00:00'), time('23:59:59'));
1       |2       
-----------------
00:00:00|23:59:59
ij> values( time('00 AM'), time( '12:59 AM'), time('1 PM'), time('12:59 PM'));
1       |2       |3       |4       
-----------------------------------
00:00:00|00:59:00|13:00:00|12:59:00
ij> values( time('00.00.00'), time('23.59.59'), time('24.00.00'));
1       |2       |3       
--------------------------
00:00:00|23:59:59|00:00:00
ij> values( timestamp('0001-1-1 00:00:00'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx'));
1                         |2                         
-----------------------------------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- show that overflow and underflow are not allowed
-- (SQL92 would have these report errors)
values( date('0000-01-01'));
ERROR 22008: '0000-01-01' is an invalid argument to the date function.
ij> values( date('2000-00-01'));
ERROR 22008: '2000-00-01' is an invalid argument to the date function.
ij> values( date('2000-01-00'));
ERROR 22008: '2000-01-00' is an invalid argument to the date function.
ij> values( date('10000-01-01'));
ERROR 22008: '10000-01-01' is an invalid argument to the date function.
ij> values( date('2000-13-01'));
ERROR 22008: '2000-13-01' is an invalid argument to the date function.
ij> values( date('2000-01-32'));
ERROR 22008: '2000-01-32' is an invalid argument to the date function.
ij> values( date('1900-02-29'));
ERROR 22008: '1900-02-29' is an invalid argument to the date function.
ij> values( date('2001-02-29'));
ERROR 22008: '2001-02-29' is an invalid argument to the date function.
ij> values( time('25.00.00'));
ERROR 22007: The string representation of a datetime value is out of range.
ij> values( time('24.00.01'));
ERROR 22007: The string representation of a datetime value is out of range.
ij> values( time('0:60:00'));
ERROR 22007: The string representation of a datetime value is out of range.
ij> values( time('00:00:60'));
ERROR 22007: The string representation of a datetime value is out of range.
ij> -- show garbage in == errors out
select date( 'xxxx') from t where p is null;
ERROR 22008: 'xxxx' is an invalid argument to the date function.
ij> select time( '') from t where p is null;
ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
ij> select timestamp( 'is there anything here?' )from t where p is null;
ERROR 22008: 'is there anything here?' is an invalid argument to the timestamp function.
ij> select timestamp( '1992-01- there anything here?' )from t where p is null;
ERROR 22008: '1992-01- there anything here?' is an invalid argument to the timestamp function.
ij> select timestamp( '--::' )from t where p is null;
ERROR 22008: '--::' is an invalid argument to the timestamp function.
ij> select time('::::') from t where p is null;
ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
ij> -- show is not null at work
select * from t 
where e is not null
and t is not null
and p is not null;
I          |S     |C         |V                                                 |D                     |R            |E         |T       |P                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
0          |100   |hello     |everyone is here                                  |200.0                 |300.0        |1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx
-1         |-100  |goodbye   |everyone is there                                 |-200.0                |-300.0       |1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- test =SQ
-- this gets cardinality error
select 'fail' from t where e = (select e from t);
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- this works
select 'pass' from t where e = (select e from t where d=200);
1   
----
pass
pass
ij> -- this gets cardinality error
select 'fail' from t where t = (select t from t);
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- this works
select 'pass' from t where t = (select t from t where d=200);
1   
----
pass
pass
ij> -- this gets cardinality error
select 'fail' from t where p = (select p from t);
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- this works
select 'pass' from t where p = (select p from t where d=200);
1   
----
pass
ij> drop table t;
0 rows inserted/updated/deleted
ij> --
-- test syntax: precision cannot be specified
--
create table wrong (t time(-100));
ERROR 42X01: Syntax error: Encountered "(" at line 4, column 27.
ij> create table wrong (t time(0));
ERROR 42X01: Syntax error: Encountered "(" at line 1, column 27.
ij> create table wrong (t time(23));
ERROR 42X01: Syntax error: Encountered "(" at line 1, column 27.
ij> create table wrong (t timestamp(-100));
ERROR 42X01: Syntax error: Encountered "(" at line 1, column 32.
ij> create table wrong (t timestamp(0));
ERROR 42X01: Syntax error: Encountered "(" at line 1, column 32.
ij> create table wrong (t timestamp(6));
ERROR 42X01: Syntax error: Encountered "(" at line 1, column 32.
ij> create table wrong (t timestamp(9));
ERROR 42X01: Syntax error: Encountered "(" at line 1, column 32.
ij> create table wrong (t timestamp(23));
ERROR 42X01: Syntax error: Encountered "(" at line 1, column 32.
ij> --
-- test a variety of inserts and updates
--
create table source (i int, s smallint, c char(10), v varchar(50), 
	d double precision, r real, e date, t time, p timestamp);
0 rows inserted/updated/deleted
ij> create table target (e date not null, t time not null, p timestamp not null);
0 rows inserted/updated/deleted
ij> -- we have already tested inserting literals.
insert into source values (1, 2, '3', '4', 5, 6, date('1997-07-07'),
	time('08:08:08'),timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx));
1 row inserted/updated/deleted
ij> -- these work:
insert into target select e,t,p from source;
1 row inserted/updated/deleted
ij> -- these will all fail:
insert into target select p,e,t from source;
ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIMESTAMP'. 
ij> insert into target select i,s,d from source;
ERROR 42821: Columns of type 'DATE' cannot hold values of type 'INTEGER'. 
ij> insert into target (t,p) select c,r from source;
ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'REAL'. 
ij> delete from source;
1 row inserted/updated/deleted
ij> insert into source values (null, null, null, null, null, null, null, null, null);
1 row inserted/updated/deleted
ij> -- these fail because the target won't take a null -- of any type
insert into target values(null, null, null);
ERROR 23502: Column 'E'  cannot accept a NULL value.
ij> insert into target select e,t,p from source;
ERROR 23502: Column 'E'  cannot accept a NULL value.
ij> -- these still fail with type errors:
insert into target select p,e,t from source;
ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIMESTAMP'. 
ij> insert into target select i,s,d from source;
ERROR 42821: Columns of type 'DATE' cannot hold values of type 'INTEGER'. 
ij> insert into target (t,p)select c,r from source;
ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'REAL'. 
ij> -- expect 1 row in target:
select * from target;
E         |T       |P                         
----------------------------------------------
1997-07-07|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- unchanged:
update target set e = e, t = t, p = p;
1 row inserted/updated/deleted
ij> select * from target;
E         |T       |P                         
----------------------------------------------
1997-07-07|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- alters the row:
update target set e = date('1990-01-01');
1 row inserted/updated/deleted
ij> select * from target;
E         |T       |P                         
----------------------------------------------
1990-01-01|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- not settable to null
update target set e = null;
ERROR 23502: Column 'E'  cannot accept a NULL value.
ij> select * from target;