A time value can be used in:
Assignments:
(see section "Assignment rules")Aggregate functions:
A time value can be used in the aggregate functions COUNT(), MIN() and MAX().Numeric functions:
A time value can be used in the numeric function JULIAN_DAY_OF_DATE().Predicates:
A time value can be used in comparisons with another value or with a derived column, in range queries and in element queries. All the values involved must be of the same time data type. The rules governing comparisons are described in the section "Comparison of two rows".CAST expressions:
A time value can be converted to a value of a different data type.
Functions and predicates are described in detail in the chapter "Compound language constructs".
Examples
The following examples refer to the ORDERS table and the fictitious table EXAMPLE.
Update the delivery date for order 300:
UPDATE orders SET order_date=DATE'2013-10-06' WHERE order_num=300
UPDATE orders SET order_date=DATE'2013-10-06' WHERE order_num=300
The last one is incorrect: Since the single-digit value 6 for a day is not permitted. The correct specification would be 06.
In the column wakeup_time, the time 7:51 hours and 19.77 seconds is entered:
CREATE TABLE example (wakeup_time TIME (3), appointment TIMESTAMP (3))
INSERT INTO example (wakeup_time) VALUES (TIME'07:51:19.770')
In the column appointment, the time stamp 16:00 hours on November 24th, 2010 is entered:
INSERT INTO example (appointment)
VALUES (TIMESTAMP'2013-10-06 16:00:00.000')
INSERT INTO example (appointment)
VALUES (TIMESTAMP'2013-10-06 16:00')
The last one is incorrect as seconds have not been specified.