The date and time types for representing temporal values are DATETIME
, DATE
, TIMESTAMP
, TIME
, and YEAR
. Each temporal type has a range of legal values, as well as a ``zero'' value that is used when you specify an illegal value that MySQL cannot represent. The TIMESTAMP
type has special automatic updating behavior, described later on.
Starting from MySQL 5.0.2, MySQL gives warnings/errors if you try to insert an illegal date. You can get MySQL to accept certain dates, such as '1999-11-31'
, by using the ALLOW_INVALID_DATES
SQL mode. (Before 5.0.2, this mode was the default behavior for MySQL). This is useful when you want to store the ``possibly wrong'' value 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 DATE
or DATETIME
column. This is extremely useful for applications that need to store a birthdate for which you don't know the exact date. In this case, you simply store the date as '1999-00-00'
or '1999-01-00'
. If you store dates such as these, you should not expect to get correct results for functions such as DATE_SUB()
or DATE_ADD
that require complete dates. (If you don't want to allow zero in dates, you can use the NO_ZERO_IN_DATE
SQL mode).
MySQL also allows you to store '0000-00-00'
as a ``dummy date'' (if you are not using the NO_ZERO_DATE
SQL mode). This is in some cases is more convenient (and uses less space in data and index) than using NULL
values.
By setting the sql_mode
system variable to the appropriate mode values, You can more exactly what kind of dates you want MySQL to support. See Section 5.3.2, “The Server SQL Mode”.
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, and 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
00-69
are converted to2000-2069
. -
Year values in the range
70-99
are converted to1970-1999
.
-
-
Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example,
'98-09-04'
), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example,'09-04-98'
,'04-09-98'
). -
MySQL automatically converts a date or time type value to a number if the value is used in a numeric context and vice versa.
-
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
TIME
values are clipped to the appropriate endpoint of theTIME
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
NO_ZERO_DATE
SQL mode is enabled.Column Type ``Zero'' ValueDATETIME
'0000-00-00 00:00:00'
DATE
'0000-00-00'
TIMESTAMP
00000000000000
TIME
'00:00:00'
YEAR
0000
-
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
'0'
or0
, which are easier to write. -
``Zero'' date or time values used through MyODBC are converted automatically to
NULL
in MyODBC 2.50.12 and above, because ODBC can't handle such values.
The DATETIME
, DATE
, and TIMESTAMP
types are related. This section describes their characteristics, how they are similar, and how they differ.
The DATETIME
type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME
values in 'YYYY-MM-DD HH:MM:SS'
format. The supported range is '1000-01-01 00:00:00'
to '9999-12-31 23:59:59'
. (``Supported'' means that although earlier values might work, there is no guarantee)
The DATE
type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE
values in 'YYYY-MM-DD'
format. The supported range is '1000-01-01'
to '9999-12-31'
.
The TIMESTAMP
column 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 DATETIME
, DATE
, and TIMESTAMP
values using any of a common set of formats:
-
As a string in either
'YYYY-MM-DD HH:MM:SS'
or'YY-MM-DD HH:MM:SS'
format. A ``relaxed'' syntax is allowed: Any punctuation character may be used as the delimiter between date parts or time parts. For example,'98-12-31 11:30:45'
,'98.12.31 11+30+45'
,'98/12/31 11*30*45'
, and'98@12@31 11^30^45'
are equivalent. -
As a string in either
'YYYY-MM-DD'
or'YY-MM-DD'
format. A ``relaxed'' syntax is allowed here, too. For example,'98-12-31'
,'98.12.31'
,'98/12/31'
, and'98@12@31'
are equivalent. -
As a string with no delimiters in either
'YYYYMMDDHHMMSS'
or'YYMMDDHHMMSS'
format, provided that the string makes sense as a date. For example,'19970523091528'
and'970523091528'
are interpreted as'1997-05-23 09:15:28'
, but'971122129015'
is illegal (it has a nonsensical minute part) and becomes'0000-00-00 00:00:00'
. -
As a string with no delimiters in either
'YYYYMMDD'
or'YYMMDD'
format, provided that the string makes sense as a date. For example,'19970523'
and'970523'
are interpreted as'1997-05-23'
, but'971332'
is illegal (it has nonsensical month and day parts) and becomes'0000-00-00'
. -
As a number in either
YYYYMMDDHHMMSS
orYYMMDDHHMMSS
format, provided that the number makes sense as a date. For example,19830905132800
and830905132800
are interpreted as'1983-09-05 13:28:00'
. -
As a number in either
YYYYMMDD
orYYMMDD
format, provided that the number makes sense as a date. For example,19830905
and830905
are interpreted as'1983-09-05'
. -
As the result of a function that returns a value that is acceptable in a
DATETIME
,DATE
, orTIMESTAMP
context, such asNOW()
orCURRENT_DATE
.
Illegal DATETIME
, DATE
, or TIMESTAMP
values are converted to the ``zero'' value of the appropriate type ('0000-00-00 00:00:00'
, '0000-00-00'
, or 00000000000000
).
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 10
. '1979-6-9'
is the same as '1979-06-09'
. 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 10
. '1979-10-30 1:2:3'
is the same as '1979-10-30 01:02:03'
.
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 YYYYMMDD
or YYYYMMDDHHMMSS
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 YYMMDD
or YYMMDDHHMMSS
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 '9903'
, thinking that represents March, 1999, MySQL inserts a ``zero'' date into your table. This is because the year and month values are 99
and 03
, but the day part is completely missing, so the value is not a legal date. However, as of MySQL 3.23, you can explicitly specify a value of zero to represent missing month or day parts. For example, you can use '990300'
to insert the value '1999-03-00'
.
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
DATE
value to aDATETIME
orTIMESTAMP
object, the time part of the resulting value is set to'00:00:00'
because theDATE
value contains no time information. -
If you assign a
DATETIME
orTIMESTAMP
value to aDATE
object, the time part of the resulting value is deleted because theDATE
type stores no time information. -
Remember that although
DATETIME
,DATE
, andTIMESTAMP
values all can be specified using the same set of formats, the types do not all have the same range of values. For example,TIMESTAMP
values cannot be earlier than1970
or later than2037
. This means that a date such as'1968-01-01'
, while legal as aDATETIME
orDATE
value, is not a validTIMESTAMP
value and is converted to0
if assigned to such an object.
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
'10:11:12'
might look like a time value because of the ':
' delimiter, but if used in a date context is interpreted as the year'2010-11-12'
. The value'10:45:15'
is converted to'0000-00-00'
because'45'
is not a legal month. -
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
'0000-00-00'
. Please note that this still allows you to store invalid dates such as'2002-04-31'
. To ensure that a date is valid, 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
00-69
are converted to2000-2069
. -
Year values in the range
70-99
are converted to1970-1999
.
-
The TIMESTAMP
column type provides a type that you can use to automatically mark INSERT
or UPDATE
operations with the current date and time. If you have multiple TIMESTAMP
columns in a table, only the first one is updated automatically. (From MySQL 4.1.2 on, you can specify which TIMESTAMP
column updates; see Section 11.3.1.2, “TIMESTAMP
Properties as of MySQL 4.1”.)
Automatic updating of the first TIMESTAMP
column in a table occurs under any of the following conditions:
-
You explicitly set the column to
NULL
. -
The column is not specified explicitly in an
INSERT
orLOAD DATA INFILE
statement. -
The column is not specified explicitly in an
UPDATE
statement and some other column changes value. AnUPDATE
that sets a column to the value it does not cause theTIMESTAMP
column to be updated; if you set a column to its current value, MySQL ignores the update for efficiency.
A TIMESTAMP
column other than the first also can be assigned the current date and time by setting it to NULL
or to any function that produces the current date and time (NOW()
, CURRENT_TIMESTAMP
).
You can set any TIMESTAMP
column to a value different from the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP
column. You can use this property if, for example, you want a TIMESTAMP
to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later:
-
Let MySQL set the column when the row is created. This initializes it to the current date and time.
-
When you perform subsequent updates to other columns in the row, set the
<PRE class=programlisting>UPDATETIMESTAMP
column explicitly to its current value:tbl_name
SETtimestamp_col
=timestamp_col
,other_col1
=new_value1
,other_col2
=new_value2
, ...</PRE>
Another way to maintain a column that records row-creation time is to use a DATETIME
column that you initialize to NOW()
when the row is created and do not modify for subsequent updates.
TIMESTAMP
values may range from the beginning of 1970 to partway through the year 2037, with a resolution of one second. Values are displayed as numbers. When you store a value in a TIMESTAMP
column, it is assumed to be represented in the current time zone, and is converted to UTC for storage. When you retrieve the value, it is converted from UTC back to the local time zone for display. Before MySQL 4.1.3, the server has a single time zone. As of 4.1.3, clients can set their time zone on a per-connection basis, as described in Time zone support
.
The format in which MySQL retrieves and displays TIMESTAMP
values depends on the display size, as illustrated by the following table. The ``full'' TIMESTAMP
format is 14 digits, but TIMESTAMP
columns may be created with shorter display sizes:
Column Type | Display Format |
TIMESTAMP(14) |
YYYYMMDDHHMMSS |
TIMESTAMP(12) |
YYMMDDHHMMSS |
TIMESTAMP(10) |
YYMMDDHHMM |
TIMESTAMP(8) |
YYYYMMDD |
TIMESTAMP(6) |
YYMMDD |
TIMESTAMP(4) |
YYMM |
TIMESTAMP(2) |
YY |
All TIMESTAMP
columns have the same storage size, regardless of display size. The most common display sizes are 6, 8, 12, and 14. You can specify an arbitrary display size at table creation time, but values of 0 or greater than 14 are coerced to 14. Odd-valued sizes in the range from 1 to 13 are coerced to the next higher even number.
TIMESTAMP
columns store legal values using the full precision with which the value was specified, regardless of the display size. This has several implications:
-
Always specify year, month, and day, even if your column types are
TIMESTAMP(4)
orTIMESTAMP(2)
. Otherwise, the value is not a legal date and0
is stored. -
If you use
ALTER TABLE
to widen a narrowTIMESTAMP
column, information is displayed that previously was ``hidden.'' -
Similarly, narrowing a
TIMESTAMP
column does not cause information to be lost, except in the sense that less information is shown when the values are displayed. -
If you are planning to use
mysqldump
for the database, do not useTIMESTAMP(4)
orTIMESTAMP(2)
. The display format for these column types are not legal dates and0
will be stored instead. This inconsistency is fixed starting with MySQL 4.1, where display width is ignored. To prepare for transition to versions after 4.0, you should change to use display widths of 6 or more, which will produce a legal display format. You can change the display width ofTIMESTAMP
column types, without losing any information, by usingALTER TABLE
as indicated above.If you need to print the timestamps for external applications, you can use
MID()
to extract the relevant part of the timestamp: for example, to imitate theTIMESTAMP(4)
display format. -
Although
TIMESTAMP
values are stored to full precision, the only function that operates directly on the underlying stored value isUNIX_TIMESTAMP()
. Other functions operate on the formatted retrieved value. This means you cannot use a function such asHOUR()
orSECOND()
unless the relevant part of theTIMESTAMP
value is included in the formatted value. For example, theHH
part of aTIMESTAMP
column is not displayed unless the display size is at least 10, so trying to useHOUR()
on shorterTIMESTAMP
values produces a meaningless result.
In MySQL 4.1, TIMESTAMP
display format changes to be the same as DATETIME
, that is, as a string in 'YYYY-MM-DD HH:MM:SS'
format rather than as a number in YYYYMMDDHHMMSS
format. To test applications written for MySQL 4.0 for compatibility with this change, you can set the new
system variable to 1. This variable is available beginning with MySQL 4.0.12. It can be set at server startup by specifying the --new
option to mysqld. At runtime, a user who has the SUPER
privilege can set the global value with a SET
statement:
Any client can set its session value of new
as follows:
The general effect of setting new
to 1 is that values for a TIMESTAMP
column display as strings rather than as numbers. Also, DESCRIBE
displays the column definition as timestamp(19)
, rather than as timestamp(14)
.
However, the effect differs somewhat for TIMESTAMP
columns that are created while new
is set to 1. In this case, column values display as strings and DESCRIBE
shows the definition as timestamp(19)
, regardless of the current value of new
.
In other words, with new=1
, all TIMESTAMP
values display as strings and DESCRIBE
shows a display width of 19. For columns created while new=1
, they continue to display as strings and to have a display width of 19 even if new
is set to 0.
For a TIMESTAMP
column that displays as a string, you can display it as a number by retrieving it as
. col_name
+0
In MySQL 4.1 and up, the properties of the TIMESTAMP
column type change in the ways described in this section.
From MySQL 4.1.0 on, TIMESTAMP
display format differs from that of earlier MySQL releases:
-
TIMESTAMP
columns are displayed in the same format asDATETIME
columns. -
Display widths (used as described in the preceding section) are no longer supported. In other words, for declarations such as
TIMESTAMP(2)
,TIMESTAMP(4)
, and so on, the display width is ignored.
Beginning with MySQL 4.1.1, the MySQL server can be run in MAXDB
mode. When the server runs in this mode, TIMESTAMP
is identical with DATETIME
. That is, if the server is running in MAXDB
mode at the time that a table is created, TIMESTAMP
columns are created as DATETIME
columns. As a result, such columns use DATETIME
display format, have the same range of values, and there is no automatic initialization or updating to the current date and time.
To enable MAXDB
mode, set the server SQL mode to MAXDB
at startup using the --sql-mode=MAXDB
server option or by setting the global sql_mode
variable at runtime:
A client can cause the server to run in MAXDB
mode for its own connection as follows:
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 exception is the special value '0000-00-00 00:00:00'
.)
Beginning with MySQL 4.1.2, you have more flexible control over when automatic TIMESTAMP
initialization and updating occur and which column should have those behaviors:
-
You can assign the current timestamp as the default value and the auto-update value, as before. But it is possible to have just one automatic behavior or the other, or neither of them. (It is not possible to have one behavior for one column and the other for another column.)
-
You can specify which
TIMESTAMP
column to automatically initialize or update to the current date and time. This no longer need be the firstTIMESTAMP
column.
The following discussion describes the revised syntax and behavior. Note that this information applies only to TIMESTAMP
columns for tables not created with MAXDB
mode enabled. As noted earlier in this section, MAXDB
mode causes columns to be created as DATETIME
columns.
The following items summarize the pre-4.1.2 properties for TIMESTAMP
initialization and updating:
The first TIMESTAMP
column in table row automatically is set to the current timestamp when the record is created if the column is set to NULL
or is not specified at all.
The first TIMESTAMP
column in table row automatically is updated to the current timestamp when the value of any other column in the row is changed, unless the TIMESTAMP
column explicitly is assigned a value other than NULL
.
If a DEFAULT
value is specified for the first TIMESTAMP
column when the table is created, it is silently ignored.
Other TIMESTAMP
columns in the table can be set to the current TIMESTAMP
by assigning NULL
to them, but they do not update automatically.
As of 4.1.2, you have more flexibility in deciding which TIMESTAMP
column automatically is initialized and updated to the current timestamp. The rules are as follows:
If a DEFAULT
value is specified for the first TIMESTAMP
column in a table, it is not ignored. The default can be CURRENT_TIMESTAMP
or a constant date and time value.
DEFAULT NULL
is the same as DEFAULT CURRENT_TIMESTAMP
for the first TIMESTAMP
column. For any other TIMESTAMP
column, DEFAULT NULL
is treated as DEFAULT 0
.
Any single TIMESTAMP
column in a table can be set to be the one that is initialized to the current timestamp and/or updated automatically.
In a CREATE TABLE
statement, the first TIMESTAMP
column can be declared in any of the following ways:
-
With both
DEFAULT CURRENT_TIMESTAMP
andON UPDATE CURRENT_TIMESTAMP
clauses, the column has the current timestamp for its default value, and is automatically updated. -
With neither
DEFAULT
norON UPDATE
clauses, it is the same asDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
. -
With a
DEFAULT CURRENT_TIMESTAMP
clause and noON UPDATE
clause, the column has the current timestamp for its default value but is not automatically updated. -
With no
DEFAULT
clause and with anON UPDATE CURRENT_TIMESTAMP
clause, the column has a default of 0 and is automatically updated. -
With a constant
DEFAULT
value, the column has the given default. If the column has anON UPDATE CURRENT_TIMESTAMP
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 ON UPDATE
to get auto-update without also having the column auto-initialized.)
Any of CURRENT_TIMESTAMP
, CURRENT_TIMESTAMP()
, or NOW()
can be used in the DEFAULT
and ON UPDATE
clauses. They all have the same effect.
The order of the two attributes does not matter. If both DEFAULT
and ON UPDATE
are specified for a TIMESTAMP
column, either can precede the other.
Example. These statements are equivalent:
<PRE class=programlisting>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);</PRE>To specify automatic default or updating for a TIMESTAMP
column other than the first one, you must suppress the automatic initialization and update behaviors for the first TIMESTAMP
column by explicitly assigning it a constant DEFAULT
value (for example, DEFAULT 0
or DEFAULT '2003-01-01 00:00:00'
). Then for the other TIMESTAMP
column, the rules are the same as for the first TIMESTAMP
column, except that you cannot omit both of the DEFAULT
and ON UPDATE
clauses. If you do that, no automatic initialization or updating occurs.
Example. These statements are equivalent:
<PRE class=programlisting>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);</PRE>Beginning with MySQL 4.1.3, you can set the current time zone on a per-connection basis, as described in Time zone support
. TIMESTAMP
values still are stored in UTC, but are converted from the current time zone for storage, and converted back to the current time zone for retrieval. As long as the time zone setting remains the same, you get back the same value you store. If you store a TIMESTAMP
value, then change the time zone and retrieve the value, it is different than the value you stored. This occurs because the same time zone is not used for conversion in both directions. The current time zone is available as the value of the time_zone
system variable.
Beginning with MySQL 4.1.6, you can include the NULL
attribute in the definition of a TIMESTAMP
column to allow the column to contain NULL
values. For example:
Before MySQL 4.1.6 (and even as of 4.1.6 if the NULL
attribute is not specified), setting the column to NULL
sets it to the current timestamp.
MySQL retrieves and displays TIME
values in 'HH:MM:SS'
format (or 'HHH:MM:SS'
format for large hours values). TIME
values may range from '-838:59:59'
to '838:59:59'
. The reason the hours part may be so large is that the TIME
type may 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 TIME
values in a variety of formats:
-
As a string in
'D HH:MM:SS.fraction'
format. You can also use one of the following ``relaxed'' syntaxes:'HH:MM:SS.fraction'
,'HH:MM:SS'
,'HH:MM'
,'D HH:MM:SS'
,'D HH:MM'
,'D HH'
, or'SS'
. HereD
represents days and can have a value from 0 to 34. Note that MySQL doesn't yet store the fraction part. -
As a string with no delimiters in
'HHMMSS'
format, provided that it makes sense as a time. For example,'101112'
is understood as'10:11:12'
, but'109712'
is illegal (it has a nonsensical minute part) and becomes'00:00:00'
. -
As a number in
HHMMSS
format, provided that it makes sense as a time. For example,101112
is understood as'10:11:12'
. The following alternative formats are also understood:SS
,MMSS
,HHMMSS
,HHMMSS.fraction
. Note that MySQL doesn't yet store the fraction part. -
As the result of a function that returns a value that is acceptable in a
TIME
context, such asCURRENT_TIME
.
For TIME
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 10
. '8:3:2'
is the same as '08:03:02'
.
Be careful about assigning ``short'' TIME
values to a TIME
column. Without colons, MySQL interprets values using the assumption that the rightmost digits represent seconds. (MySQL interprets TIME
values as elapsed time rather than as time of day.) For example, you might think of '1112'
and 1112
as meaning '11:12:00'
(12 minutes after 11 o'clock), but MySQL interprets them as '00:11:12'
(11 minutes, 12 seconds). Similarly, '12'
and 12
are interpreted as '00:00:12'
. TIME
values with colons, by contrast, are always treated as time of the day. That is '11:12'
mean '11:12:00'
, not '00:11:12'
.
Values that lie outside the TIME
range but are otherwise legal are clipped to the closest endpoint of the range. For example, '-850:00:00'
and '850:00:00'
are converted to '-838:59:59'
and '838:59:59'
.
Illegal TIME
values are converted to '00:00:00'
. Note that because '00:00:00'
is itself a legal TIME
value, there is no way to tell, from a value of '00:00:00'
stored in a table, whether the original value was specified as '00:00:00'
or whether it was illegal.
The YEAR
type is a one-byte type used for representing years.
MySQL retrieves and displays YEAR
values in YYYY
format. The range is 1901
to 2155
.
You can specify YEAR
values in a variety of formats:
-
As a four-digit string in the range
'1901'
to'2155'
. -
As a four-digit number in the range
1901
to2155
. -
As a two-digit string in the range
'00'
to'99'
. Values in the ranges'00'
to'69'
and'70'
to'99'
are converted toYEAR
values in the ranges2000
to2069
and1970
to1999
. -
As a two-digit number in the range
1
to99
. Values in the ranges1
to69
and70
to99
are converted toYEAR
values in the ranges2001
to2069
and1970
to1999
. 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 as2000
. You must specify it as a string'0'
or'00'
or it is interpreted as0000
. -
As the result of a function that returns a value that is acceptable in a
YEAR
context, such asNOW()
.
Illegal YEAR
values are converted to 0000
.
MySQL itself is year 2000 (Y2K) safe (see Section 1.4.5, “Year 2000 Compliance”), but input values presented to MySQL may not be. Any input containing two-digit year values 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 DATETIME
, DATE
, TIMESTAMP
, and YEAR
types, MySQL interprets dates with ambiguous year values using the following rules:
-
Year values in the range
00-69
are converted to2000-2069
. -
Year values in the range
70-99
are converted to1970-1999
.
Remember that these rules provide only reasonable guesses as to what your data values mean. If the heuristics used by MySQL do not produce the correct values, you should provide unambiguous input containing four-digit year values.
ORDER BY
properly sorts TIMESTAMP
or YEAR
values that have two-digit years.
Some functions like MIN()
and MAX()
convert a TIMESTAMP
or YEAR
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 TIMESTAMP
or YEAR
to four-digit year format or use something like MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS))
.
[출처] modestia2004
"쇼핑몰·홈페이지·오픈마켓
블로그·페이스북·이메일 등의 각종 마케팅 글쓰기, 각종 광고, 영업, 판매, 제안서, 전단지 반응율 3배×10배 이상 높이는 마법의 8단계 공식" |
☞자세히보기 |
|
|