Rechercher dans le manuel MySQL
11.3.6 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 valid 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 valid as a
DATE
or
DATETIME
value, is not valid as a
TIMESTAMP
value and is converted
to 0
.
Conversion of DATE
values:
Conversion of DATETIME
and
TIMESTAMP
values:
Conversion to a
DATE
value takes fractional seconds into account and rounds the time part. For example,'1999-12-31 23:59:59.499'
becomes'1999-12-31'
, whereas'1999-12-31 23:59:59.500'
becomes'2000-01-01'
.Conversion to a
TIME
value discards the date part because theTIME
type contains no date information.
For conversion of TIME
values to
other temporal types, the value of
CURRENT_DATE()
is used for the
date part. The TIME
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 to
DATETIME
or
TIMESTAMP
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
to
DATE
is similar but discards the
time part from the result: '2012-01-01'
,
'2012-01-02'
, and
'2011-12-31'
, respectively.
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:
Conversion of TIME
and
DATETIME
values to numeric form
(for example, by adding +0
) depends on
whether the value contains a fractional seconds part.
TIME(
or
N
)DATETIME(
is converted to integer when N
)N
is 0
(or omitted) and to a DECIMAL
value with
N
decimal digits when
N
is greater than 0:
- +-----------+-------------+--------------+
- +-----------+-------------+--------------+
- | 09:28:00 | 92800 | 92800.887 |
- +-----------+-------------+--------------+
- +---------------------+----------------+--------------------+
- +---------------------+----------------+--------------------+
- | 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
- +---------------------+----------------+--------------------+
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-date-and-time-type-conversion.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.