O'Reilly logo

MySQL in a Nutshell by Russell J.T. Dyer

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required