Data Definition Language, Part 1 - 4.10.3 Date and Time Column Types

MySQL provides column types for storing different kinds of temporal information. In the following descriptions, the terms YYYY, MM, DD, hh, mm, and ss stand for a year, month, day of month, hour, minute, and second value, respectively.

The storage requirements and ranges for the date and time datatypes are summarized in the following table:

DATE

TIME

DATETIME

TIMESTAMP

YEAR

For TIMESTAMP, MySQL 4.0 displays values such as '1970-01-01 00:00:00 'as 19700101000000. In MySQL 4.1, this changes so that TIMESTAMP display format is the same as DATETIME.

Each of these temporal datatypes also has a "zero" value that's used when you attempt to store an illegal value. The "zero" value is represented in a format appropriate for the type (such as '0000-00-00 'for DATE and '00:00:00 'for TIME).

The DATE datatype represents date values in 'YYYY-MM-DD 'format. This representation corresponds to the ANSI SQL date format, also known as ISO 8601 format.

The supported range of DATE values is '1000-01-01 'to '9999-12-31 '. You might be able to use earlier dates than that, but it's better to stay within the supported range to avoid unexpected behavior.

MySQL always represents DATE values in ISO 8601 format when it displays them. If necessary, you can reformat DATE values into other display formats using the DATE_FORMAT() function.

For date entry, MySQL also expects to receive dates in ISO format, or at least close to ISO format. That is, date values must be given in year-month-day order, but some deviation from strict ISO format is allowed:

• Leading zeros on month and day values may be omitted. For example, '2000-1-1 'and '2000-01-01 'are both accepted as legal.

• The delimiter between date parts need not be -; you can use other punctuation characters, such as /.

• Two-digit years are converted to four-digit years. You should be aware that this conversion is done based on the rule that year values from 70 to 99 represent the years 1970 to 1999, whereas values from 00 to 69 represent the years 2000 to 2069. It's better to provide values with four-digit years to avoid problems with conversion of values for which the rule does not apply.

Instead of attempting to load values that aren't in an acceptable format into a DATE column, you should convert them into ISO format. An alternative approach that's useful in some circumstances is to load the values into a string column and perform reformatting operations using SQL string functions to produce ISO format values that can be assigned to a DATE column.

The TIME datatype represents time values in 'hh:mm:ss 'format. TIME values may represent elapsed time, and thus might be outside the range of time-of-day values. They may even be negative values. (The actual range of TIME values is '-838:59:59 'to '838:59:59 '.)

MySQL represents TIME values in 'hh:mm:ss 'format when displaying them. If necessary, you can reformat TIME values into other display formats using the TIME_FORMAT() function.

For TIME value entry, some variation on this format is allowed. For example, leading zeros on TIME parts may be omitted.

The DATETIME column type stores date-and-time values in 'YYYY-MM-DD hh:mm:ss 'format. It's similar to a combination of DATE and TIME values, but the TIME part represents time of day rather than elapsed time and has a range limited to '00:00:00 'to '23:59:59 '. The date part of DATETIME columns has the same range as DATE columns; combined with the TIME part, this results in a DATETIME range from '1000-01-01 00:00:00 'to '9999-12-31 23:59:59 '.

The TIMESTAMP type, like DATETIME, stores date-and-time values, but has a different range and some special properties that make it especially suitable for tracking data modification times. TIMESTAMP also has a different display format from DATETIME prior to MySQL 4.1:

• Until MySQL 4.1, TIMESTAMP values are represented as numbers in YYYYMMDDhhmmss format. The default display width is 14 digits, but you can specify an explicit width of any even number from 2 to 14. The display width affects only how MySQL displays TIMESTAMP values, not how it stores them. Stored values always include the full 14 digits.

• From MySQL 4.1 on, the TIMESTAMP format is 'YYYY-MM-DD hh:mm:ss ', just like DATETIME. Display widths are not supported.

The range of TIMESTAMP values begins at 1970-01-01 00:00:00 (GMT) and extends partway into the year 2037. TIMESTAMP values actually represent the number of seconds elapsed since the beginning of 1970 and are stored using four bytes. This provides room for sufficient seconds to represent a date in the year 2037. Note that TIMESTAMP values are stored using the server's local timezone.

TIMESTAMP columns have the following special properties:

• Storing NULL into a TIMESTAMP column sets it to the current date and time. Updating a TIMESTAMP column to NULL also sets it to the current date and time.

• If you omit a TIMESTAMP column from an INSERT statement, MySQL inserts the current date and time if the column is the first TIMESTAMP column in the table, and inserts zero if it is not.

• MySQL automatically updates the first TIMESTAMP column in a table to the current date and time when you update (change the existing data in) any other column in the table. (Setting a column to its current value doesn't count as updating it.) Only the first TIMESTAMP column is subject to automatic updating. All other TIMESTAMP columns do not change unless you update them explicitly.

It's important to know about the automatic-update property. It's what makes TIMESTAMP columns useful for tracking record modification times, but is a source of confusion if you're not aware of it. People who choose TIMESTAMP for a column on the basis of the fact that it stores date-and-time values become dismayed and mystified when they discover that the column's values change unexpectedly.

The YEAR column type represents year-only values. You can declare such columns as YEAR(4) or YEAR(2) to obtain a four-digit or two-digit display format. If you don't specify any display width, the default is four digits.

If you don't need a full date and the range of values you need to store falls into the YEAR range, consider using YEAR to store temporal values. It's a very space-efficient datatype because values require only one byte of storage each.

This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.

Next: 4.10.4 Column Options >>
 

More MySQL Articles
More By Sams Publishing

Comments On: Data Definition Language, Part 1

· I am a student from China,my English is vervy poor.I over it at last,but I do not...    · OKAY!!!   >>> Post your comment now!