datetime.out

derby database source code.good for you.

E         |T       |P                         
----------------------------------------------
1990-01-01|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- nullable col can be set to null:
update source set e = date('1492-10-01');
1 row inserted/updated/deleted
ij> select e from source;
E         
----------
1492-10-01
ij> update source set e = null;
1 row inserted/updated/deleted
ij> select e from source;
E         
----------
NULL      
ij> -- these should get type errors
update target set e = 1;
ERROR 42821: Columns of type 'DATE' cannot hold values of type 'INTEGER'. 
ij> update source set p = 1.4e10;
ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'DOUBLE'. 
ij> update source set i = date('1001-01-01');
ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'DATE'. 
ij> -- tests with current functions:
delete from source;
1 row inserted/updated/deleted
ij> delete from target;
1 row inserted/updated/deleted
ij> insert into source values (1, 2, '3', '4', 5, 6, date('1997-06-07'),
	time('08:08:08'),timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx));
1 row inserted/updated/deleted
ij> -- these tests are 'funny' so that the masters won't show a diff
-- every time.
select 'pass' from source 
where current_date = current_date
and current_time = current_time
and current_timestamp = current_timestamp;
1   
----
pass
ij> select 'pass' from source 
where current_date > date('1996-12-31')
and current_time <= time(	'23:59:59') -- may oopsie on leap second days
and current_timestamp <> timestamp( -- this comment is just more whitespace
    'xxxxxxFILTERED-TIMESTAMPxxxxx);
1   
----
pass
ij> -- test with DB2 compatible syntax
select 'pass' from source 
where current date = current date
and current time = current time
and current timestamp = current timestamp;
1   
----
pass
ij> select 'pass' from source 
where current date > date('1996-12-31')
and current time <= time(	'23:59:59') -- may oopsie on leap second days
and current timestamp <> timestamp( -- this comment is just more whitespace
    'xxxxxxFILTERED-TIMESTAMPxxxxx);
1   
----
pass
ij> -- test escaped functions
-- CURRENT_DATE escaped function not supported in DB2 UDB
-- CURRENT_TIME escaped function not supported in DB2 UDB
select 'pass' from source 
where current_date = {fn current_date()}
and current_time = {fn current_time()}
and current_timestamp = current_timestamp;
ERROR 42X01: Syntax error: Encountered "current_date" at line 5, column 26.
ij> select 'pass' from source 
where current_date = {fn curdate()}
and current_time = {fn curtime()}
and current_timestamp = current_timestamp;
1   
----
pass
ij> -- current_date() and current_time() not valid in DB2. curdate() and curtime()
-- are as escaped functions only.
values curdate();
ERROR 42Y03: 'CURDATE' is not recognized as a function or procedure.
ij> values curtime();
ERROR 42Y03: 'CURTIME' is not recognized as a function or procedure.
ij> values current_date();
ERROR 42X01: Syntax error: Encountered "(" at line 1, column 20.
ij> values current_time();
ERROR 42X01: Syntax error: Encountered "(" at line 1, column 20.
ij> values {fn current_date()};
ERROR 42X01: Syntax error: Encountered "current_date" at line 1, column 12.
ij> values {fn current_time()};
ERROR 42X01: Syntax error: Encountered "current_time" at line 1, column 12.
ij> -- DB2 UDB compatible test for escaped functions
select 'pass' from source 
where hour(current_time) = {fn hour(current_time)}
and minute(current_time) = {fn minute(current_time)}
and second(current_time) = {fn second(current_time)}
and year(current_date)   = {fn year(current_date)};
1   
----
pass
ij> -- valid jdbc date and time escaped functions
values {fn hour('23:38:10')};
1          
-----------
23         
ij> values {fn minute('23:38:10')};
1          
-----------
38         
ij> values {fn second('23:38:10')};
1          
-----------
10         
ij> values {fn year('2004-03-22')};
1          
-----------
2004       
ij> -- currents do have types, these inserts fail:
insert into source values (0, 0, '0', '0', 0, 0,
	current_time, current_time, current_timestamp);
ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIME'. 
ij> insert into source values (0, 0, '0', '0', 0, 0,
	current_date, current_timestamp, current_timestamp);
ERROR 42821: Columns of type 'TIME' cannot hold values of type 'TIMESTAMP'. 
ij> insert into source values (0, 0, '0', '0', 0, 0,
	current_date, current_time, current_date);
ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'DATE'. 
ij> -- this insert works
insert into source values (0, 0, '0', '0', 0, 0,
	current_date, current_time, current_timestamp);
1 row inserted/updated/deleted
ij> -- test with DB2 syntax
-- this insert works
insert into source values (0, 0, '0', '0', 0, 0,
	current date, current time, current timestamp);
1 row inserted/updated/deleted
ij> -- this test will diff if the select is run just after midnight,
-- and the insert above was run just before midnight...
select * from source where e <> current_date and p <> current_timestamp;
I          |S     |C         |V                                                 |D                     |R            |E         |T       |P                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1          |2     |3         |4                                                 |5.0                   |6.0          |1997-06-07|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- test with DB2 syntax
select * from source where e <> current date and p <> current timestamp;
I          |S     |C         |V                                                 |D                     |R            |E         |T       |P                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1          |2     |3         |4                                                 |5.0                   |6.0          |1997-06-07|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx
ij> select 'pass' from source 
where e <= current_date and p <= current_timestamp;
1   
----
pass
pass
ij> -- reduce it back to one row
delete from source where i=0;
2 rows inserted/updated/deleted
ij> -- tests with extract:
select year( e),
	month( e),
	day( date( '1997-01-15')),
	hour( t),
	minute( t),
	second( time( '01:01:42')),
	year( p),
	month( p),
	day( p),
	hour( timestamp( 'xxxxxxFILTERED-TIMESTAMPxxxxx)),
	minute( p),
	second( p)
from source;
1          |2          |3          |4          |5          |6          |7          |8          |9          |10         |11         |12                    
----------------------------------------------------------------------------------------------------------------------------------------------------------
1997       |6          |15         |8          |8          |42         |9999       |9          |9          |14         |9          |9.0                   
ij> -- extract won't work on other types
select month( i) from source;
ERROR 42X25: The 'EXTRACT MONTH' function is not allowed on the 'INTEGER' type.
ij> select hour( d) from source;
ERROR 42X25: The 'EXTRACT HOUR' function is not allowed on the 'DOUBLE' type.
ij> -- extract won't work on certain field/type combos
select month( t) from source;
ERROR 42X25: The 'EXTRACT MONTH' function is not allowed on the 'TIME' type.
ij> select day( t) from source;
ERROR 42X25: The 'EXTRACT DAY' function is not allowed on the 'TIME' type.
ij> select year( t) from source;
ERROR 42X25: The 'EXTRACT YEAR' function is not allowed on the 'TIME' type.
ij> select hour( e) from source;
ERROR 42X25: The 'EXTRACT HOUR' function is not allowed on the 'DATE' type.
ij> select minute( e) from source;
ERROR 42X25: The 'EXTRACT MINUTE' function is not allowed on the 'DATE' type.
ij> select second( e) from source;
ERROR 42X25: The 'EXTRACT SECOND' function is not allowed on the 'DATE' type.
ij> update source set i=month( e), s=minute( t),
	d=second( p);
1 row inserted/updated/deleted
ij> -- should be true and atomics should match field named as label in date/times
select i,e as "month",s,t as "minute",d,p as "second" 
	from source 
	where       (i = month(e))
		and (s = minute(t))
		and (d = second(p));
I          |month     |S     |minute  |D                     |second                    
----------------------------------------------------------------------------------------
6          |1997-06-07|8     |08:08:08|9.0                   |xxxxxxFILTERED-TIMESTAMPxxxxx
ij> -- fields should match the fields in the date (in order)
select p, year( p) as "year",
	month( p) as "month",
	day( p) as "day",
	hour( p) as "hour",
	minute( p) as "minute",
	second( p) as "second"
from source;
P                         |year       |month      |day        |hour       |minute     |second                
-------------------------------------------------------------------------------------------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx|9999       |9          |9          |9          |9          |9.0                   
ij> -- jdbc escape sequences
values ({d '1999-01-12'}, {t '11:26:35'}, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx});
1         |2       |3                         
----------------------------------------------
1999-01-12|11:26:35|xxxxxxFILTERED-TIMESTAMPxxxxx
ij> values year( {d '1999-01-12'});
1          
-----------
1999       
ij> values hour( {t '11:28:10'});
1          
-----------
11         
ij> values day( {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx});
1          
-----------
12         
ij> drop table source;
0 rows inserted/updated/deleted
ij> drop table target;
0 rows inserted/updated/deleted
ij> -- random tests for date
create table sertest(d date, s Date,
	o Date);
0 rows inserted/updated/deleted
ij> insert into sertest values (date('1992-01-03'), null, null);
1 row inserted/updated/deleted
ij> select * from sertest;
D         |S         |O         
--------------------------------
1992-01-03|NULL      |NULL      
ij> update sertest set s=d;
1 row inserted/updated/deleted
ij> update sertest set o=d;
1 row inserted/updated/deleted
ij> insert into sertest values (date( '3245-09-09'), date( '1001-06-07'),
	date( '1999-01-05'));
1 row inserted/updated/deleted
ij> select * from sertest;
D         |S         |O         
--------------------------------
1992-01-03|1992-01-03|1992-01-03
3245-09-09|1001-06-07|1999-01-05
ij> select * from sertest where d > s;
D         |S         |O         
--------------------------------
3245-09-09|1001-06-07|1999-01-05
ij> update sertest set d=s;
2 rows inserted/updated/deleted
ij> -- should get type errors:
insert into sertest values (date('3245-09-09'), time('09:30:25'), null);
ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIME'. 
ij> insert into sertest values (null, null, time('09:30:25'));
ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIME'. 
ij> insert into sertest values (null, null, timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx));
ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIMESTAMP'. 
ij> -- should work...
update sertest set d=o;
2 rows inserted/updated/deleted
ij> select * from sertest where s is null and o is not null;
D         |S         |O         
--------------------------------
ij> -- should work
select month(s) from sertest where s is not null;
1          
-----------
1          
6          
ij> select day(o) from sertest;
1          
-----------
3          
5          
ij> drop table sertest;
0 rows inserted/updated/deleted
ij> -- conversion tests
drop table convtest;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'CONVTEST' because it does not exist.
ij> create table convtest(d date, t time, ts timestamp);
0 rows inserted/updated/deleted
ij> insert into convtest values(date('1932-03-21'),  time('23:49:52'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx'));
1 row inserted/updated/deleted
ij> insert into convtest values(date('0001-03-21'),  time('5:22:59'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx'));
1 row inserted/updated/deleted
ij> insert into convtest values(null, null, null);
1 row inserted/updated/deleted
ij> -- these should fail
select CAST (d AS time) from convtest;