Table Column Types

Part:   1  2  3  

(Continued from previous part...)

Examples of approximate number column types, ApproximateNumberColumns.sql:

-- ApproximateNumberColumns.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
CREATE TABLE ApproximateNumber (
   Line VARCHAR(8),
   X FLOAT(8), -- (precision)
   Y REAL, 
   Z DOUBLE PRECISION
   );
INSERT INTO ApproximateNumber VALUES (
   '1',
   1.11,
   2.22, 
   3.33
   );
INSERT INTO ApproximateNumber VALUES (
   '2',
   11.1111,
   22.2222, 
   33.3333
   );
INSERT INTO ApproximateNumber VALUES (
   '3',
   111.111111,
   222.222222, 
   333.333333
   );
INSERT INTO ApproximateNumber VALUES (
   '4',
   2/3,
   2/3,
   2/3
   );
SELECT * FROM ApproximateNumber;
DROP TABLE ApproximateNumber;

Output:

Line    X       Y       Z
1       1.11    2.22    3.33
2       11.1111 22.2222 33.3333
3       111.111 222.222222      333.333333
4       0.666667        0.666666666666667       0.666666666666667

Note that:

• Line 3 tells us that FLOAT(8) can only take 6 digits, if not enough space, extra fractional digits will be rounded up or down.
• Line 4 tells us that REAL and DOUBLE PRECISION are having the same precision.

Table Column Types for Date and Time Values

There 6 types of table columns to store date and time values: TIME, DATE, TIMESTAMP, TIME WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, and INTERVAL.

"TIME" - Compound value of hour, minute, second, and fractional seconds.

"DATE" - Compound value of year, month, and day.

"TIMESTAMP" - Compound value of year, month, day, hour, minute, second and fractional seconds.

"TIME WITH TIME ZONE" - Compound value of hour, minute, second, fractional seconds and time zone.

"TIMESTAMP WITH TIME ZONE" - Compound value of year, month, day, hour, minute, second, fractional seconds and time zone.

"INTERVAL" - Value representing two different date time values.

Examples of date and time column types, DateTimeColumns.sql:

-- DateTimeColumns.sql
-- Copyright (c) 1999 by Dr. Herong Yang

CREATE TABLE DateTime (
   Line VARCHAR(2),
   A DATE,
   B TIME, -- MySQL does not support TIME(3)
   C TIMESTAMP,
   D TIME -- MySQL does not support TIME WITH TIME ZONE
--   E TIMESTAMP WITH TIME ZONE
--   F INTERVAL
   );
INSERT INTO DateTime VALUES (
   '1',
   DATE '1999-07-01',
   TIME '10:20:30.001',
   TIMESTAMP '1999-06-24 01:02:03.001',
   TIME '10:20:30.001-11:11'
   );
INSERT INTO DateTime VALUES (
   '2',
   '1999-07-02',
   '10:20:31.002',
   '1999-06-25 01:02:13.002',
   '10:20:31.002-12:12'
   );
INSERT INTO DateTime VALUES (
   '3',
   '10:20:33.002',
   '1999-07-03',
   '1999-06-26 01:02:13.003',
   '10:20:33.003-13:13'
   );
SELECT * FROM DateTime;
DROP TABLE DateTime;

Output:

Line    A       B       C       D
1       1999-07-01      10:20:30        19990624010203  10:20:30
2       1999-07-02      10:20:31        19990625010213  10:20:31
3       0000-00-00      00:00:00        19990626010213  10:20:33

Note that:

• Seconds fraction can be assigned to a column of TIME and TIMESTAMP.
• Time zone can be assigned to a column of TIME and TIMESTAMP.
• Line 3 tells us that a time literal can be assigned to a column of DATE. But the time value will be truncated and a '0' will be assigned to the column.
• Line 3 also tells us that a date literal can be assigned to a column of TIME. But the date value will be truncated and a '0' will be assigned to the column.

Part:   1  2  3