datetime.sql

derby database source code.good for you.

--
-- 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);

insert into t values (null, null, null, null, null, null, null, null, null);
insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0,
	300.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:30'));
insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0,
	-300.0e0, date('1992-01-01'), time('12:30:30'), timestamp('1992-01-01 12:30:45'));

-- date/times don't support math, show each combination
select e + e from t;
select i + e from t;
select p / p from t;
select p * s from t;
select t - t from t;
select -t from t;
select +e from t;

--
-- comparisons
--

-- select each one in turn
-- each pair gets the same result
select e from t where e = date('1992-01-01');
select e from t where date('1992-01-01') = e;
select t from t where t > time('09:30:15');
select t from t where time('09:30:15') < t;
select p from t where p < timestamp('1997-06-30 01:01:01');
select p from t where timestamp('1997-06-30 01:01:01' )> p;

-- now look for a value that isn't in the table
select e from t where e <> date('1992-01-01');
select e from t where date('1992-01-01') <> e;

-- now test null = null semantics
select e, t, p from t where e = e or t = t or p = p;

-- now test <=, >=, <>
-- each pair gets the same result
select e from t where e >= date('1990-01-01');
select e from t where date('1990-01-01')<= e;
select t from t where t <= time('09:30:15');
select t from t where time('09:30:15') >= t;
select p from t where p <> timestamp('1997-06-30 01:01:01');
select p from t where timestamp('1997-06-30 01:01:01' )<> p;

-- show comparisons with mixed types don't work
select e from t where e <= i;
select e from t where t < s;
select e from t where p > d;
select e from t where e >= t;
select e from t where t <> p;
select e from t where p = e;

-- check limit values
values( date('0001-1-1'), date('9999-12-31'), date('2/29/2000'), date('29.2.2004'));
values( time('00:00:00'), time('23:59:59'));
values( time('00 AM'), time( '12:59 AM'), time('1 PM'), time('12:59 PM'));
values( time('00.00.00'), time('23.59.59'), time('24.00.00'));
values( timestamp('0001-1-1 00:00:00'), timestamp('9999-12-31 23:59:59.999999'));

-- show that overflow and underflow are not allowed
-- (SQL92 would have these report errors)
values( date('0000-01-01'));
values( date('2000-00-01'));
values( date('2000-01-00'));
values( date('10000-01-01'));
values( date('2000-13-01'));
values( date('2000-01-32'));
values( date('1900-02-29'));
values( date('2001-02-29'));

values( time('25.00.00'));
values( time('24.00.01'));
values( time('0:60:00'));
values( time('00:00:60'));

-- show garbage in == errors out
select date( 'xxxx') from t where p is null;
select time( '') from t where p is null;
select timestamp( 'is there anything here?' )from t where p is null;
select timestamp( '1992-01- there anything here?' )from t where p is null;
select timestamp( '--::' )from t where p is null;
select time('::::') from t where p is null;

-- show is not null at work
select * from t 
where e is not null
and t is not null
and p is not null;

-- test =SQ
-- this gets cardinality error
select 'fail' from t where e = (select e from t);
-- this works
select 'pass' from t where e = (select e from t where d=200);
-- this gets cardinality error
select 'fail' from t where t = (select t from t);
-- this works
select 'pass' from t where t = (select t from t where d=200);
-- this gets cardinality error
select 'fail' from t where p = (select p from t);
-- this works
select 'pass' from t where p = (select p from t where d=200);

drop table t;

--
-- test syntax: precision cannot be specified
--
create table wrong (t time(-100));
create table wrong (t time(0));
create table wrong (t time(23));
create table wrong (t timestamp(-100));
create table wrong (t timestamp(0));
create table wrong (t timestamp(6));
create table wrong (t timestamp(9));
create table wrong (t timestamp(23));

--
-- 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);
create table target (e date not null, t time not null, p timestamp not null);

-- 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('1999-09-09 09:09:09'));

-- these work:
insert into target select e,t,p from source;

-- these will all fail:
insert into target select p,e,t from source;
insert into target select i,s,d from source;
insert into target (t,p) select c,r from source;

delete from source;
insert into source values (null, null, null, null, null, null, null, null, null);
-- these fail because the target won't take a null -- of any type
insert into target values(null, null, null);
insert into target select e,t,p from source;
-- these still fail with type errors:
insert into target select p,e,t from source;
insert into target select i,s,d from source;
insert into target (t,p)select c,r from source;

-- expect 1 row in target:
select * from target;

-- unchanged:
update target set e = e, t = t, p = p;
select * from target;

-- alters the row:
update target set e = date('1990-01-01');
select * from target;

-- not settable to null
update target set e = null;
select * from target;

-- nullable col can be set to null:
update source set e = date('1492-10-01');
select e from source;
update source set e = null;
select e from source;

-- these should get type errors
update target set e = 1;
update source set p = 1.4e10;
update source set i = date('1001-01-01');


-- tests with current functions:
delete from source;
delete from target;
insert into source values (1, 2, '3', '4', 5, 6, date('1997-06-07'),
	time('08:08:08'),timestamp('9999-09-09 09:09:09'));

-- 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;

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
    '1996-12-31 00:00:00');

-- test with DB2 compatible syntax
select 'pass' from source 
where current date = current date
and current time = current time
and current timestamp = current timestamp;

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
    '1996-12-31 00:00:00');

-- 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;

select 'pass' from source 
where current_date = {fn curdate()}
and current_time = {fn curtime()}
and current_timestamp = current_timestamp;

-- current_date() and current_time() not valid in DB2. curdate() and curtime()
-- are as escaped functions only.
values curdate();
values curtime();
values current_date();
values current_time();

values {fn current_date()};
values {fn current_time()};

-- 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)};

-- valid jdbc date and time escaped functions
values {fn hour('23:38:10')};
values {fn minute('23:38:10')};
values {fn second('23:38:10')};
values {fn year('2004-03-22')};

-- currents do have types, these inserts fail:
insert into source values (0, 0, '0', '0', 0, 0,
	current_time, current_time, current_timestamp);
insert into source values (0, 0, '0', '0', 0, 0,
	current_date, current_timestamp, current_timestamp);
insert into source values (0, 0, '0', '0', 0, 0,
	current_date, current_time, current_date);

-- this insert works
insert into source values (0, 0, '0', '0', 0, 0,
	current_date, current_time, current_timestamp);

-- test with DB2 syntax
-- this insert works
insert into source values (0, 0, '0', '0', 0, 0,
	current date, current time, current timestamp);


-- 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;
-- test with DB2 syntax
select * from source where e <> current date and p <> current timestamp;

select 'pass' from source 
where e <= current_date and p <= current_timestamp;

-- reduce it back to one row
delete from source where i=0;

-- 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( '1992-01-01 14:11:23')),
	minute( p),
	second( p)
from source;

-- extract won't work on other types
select month( i) from source;
select hour( d) from source;

-- extract won't work on certain field/type combos
select month( t) from source;
select day( t) from source;
select year( t) from source;
select hour( e) from source;
select minute( e) from source;
select second( e) from source;

update source set i=month( e), s=minute( t),
	d=second( p);

-- 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));

-- 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;

-- jdbc escape sequences
values ({d '1999-01-12'}, {t '11:26:35'}, {ts '1999-01-12 11:26:51'});
values year( {d '1999-01-12'});
values hour( {t '11:28:10'});
values day( {ts '1999-01-12 11:28:23'});

drop table source;
drop table target;

-- random tests for date
create table sertest(d date, s Date,