[Monetdb-developers] SQL: Snodgrass' examples

15 Nov 2005

      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;

[Monetdb-developers] SQL: Snodgrass' examples

Steffen Goeldner