11.3. Date and Time Types

MySQL 5.0

previous page next page

11.3. Date and Time Types

The date and time types for representing temporal values are , , , , and . Each temporal type has a range of legal values, as well as a “zero” value that may be used when you specify an illegal value that MySQL cannot represent. The type has special automatic updating behavior, described later on. For temporary type storage requirements, see Section 11.5, “Data Type Storage Requirements”.

Starting from MySQL 5.0.2, MySQL gives warnings or errors if you try to insert an illegal date. By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you want MySQL to support. (See Section 5.2.5, “The Server SQL Mode”.) You can get MySQL to accept certain dates, such as , by using the SQL mode. (Before 5.0.2, this mode was the default behavior for MySQL.) This is useful when you want to store a “possibly wrong” value which the user has specified (for example, in a web form) in the database for future processing. Under this mode, MySQL verifies only that the month is in the range from 0 to 12 and that the day is in the range from 0 to 31. These ranges are defined to include zero because MySQL allows you to store dates where the day or month and day are zero in a or column. This is extremely useful for applications that need to store a birthdate for which you do not know the exact date. In this case, you simply store the date as or . If you store dates such as these, you should not expect to get correct results for functions such as or that require complete dates. (If you do not want to allow zero in dates, you can use the SQL mode).

MySQL also allows you to store as a “dummy date” (if you are not using the SQL mode). This is in some cases is more convenient (and uses less space in data and index) than using values.

Here are some general considerations to keep in mind when working with date and time types:

  • MySQL retrieves values for a given date or time type in a standard output format, but it attempts to interpret a variety of formats for input values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). Only the formats described in the following sections are supported. It is expected that you supply legal values. Unpredictable results may occur if you use values in other formats.

  • Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules:

    • Year values in the range are converted to .

    • Year values in the range are converted to .

  • Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, ), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, , ).

  • MySQL automatically converts a date or time type value to a number if the value is used in a numeric context and vice versa.

  • By default, when MySQL encounters a value for a date or time type that is out of range or otherwise illegal for the type (as described at the beginning of this section), it converts the value to the “zero” value for that type. The exception is that out-of-range values are clipped to the appropriate endpoint of the range.

    The following table shows the format of the “zero” value for each type. Note that the use of these values produces warnings if the SQL mode is enabled.

    Data Type “Zero” Value

  • The “zero” values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values or , which are easier to write.

  • “Zero” date or time values used through MyODBC are converted automatically to in MyODBC 2.50.12 and above, because ODBC cannot handle such values.

11.3.1. The DATETIME, DATE, and TIMESTAMP Types

The , , and types are related. This section describes their characteristics, how they are similar, and how they differ.

The type is used when you need values that contain both date and time information. MySQL retrieves and displays values in format. The supported range is to .

The type is used when you need only a date value, without a time part. MySQL retrieves and displays values in format. The supported range is to .

For the and range descriptions, “supported” means that although earlier values might work, there is no guarantee.

The data type has varying properties, depending on the MySQL version and the SQL mode the server is running in. These properties are described later in this section.

You can specify , , and values using any of a common set of formats:

  • As a string in either or format. A “relaxed” syntax is allowed: Any punctuation character may be used as the delimiter between date parts or time parts. For example, , , , and are equivalent.

  • As a string in either or format. A “relaxed” syntax is allowed here, too. For example, , , , and are equivalent.

  • As a string with no delimiters in either or format, provided that the string makes sense as a date. For example, and are interpreted as , but is illegal (it has a nonsensical minute part) and becomes .

  • As a string with no delimiters in either or format, provided that the string makes sense as a date. For example, and are interpreted as , but is illegal (it has nonsensical month and day parts) and becomes .

  • As a number in either or format, provided that the number makes sense as a date. For example, and are interpreted as .

  • As a number in either or format, provided that the number makes sense as a date. For example, and are interpreted as .

  • As the result of a function that returns a value that is acceptable in a , , or context, such as or .

Illegal , , or values are converted to the “zero” value of the appropriate type ( or ).

For values specified as strings that include date part delimiters, it is not necessary to specify two digits for month or day values that are less than . is the same as . Similarly, for values specified as strings that include time part delimiters, it is not necessary to specify two digits for hour, minute, or second values that are less than . is the same as .

Values specified as numbers should be 6, 8, 12, or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be in or format and that the year is given by the first 4 digits. If the number is 6 or 12 digits long, it is assumed to be in or format and that the year is given by the first 2 digits. Numbers that are not one of these lengths are interpreted as though padded with leading zeros to the closest length.

