D.3 Date Datatypes
The DATE types encompass all of the time-based datatypes in MySQL. The major difference in behavior between the Date types and all other numeric types is in the zero value. All DATE types return the full date string with every digit zeroed. For example the DATE type would return 0000-00-00 and TIME would return 00:00:00.
4932-4 AppD.F 5/29/02 3:55 PM Page 648
648
Part VI ✦ Appendixes
DATE
The DATE type represents a full calendar date in the format YYYY-MM-DD. Legal values for the DATE column range between 1000-01-01 and 9999-12-31. Invalid values for the column will result in a value of 0000-00-00.
DATETIME
The DATETIME type is equivalent to the DATE column with the addition of a time constituent. The DATETIME column follows the format YYYY-MM-DD HH:MM:SS. Valid DATETIME values can range between 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
Invalid column values result in a value of 0000-00-00 00:00:00.
TIMESTAMP
The TIMESTAMP column is a string representation of the Unix timestamp. The Unix timestamp is derived from what is known as Epoch time. Valid TIMESTAMP values range from 1970-01-01 00:00:00 through 2038-01-19 03:14:07. An invalid value will return a zerofill to the designated size of the column.
Designated by TIMESTAMP( p) where p is the number of digits displayed and returned on queries to the column. The maximum length of the TIMESTAMP type is 14 places.
If a TIMESTAMP is designated with 0 digits or greater than 14 it will default to 14. If an odd value is given for p it will be converted to p+1. NULL inserts into the first timestamp column in a table will result in the current date and time being returned.
p
Maximum Size
2
YY
4
YYMM
6
YYMMDD
8
YYYYMMDD
10
YYMMDDHHMM
12
YYMMDDHHMMSS
14
YYYYMMDDHHMMSS
TIME
The TIME type is a generic representation of time values in hours, minutes, and seconds. The column besides representing time in the standard from of a 24-hour clock also can represent time in both the past and future. The valid range for the TIME
type is –838:59:59 to 838:59:59.
4932-4 AppD.F 5/29/02 3:55 PM Page 649
Appendix D ✦ Datatype Reference
649
The TIME type can additionally take a day modifier upon inserts. The valid formats for inserts are D HH:MM:SS, HH:MM:SS, MM:SS, and SS. Where the day or time constituent can be preceeded by – to represent negative time. Return values will have left zero padding.
YEAR
The YEAR type represents the calendar year in either a 2-digit or 4-digit format.
In the 4-digit format, YEAR can range from 1901 to 2155 or 0000. In the 2-digit format, the YEAR range is 1970 through 2069, represented as 70 to 69. Invalid values inserted into the YEAR column will return 0000. With the 2-digit format, a single 0 is considered an invalid value; 00 must be used to represent the year 2000.
D.4. STRING Datatypes
The STRING datatypes encompass all column types used for textual data. In addition to the NULL value, all STRING types additionally have the empty string value (‘’). Inserts into STRING types that exceed the maximum allotted length will be truncated to fit the space allocated.
BLOB
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
BLOB is the STRING datatype used for binary data. Any queries on a BLOB type result in a case-sensitive return. For example a BLOB type with the value ‘string’
will not be matched by queries for ‘STRING’. This is opposite to the behavior of the TEXT type.
Note
Data exceeding the maximum size of the column is forcibly truncated after insertion.
Column Type
Maximum Size
TINYBLOB
255
BLOB
65535
MEDIUMBLOB
16777215
LONGBLOB
4294967295
4932-4 AppD.F 5/29/02 3:55 PM Page 650
650
Part VI ✦ Appendixes
CHAR
The CHAR datatype is used for string storage.
Designated by CHAR( p) where p designates the field length, the maximum length for the CHAR type is 255. When stored the data in the column will pad to the full length ( p) of the field with whitespace. Upon retrieval from the database, the appended whitespace is removed.
TEXT
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
TEXT is the STRING datatype used for character data. It works much like the BLOB
datatype; queries upon the TEXT type will return case insensitive values. For example a TEXT type with the value ‘string’ will be matched by queries for ‘STRING’.
This is opposite to the behavior of the BLOB type.
Column Type
Maximum Size
TINYTEXT
255
TEXT
65535
MEDIUMTEXT
16777215
LONGTEXT
4294967295
VARCHAR
The VARCHAR datatype works like the CHAR type except for its data-storage method.
The VARCHAR type removes all trailing whitespace from inserted data.
Designated by VARCHAR( p) where p designates the field length, the maximum length of the VARCHAR type is 255 characters.
In considering space requirements for some systems, the VARCHAR type should be used as opposed to the CHAR type. The addition of whitespace trailing all CHAR entities can dramatically increase the space used in a table containing even very few rows of data.
4932-4 AppD.F 5/29/02 3:55 PM Page 651
Appendix D ✦ Datatype Reference
651
D.5. Grouping Datatypes