A.4. Date and Time Datatypes


There are a few column datatypes for storing date and time values. They are listed in Table A-3. The table also lists the valid ranges for each datatype. If a value is inserted that is not permitted, or is outside of the acceptable range for the datatype, zeros are used instead. You can override this by starting the server with --sql-mode='ALLOW_INVALID_DATES'. Starting in Version 5.0.2 of MySQL, warnings will be generated when inserting invalid dates or times. For dates that are inserted with only two digits for the year, values from 00 to 69 are assumed to be in the 21st century. For years from 70 to 99, they are assumed to be in the 20th century.

Table A-3. Date and time data types

Data type

Format

Range

DATE

yyyy-mm-dd

1000-01-01 to 9999-12-31

DATETIME

yyyy-mm-dd hh:mm:ss

1000-01-01 00:00:00 to 9999-12-31 00:00:00

TIMESTAMP[(width)]

yyyymmddhhmmss

1970-01-01 00:00:00 to 2037-12-31 23:59:59

TIME

hh:mm:ss

-838:59:59 to 838:59:59

YEAR[(2|4)]

yy or yyyy

1970 to 2069 or 1901 to 2155


Times values may be given as either a string, or numerically. As a string, you may enter a value as d hh:mm:ss.f. In this format, d stands for the number of days, with an allowable range of 0 to 34. The f stands for a fractional number of seconds. This value will not be stored, though. Storing fractional seconds is expected to be added in future releases of MySQL. You don't have to specify values for all elements of a time. Instead, you can enter a time value using one of these formats: hh:mm:ss.f, or hh:mm:ss, hh:mm, or just ss. If you want to include the number of days you can use these formats: d hh:mm:ss, d hh:mm, or d hh. You can also drop the colons and just enter hhmmss, but you can't add minutes onto the end of that format. The datatype TIMESTAMP stores the date and time in a format of yyyymmddhhmmss, but displays it with the format of yyyy-mm-dd hh:mm:ss. MySQL will automatically convert a date or time to its numeric equivalent when it is used in a numeric context, and it will do the reverse as well.



MySQL in a Nutshell
MYSQL in a Nutshell (In a Nutshell (OReilly))
ISBN: 0596514336
EAN: 2147483647
Year: 2006
Pages: 134
Authors: Russell Dyer

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net