Conversion Between Date and Time Types
To some extent, you can convert a value from one temporal type to another. However, there may be some alteration of the value or loss of information. In all cases, conversion between temporal types is subject to the range of legal values for the resulting type. For example, although DATE
, DATETIME
, and TIMESTAMP
values all can be specified using the same set of formats, the types do not all have the same range of values. TIMESTAMP
values cannot be earlier than 1970
UTC or later than '2038-01-19 03:14:07'
UTC. This means that a date such as '1968-01-01'
, while legal as a DATE
or DATETIME
value, is not valid as a TIMESTAMP
value and is converted to 0
.
Conversion of DATE
values:
- Conversion to a
DATETIME
orTIMESTAMP
value adds a time part of'00:00:00'
because theDATE
value contains no time information. - Conversion to a
TIME
value is not useful; the result is'00:00:00'
.
Conversion of DATETIME
and TIMESTAMP
values:
- Conversion to a
DATE
value discards the time part because theDATE
type contains no time information. - Conversion to a
TIME
value discards the date part because theTIME
type contains no date information.
Conversion of TIME
values to other temporal types is version specific:
- As of MariaDB 5.6.4, the value of
CURRENT-DATE()
is used for the date part. TheTIME
is interpreted as elapsed time (not time of day) and added to the date. This means that the date part of the result differs from the current date if the time value is outside the range from'00:00:00'
to'23:59:59'
.Suppose that the current date is
'2012-01-01'
.TIME
values of'12:00:00'
,'24:00:00'
, and'-12:00:00'
, when converted toDATETIME
orTIMESTAMP
values, result in'2012-01-01 12:00:00'
,'2012-01-02 00:00:00'
, and'2011-12-31 12:00:00'
, respectively.Conversion of
TIME
toDATE
is similar but discards the time part from the result:'2012-01-01'
,'2012-01-02'
, and'2011-12-31'
, respectively. - Before 5.6.4, MariaDB converts a time value to a date or date-and-time value by parsing the string value of the time as a date or date-and-time. This is unlikely to be useful. For example,
'23:12:31'
interpreted as a date becomes'2032-12-31'
. Time values not valid as dates become'0000-00-00'
orNULL
.
Explicit conversion can be used to override implicit conversion. For example, in comparison of DATE
and DATETIME
values, the DATE
value is coerced to the DATETIME
type by adding a time part of '00:00:00'
. To perform the comparison by ignoring the time part of the DATETIME
value instead, use the CAST()
function in the following way:
date_col
= CAST(datetime_col
AS DATE)
Conversion of TIME
or DATETIME
values to numeric form (for example, by adding +0
) results in a double-precision value with a microseconds part of .000000
: