Universal Database (netDB2) Introductory Guide

Creating A Table

TIME:
The TIME function returns a time from a value. The schema is SYSIBM. The argument must be a time, timestamp, or a valid character string representation of a time or timestamp that is neither a CLOB nor a LONG VARCHAR. The result of the function is a time. If the argument can be null, the result can be null; if the argument is null, the result is the null value. The other rules depend on the data type of the argument:

If the argument is a time, the result is that time. If the argument is a timestamp, the result is the time part of the timestamp. If the argument is a character string, the result is the time represented by the character string. 

Syntax:  time {expression}

Example:
   Select all notes from the IN_TRAY sample table that were received at least
   one hour later in the day (any day) than the current time.

   SELECT * FROM IN_TRAY
   WHERE TIME(RECEIVED) >= CURRENT TIME + 1HOUR

The TIMESTAMP function returns a timestamp from a value or a pair of values. 

Syntax: Timestamp {expression } {expression}

Example:
	Assume the column START_DATE (date) has a value equivalent to
	1988-12-25, and the column START_TIME (time) has a value 
	equivalent to 17.12.30. 
	
	TIMESTAMP(START_DATE, START_TIME)
	
	Returns the value ’1988-12-25-17.12.30.000000’.

Wing example illustrates a use of before and after triggers. Consider an application that records and tracks changes to stock prices. The database contains two tables, CURRENTQUOTE and QUOTEHISTORY defined as: 

CREATE TABLE CURRENTQUOTE
(SYMBOL VARCHAR(10),
QUOTE DECIMAL(5,2),
STATUS VARCHAR(9))
CREATE TABLE QUOTEHISTORY
(SYMBOL VARCHAR(10),
QUOTE DECIMAL(5,2),
TIMESTAMP TIMESTAMP)

When the QUOTE column of CURRENTQUOTE is updated using a statement such as: 

UPDATE CURRENTQUOTE
SET QUOTE = 68.5
WHERE SYMBOL = ©IBM©

The STATUS column of CURRENTQUOTE should be updated to reflect whether the stock is: 

v Rising in value
v At a new high for the year
v Dropping in value
v At a new low for the year
v Steady in value.

This is done using the following before trigger::wq