Values specified as non-delimited strings are interpreted using their length as given. If the string is 8 or 14 characters long, the year is assumed to be given by the first 4 characters. Otherwise, the year is assumed to be given by the first 2 characters. The string is interpreted from left to right to find year, month, day, hour, minute, and second values, for as many parts as are present in the string. This means you should not use strings that have fewer than 6 characters. For example, if you specify , thinking that represents March, 1999, MySQL inserts a “zero” date value into your table. This occurs because the year and month values are and , but the day part is completely missing, so the value is not a legal date. However, you can explicitly specify a value of zero to represent missing month or day parts. For example, you can use to insert the value .

You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:

  • If you assign a value to a or object, the time part of the resulting value is set to because the value contains no time information.

  • If you assign a or value to a object, the time part of the resulting value is deleted because the type stores no time information.

  • Remember that although , , and values all can be specified using the same set of formats, the types do not all have the same range of values. For example, values cannot be earlier than or later than . This means that a date such as , while legal as a or value, is not valid as a value and is converted to .

Be aware of certain pitfalls when specifying date values:

  • The relaxed format allowed for values specified as strings can be deceiving. For example, a value such as might look like a time value because of the ‘’ delimiter, but if used in a date context is interpreted as the year . The value is converted to because is not a legal month.

  • As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as are converted to and a warning is generated. With strict mode enabled, invalid dates generate an error. To allow such dates, enable . See Section 5.2.5, “The Server SQL Mode”, for more information.

    Before MySQL 5.0.2, the MySQL server performs only basic checking on the validity of a date: The ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date containing parts not within these ranges is subject to conversion to . Please note that this still allows you to store invalid dates such as . To ensure that a date is valid, you should perform a check in your application.

  • Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules:

    • Year values in the range are converted to .

    • Year values in the range are converted to .

11.3.1.1.  Properties as of MySQL 4.1

Note: In older versions of MySQL (prior to 4.1), the properties of the data type differed significantly in many ways from what is described in this section. If you need to convert older data to work with MySQL 5.0, be sure to see the MySQL 3.23, 4.0, 4.1 Reference Manual for details.

columns are displayed in the same format as columns. In other words, the display width is fixed at 19 characters, and the format is .

The MySQL server can be also be run with the SQL mode enabled. When the server runs with this mode enabled, is identical with . That is, if this mode is enabled at the time that a table is created, columns are created as columns. As a result, such columns use display format, have the same range of values, and there is no automatic initialization or updating to the current date and time.

To enable mode, set the server SQL mode to at startup using the server option or by setting the global variable at runtime: 

mysql>

A client can cause the server to run in mode for its own connection as follows: 

mysql>

Note that the information in the following discussion applies to columns only for tables not created with mode enabled, because such columns are created as columns.

As of MySQL 5.0.2, MySQL does not accept timestamp values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special value .

You have considerable flexibility in determining when automatic initialization and updating occur and which column should have those behaviors:

  • For one column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

  • You can specify which column to automatically initialize or update to the current date and time. This need not be the first column.

The following rules govern initialization and updating of columns:

  • If a value is specified for the first column in a table, it is not ignored. The default can be or a constant date and time value.

  • is the same as for the first column. For any other column, is treated as .

  • Any single column in a table can be used as the one that is initialized to the current timestamp or updated automatically.

  • In a statement, the first column can be declared in any of the following ways:

    • With both and clauses, the column has the current timestamp for its default value, and is automatically updated.

    • With neither nor clauses, it is the same as .

    • With a clause and no clause, the column has the current timestamp for its default value but is not automatically updated.

    • With no clause and with an clause, the column has a default of 0 and is automatically updated.

    • With a constant value, the column has the given default. If the column has an clause, it is automatically updated, otherwise not.

    In other words, you can use the current timestamp for both the initial value and the auto-update value, or either one, or neither. (For example, you can specify to enable auto-update without also having the column auto-initialized.)

  • or any of its synonyms (, , , , , or ) can be used in the and clauses. They all mean “the current timestamp.” ( is not allowed. Its range of values does not align with those of the column anyway unless the current time zone is .)

  • The order of the and attributes does not matter. If both and are specified for a column, either can precede the other. For example, these statements are equivalent: 

    CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                             DEFAULT CURRENT_TIMESTAMP);

  • To specify automatic default or updating for a column other than the first one, you must suppress the automatic initialization and update behaviors for the first column by explicitly assigning it a constant value (for example, or ). Then, for the other column, the rules are the same as for the first column, except that if you omit both of the and clauses, no automatic initialization or updating occurs.

    Example. These statements are equivalent: 

    CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                  DEFAULT CURRENT_TIMESTAMP);

