Database Reference
In-Depth Information
Date and Time Data Types
Because dates and timesare ultimately just strings containing numbers, they could be
stored in a regular character column. However, there are data types designed specifically
for dates and times. By using temporal data type columns, you can make use of several
built-in functions offered by MySQL and MariaDB. So before we start learning about the
date and time functions, let's look at the data types that are available for recording date and
time.
There are five temporal data types in MySQL and MariaDB:
DATE
for storing dates,
TIME
for storing time,
DATETIME
and
TIMESTAMP
for both date and time, and
YEAR
for a
year:
DATE
This records thedate only, in the format
yyyy-mm-dd
. You may prefer a different
format (e.g.,
02-14-2014
for St. Valentine's Day), but you can't change how the date is
stored — at least not without changing the source code of MySQL. But other functions
discussed in this chapter let you display the date in the format you like.
This data type has a limit to the range of dates it will accept. It allows dates from as
early as
1000-01-01
to as late as
9999-12-31
. That's far into the future, but you
wouldn't use this for recording historical dates in the first millennium.
TIME
This records timein the format
hhh:mm:ss
. It accepts times ranging from
-838:59:59
to
838:59:59
. If you give it a time outside of that range or in some
way not valid, it records the time as all zeros.
You may be wondering how you could have a time in which you need three digits for
the hour. This is so that you can record how much time has elapsed for an event or when
comparing two times, rather than just recording the time of day. For instance, you might
want to note that something took 120 hours to complete. You could do this with two
columns, one for recording the start time and the other the end time, and then compare
them as needed. But this data type allows you to record the difference in one column,
rather than recalculate each time you want that result.
DATETIME
This recordsa combination of date and time in the format
yyyy-mm-dd hh:mm:ss
.
It accepts dates and times from
1000-01-01 00:00:00
to
9999-12-31
23:59:59
. That's the same range as
DATE
, but with the addition of the full range of a
24-hour day. As of version 5.6 of MySQL, fractions of a second are possible.
