Attached is a SQL script with a collection of examples from 'Developing time-oriented database applications in SQL', chapter 3.
http://www.cs.arizona.edu/people/rts/tdbbook.pdf
I find this book very useful because the SQL standard is a very dry read. I changed a few errata, which are already confirmed by Mr. Snodgrass. It would give me great pleasure if the examples are of any use for you (e.g. as test script, quick reference, todo list ;-)
Steffen
/*
Examples from
Developing time-oriented database applications in SQL Richard T. Snodgrass ISBN 1-55860-436-7 Morgan Kaufmann Publishers http://www.cs.arizona.edu/people/rts/tdbbook.pdf
Chapter 3: Instants and Intervals
----------------------------------------------------------------------
Boolean results should be true!
ROLLBACK: expected error (not always thrown by MonetDB) rollback: MonetDB error
*/
SELECT DATE '1997-01-12'; SELECT TIME '11:35:29'; SELECT TIME '00:00:00'; SELECT TIME '11:08:27-07:00'; SELECT TIME '11:08:27.123456-07:00'; SELECT TIMESTAMP '1997-01-15 11:35:29.123456'; SELECT TIMESTAMP '1997-01-15 11:35:29.123456-07:00'; SELECT INTERVAL '3' YEAR; SELECT INTERVAL '7' MONTH; SELECT INTERVAL '3-7' YEAR TO MONTH; SELECT INTERVAL -'3-7' YEAR TO MONTH; SELECT INTERVAL +'0-0' YEAR TO MONTH; SELECT INTERVAL -'0-0' YEAR TO MONTH; SELECT INTERVAL '1 23:45:12' DAY TO SECOND;
SELECT INTERVAL '0-0' YEAR TO MONTH = INTERVAL +'0-0' YEAR TO MONTH; SELECT INTERVAL '0-0' YEAR TO MONTH = INTERVAL -'0-0' YEAR TO MONTH; SELECT INTERVAL '3-4' YEAR TO MONTH = INTERVAL +'3-4' YEAR TO MONTH; SELECT INTERVAL '3-4' YEAR TO MONTH = INTERVAL '+3-4' YEAR TO MONTH; SELECT INTERVAL '3-4' YEAR TO MONTH = INTERVAL +'+3-4' YEAR TO MONTH; SELECT INTERVAL '3-4' YEAR TO MONTH = INTERVAL -'-3-4' YEAR TO MONTH; SELECT INTERVAL '3-4' YEAR TO MONTH = INTERVAL '3-4' YEAR TO MONTH;
SELECT INTERVAL '3-7' YEAR TO MONTH = INTERVAL '43' MONTH; SELECT INTERVAL '3-7' YEAR TO MONTH = INTERVAL '23' DAY; -- incomparable ROLLBACK;
-- `< ', ` < = ', `> ', `>= ', and`<>
SELECT DATE '1996-02-24' + INTERVAL '7' DAY = DATE '1996-03-02'; SELECT DATE '1996-02-24' + INTERVAL '12:30' HOUR TO MINUTE; -- disallowed ROLLBACK; SELECT DATE '1996-02-24' + INTERVAL '2 12' DAY TO HOUR; -- disallowed ROLLBACK; SELECT INTERVAL '7' DAY + DATE '1996-02-24' = DATE '1996-03-02'; SELECT DATE '1996-03-02' - INTERVAL '7' DAY = DATE '1996-02-24'; SELECT TIMESTAMP '1996-02-24 12:34:56' AT LOCAL; -- MST: TIMESTAMP '1996-02-24 19:34:56' rollback; SELECT TIMESTAMP '1996-02-24 12:34:56+02:00' AT LOCAL; -- (MET DST) MST: TIMESTAMP '1996-02-24 03:34:56' rollback; SELECT TIMESTAMP '1996-02-24 12:34:56' AT TIME ZONE INTERVAL '-7:00' HOUR TO MINUTE = TIMESTAMP '1996-02-24 19:34:56'; rollback; SELECT CURRENT_DATE; SELECT CURRENT_TIME; SELECT CURRENT_TIMESTAMP; -- simultaneously SELECT CAST('1996-02-24' AS DATE) = DATE '1996-02-24'; SELECT CAST('12:34:56' AS TIME) = TIME '12:34:56'; SELECT CAST(TIME '12:34:56' AS TIMESTAMP WITH TIME ZONE) = TIMESTAMP '1997-07-23 12:34:56-07:00'; rollback; SELECT CAST( TIME '12:34:56.123' AS TIME(6) ) = TIME '12:34:56.123000'; SELECT CAST( TIME '12:34:56.123' AS TIME(1) ) = TIME '12:34:56.1'; SELECT CAST( TIMESTAMP '1997-07-23 12:34:56.123' AS TIME(6) ) = TIME '12:34:56.123000'; rollback; SELECT CAST( TIMESTAMP '1997-07-23 12:34:56.123' AS DATE ) = DATE '1997-07-23'; SELECT CAST( DATE '1997-01-01' AS TIMESTAMP(4)) = '1997-01-01 00:00:00.0000'; rollback;
SELECT INTERVAL '3' DAY + INTERVAL '4' DAY = INTERVAL '7' DAY; SELECT INTERVAL '3' DAY + INTERVAL '4' HOUR = INTERVAL '3 4' DAY TO HOUR; SELECT INTERVAL '3' DAY + INTERVAL '8 4' DAY TO HOUR = INTERVAL '11 4' DAY TO HOUR; SELECT INTERVAL '3' DAY - INTERVAL '4' DAY = INTERVAL -'1' DAY; SELECT INTERVAL '3' DAY - INTERVAL -'8 4' DAY TO HOUR = INTERVAL '11 4' DAY TO HOUR; SELECT ( DATE '1997-01-01' - DATE '1996-01-01') DAY = INTERVAL '366' DAY; rollback; SELECT ( DATE '1997-01-01' - DATE '1996-01-01') YEAR TO MONTH = INTERVAL '1-0' YEAR TO MONTH; rollback; SELECT INTERVAL '4' DAY * 3 = INTERVAL '12' DAY; SELECT 3 * INTERVAL '4' DAY = INTERVAL '12' DAY; SELECT INTERVAL '12:30' HOUR TO MINUTE * 3 = INTERVAL '37:30' HOUR TO MINUTE; SELECT INTERVAL '4' DAY / 2 = INTERVAL '2' DAY; SELECT 2 / INTERVAL '4' DAY; -- not permitted ROLLBACK; SELECT - INTERVAL '4' DAY = INTERVAL -'4' DAY; SELECT + INTERVAL '4' DAY = INTERVAL '4' DAY; SELECT CAST('2' AS INTERVAL MONTH ); SELECT CAST('3-7' AS INTERVAL MONTH ); -- wrong ROLLBACK; SELECT CAST(INTERVAL '8-7' YEAR TO MONTH AS INTERVAL MONTH(2) ); -- exception is raised ROLLBACK; SELECT CAST( INTERVAL '3' YEAR AS INTERVAL YEAR TO MONTH ) = INTERVAL '3-0' YEAR TO MONTH; SELECT CAST( 103 AS INTERVAL MONTH ) = INTERVAL '103' MONTH; SELECT CAST( 103 AS INTERVAL MONTH(2) ); -- overflow exception ROLLBACK; SELECT CAST('2 12:34' AS INTERVAL DAY TO MINUTE ); SELECT CAST('12:34' AS INTERVAL DAY TO MINUTE ); -- works not ROLLBACK; SELECT CAST( INTERVAL'86 00:00:00' DAY TO SECOND AS INTERVAL HOUR TO SECOND ) = INTERVAL '2064:00:00' HOUR TO SECOND; SELECT CAST('86 00:00:00' AS INTERVAL HOUR(3) TO SECOND ); -- overflow exception ROLLBACK; SELECT CAST( CAST( 7430400 AS INTERVAL SECOND ) AS INTERVAL DAY TO SECOND ) = INTERVAL '86 00:00:00' DAY TO SECOND; SELECT CAST(DATE '1997-01-01' AS CHARACTER) = '1997-01-01'; SELECT CAST(INTERVAL '7430400' SECOND AS INTEGER) = 7430400; SELECT CAST(CAST(INTERVAL '2064:00:00' HOUR TO SECOND AS INTERVAL SECOND) AS INTEGER) = 7430400;
SELECT EXTRACT( YEAR FROM DATE '1970-01-01') = 1970; SELECT EXTRACT( MINUTE FROM INTERVAL '12:34:56' HOUR TO SECOND ) = 34; SELECT EXTRACT( TIMEZONE_HOUR FROM TIME '12:34:56-07:00') = -7; rollback; SELECT EXTRACT( TIMEZONE_MINUTE FROM TIME '12:34:56-07:00') = 0; rollback;
CREATE TABLE Employee( Id INTEGER, BirthDate DATE ); INSERT INTO Employee VALUES( 77, '1970-01-01'); INSERT INTO Employee VALUES( 88, '1971-12-13'); COMMIT;
SELECT * FROM Employee WHERE BirthDate = DATE '1970-01-01'; SELECT * FROM Employee WHERE BirthDate =ANY ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE BirthDate =ALL ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE BirthDate =SOME ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE BirthDate IN ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE NOT BirthDate NOT IN ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE BirthDate MATCH ( VALUES( ( DATE '1970-01-01') ) ); rollback;
SELECT * FROM Employee WHERE NOT BirthDate <> DATE '1970-01-01'; rollback; SELECT * FROM Employee WHERE NOT BirthDate <>ANY ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE NOT BirthDate <>ALL ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE NOT BirthDate <>SOME ( VALUES( ( DATE '1970-01-01') ) ); rollback;
SELECT * FROM Employee WHERE BirthDate BETWEEN DATE '1970-01-01' AND DATE '1970-01-01'; SELECT * FROM Employee WHERE NOT BirthDate NOT BETWEEN DATE '1970-01-01' AND DATE '1970-01-01'; rollback;
SELECT * FROM Employee WHERE ( BirthDate, INTERVAL '0' DAY ) OVERLAPS ( DATE '1970-01-01', INTERVAL '0' DAY ); rollback; SELECT * FROM Employee WHERE ( BirthDate, BirthDate ) OVERLAPS ( DATE '1970-01-01', INTERVAL '0' DAY ); rollback; SELECT * FROM Employee WHERE ( BirthDate, INTERVAL '0' DAY ) OVERLAPS ( DATE '1970-01-01', DATE '1970-01-01'); rollback; SELECT * FROM Employee WHERE ( BirthDate, BirthDate ) OVERLAPS ( DATE '1970-01-01', DATE '1970-01-01'); rollback;
SELECT * FROM Employee WHERE ( BirthDate, NULL ) OVERLAPS ( DATE '1970-01-01', INTERVAL '0' DAY ); rollback; SELECT * FROM Employee WHERE ( BirthDate, NULL ) OVERLAPS ( DATE '1970-01-01', NULL ); rollback; SELECT * FROM Employee WHERE ( BirthDate, NULL ) OVERLAPS ( NULL , DATE '1970-01-01'); rollback;
SELECT * FROM Employee WHERE CAST( BirthDate AS CHAR ) = '1970-01-01'; SELECT * FROM Employee WHERE CAST( BirthDate AS CHAR ) LIKE '1970-01-01'; SELECT * FROM Employee WHERE CAST( ( DATE '1971-01-01' - BirthDate ) DAY AS INT ) = 365 AND CAST( ( DATE '1971-01-01' - BirthDate ) YEAR AS INT ) = 1; rollback; -- XXX: SELECT * FROM Employee WHERE EXTRACT( YEAR FROM BirthDate ) = 1970 AND EXTRACT( MONTH FROM BirthDate ) = 1 AND EXTRACT( DAY FROM BirthDate ) = 1;
DROP TABLE Employee; COMMIT;