You can set the current time zone on a per-connection basis, as described in Section 5.11.8, “MySQL Server Time Zone Support”. values are stored in UTC, being converted from the current time zone for storage, and converted back to the current time zone upon retrieval. As long as the time zone setting remains constant, you get back the same value you store. If you store a value, and then change the time zone and retrieve the value, the retrieved value is different than the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the system variable.

You can include the attribute in the definition of a column to allow the column to contain values. For example: 

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

If the attribute is not specified, setting the column to sets it to the current timestamp. Note that a column which allows values will not take on the current timestamp except under one of the following conditions:

  • Its default value is defined as

  • or is inserted into the column

In other words, a column defined as will auto-initialize only if it is created using a definition such as the following: 

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

Otherwise — that is, if the column is defined to allow values but not using , as shown here… 

CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL);
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');

…then you must explicitly insert a value corresponding to the current date and time. For example: 

INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);

11.3.2. The TIME Type

MySQL retrieves and displays values in format (or format for large hours values). values may range from to . The hours part may be so large because the type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

You can specify values in a variety of formats:

  • As a string in format. You can also use one of the following “relaxed” syntaxes: , , , , , , or . Here represents days and can have a value from 0 to 34. Note that MySQL does not store the fraction part.

  • As a string with no delimiters in format, provided that it makes sense as a time. For example, is understood as , but is illegal (it has a nonsensical minute part) and becomes .

  • As a number in format, provided that it makes sense as a time. For example, is understood as . The following alternative formats are also understood: , , , . Note that MySQL does not store the fraction part.

  • As the result of a function that returns a value that is acceptable in a context, such as .

For values specified as strings that include a time part delimiter, it is not necessary to specify two digits for hours, minutes, or seconds values that are less than . is the same as .

Be careful about assigning abbreviated values to a column. Without colons, MySQL interprets values using the assumption that the two rightmost digits represent seconds. (MySQL interprets values as elapsed time rather than as time of day.) For example, you might think of and as meaning (12 minutes after 11 o'clock), but MySQL interprets them as (11 minutes, 12 seconds). Similarly, and are interpreted as . values with colons, by contrast, are always treated as time of the day. That is, mean , not .

By default, values that lie outside the range but are otherwise legal are clipped to the closest endpoint of the range. For example, and are converted to and . Illegal values are converted to . Note that because is itself a legal value, there is no way to tell, from a value of stored in a table, whether the original value was specified as or whether it was illegal.

For more restrictive treatment of invalid values, enable strict SQL mode to cause errors to occur. See Section 5.2.5, “The Server SQL Mode”.

11.3.3. The YEAR Type

The type is a one-byte type used for representing years.

MySQL retrieves and displays values in format. The range is to .

You can specify values in a variety of formats:

  • As a four-digit string in the range to .

  • As a four-digit number in the range to .

  • As a two-digit string in the range to . Values in the ranges to and to are converted to values in the ranges to and to .

  • As a two-digit number in the range to . Values in the ranges to and to are converted to values in the ranges to and to . Note that the range for two-digit numbers is slightly different from the range for two-digit strings, because you cannot specify zero directly as a number and have it be interpreted as . You must specify it as a string or or it is interpreted as .

  • As the result of a function that returns a value that is acceptable in a context, such as .

Illegal values are converted to .

11.3.4. Y2K Issues and Date Types

As discussed in Section 1.4.5, “Year 2000 Compliance”, MySQL itself is year 2000 (Y2K) safe. However, particular input values presented to MySQL may not be Y2K safe. Any value containing a two-digit year is ambiguous, because the century is unknown. Such values must be interpreted into four-digit form because MySQL stores years internally using four digits.

For , , , and types, MySQL interprets dates with ambiguous year values using the following rules:

  • Year values in the range are converted to .

  • Year values in the range are converted to .

Remember that these rules are only heuristics that provide reasonable guesses as to what your data values mean. If the rules used by MySQL do not produce the correct values, you should provide unambiguous input containing four-digit year values.

properly sorts values that have two-digit years.

Some functions like and convert a to a number. This means that a value with a two-digit year does not work properly with these functions. The fix in this case is to convert the or to four-digit year format.

previous page start next page