Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

All date and time data types have a range of legal values and a "zero" value to which a field will be set if

you attempt to put an illegal value into it.

Time formats have an intuitive order that you're used to in daily life: hours at the left of the field,

minutes, and then seconds.

Dates, on the other hand, are always output with year at the left, month, and then day (never

day/month/year).

When outputting date and time, you usually have the option to ask MySQL to give you the data as either

string or numeric data, even though it is the same information. The format used will depend on the

context in which it is used in your SQL.

MySQL is quite flexible when accepting date and time data: for example, the date 2001-05-12 means

the same to MySQL as 2001/05/12, 1+5+12, or even 20010512. You can use a wide range of

separators between the components of the field or none at all, and you can omit the leading zero for

numbers less than 10.

MySQL does partial checking of date and time information. For example, it expects days to be in the

range 1 to 31 and months in the range 1 to 12. However, it does not rigorously check whether a specific

date can really exist. It will not reject 30 February, for example. This makes it more efficient when

accepting data, putting the responsibility on your application to ensure valid dates are being entered.

Date-related Data Types

MySQL has a range of date-related data types to make your life a little easier when handling date and time
information. It offers DATETIME, DATE, TIME, YEAR, and TIMESTAMP types. Take a quick look at the field
formats shown in Table 16.1.
Table 16.1 Date and Time Data Types

Data Type Standard

Format

Zero value

DATETIME

YYYY-MM-

DD

HH:MM:SS

0000-00-00

00:00:00

DATE

YYYY-MM-

DD

0000-00-00

TIME

HH:MM:SS 00:00:00

YEAR (^) YYYY 0000
TIMESTAMP (^) Varies (see
"TIMESTA
MP"
section
later in this
chapter)

00000000000000

(at longest)

DATETIME

The DATETIME format holds the entire date and time of an event, to the nearest second.
DATETIME values can range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

DATE

DATE records the date of an event to the day, without storing time information. MySQL is Y2K compliant in its
date storage.
DATE values can range from 1000-01-01 to 9999-12-31.

TIME

The TIME data type stores the time of an event independent of a particular date.
TIME values can range not just from 00:00:00 to 23:59:59, but, in fact, from -838:59:59 to 838:59:59.
This allows you to represent not just time of day but the elapsed time or difference between two events.
As with DATE and other date and time types, MySQL has a relaxed approach to accepting data, and
you can use any punctuation marks as delimiters to separate the hours, minutes, and seconds. Thus
08:32:20 is the same as 08-32-20, or you can do without leading zeros, as in 8.32.20.