Specifications

Date and Time Types
MySQL supports a number of date and time types. These are shown in Table 8.7. With all
these types, you can input data in either a string or numerical format. It is worth noting that a
TIMESTAMP column in a particular row will be set to the date and time of the most recent opera-
tion on that row if you don’t set it manually. This is useful for transaction recording.
TABLE 8.7 Date and Time Data Types
Type Range Description
DATE 1000-01-01 A date. Will be displayed as YYYY-MM-DD.
9999-12-31
TIME -838:59:59 A time. Will be displayed as HH:MM:SS.
838:59:59 Note that the range is much wider than you
will probably ever want to use.
DATETIME 1000-01-01 A date and time. Will be displayed as
00:00:00 YYYY-MM-DDHH:MM:SS.
9999-12-31
23:59:59
TIMESTAMP[(M)] 1970-01-01 A timestamp, useful for transaction
00:00:00 reporting. The display format depends on the
value of M (see Table 8.8, which follows).
Sometime The top of the range depends on the limit
in 2037 on UNIX.
timestamps.
YEAR[(2|4)] 70–69 A year. You can specify 2 or 4 digit format.
(1970–2069) Each of these has a different range, as
1901–2155 shown.
Table 8.8 shows the possible different display types for TIMESTAMP.
T
ABLE 8.8 TIMESTAMP Display Types
Type Specified Display
TIMESTAMP YYYYMMDDHHMMSS
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY
Creating Your Web Database
C
HAPTER 8
8
CREATING YOUR
WEB DATABASE
203
11 7842 CH08 3/6/01 3:38 PM Page 203