Database Reference
In-Depth Information
TIMESTAMP
This is similarto
DATETIME
, but more limited in its range of allowable time. Despite
the name, it's not limited to time, but covers a range of dates from
1970-01-01
00:00:01 UTC
to
2038-01-19 03:14:07 UTC
. It's meant for relatively cur-
rent dates and corresponds to the “epoch” chosen by the designers of the Unix operat-
ing system. As of version 5.6 of MySQL, fractions of a second are possible.
Although you can set the value of a column manually using this data type, whenever
you insert a row or update a row without specifying an explicit value, MySQL auto-
matically updates the column's value to the current date and time. That can be very
convenient for some applications such as logging, but can cause you problems if
you're unaware of it or don't allow for it. This is only for the first column in a table
which uses
TIMESTAMP
. For subsequent
TIMESTAMP
columns, you would have to
specify a couple of options to have the same effect:
ON UPDATE
CURRENT_TIMESTAMP
and
ON INSERT CURRENT_TIMESTAMP
.
YEAR
This recordsjust a year in a column, in the format
yyyy
. It could be set to two digits
(by defining the column as
YEAR(2)
with an explicit number), but that's deprecated
and causes problems. So don't record years in two-digit formats with this data type.
This data type is also meant for birth years; it allows years from
1901
to
2155
. If you
give it an invalid value or a year outside of the allowed range, it records the year as
0000
.
NOTE
Given some of the limitations of these data types, you may need to use a nontemporal data type for dates
outside of the allowed ranges. You could use the
INT
data type to storeeach component of a date, or
CHAR
data type to store dates in a fixed width. For instance, you might have one
INT
column for storing
the month, another for the day, and one
CHAR(4)
column to store years before the 20th century.
That can work generally, but it can be a problem when you try to do a calculation with these data types.
Suppose you want to store
February 15
in two
INT
columns:
2
in
my_month
and
15
in
my_day
. If
you were to add 20 days to the value of
my_day
, you would get an invalid date of
February 35
. To deal
with this, you would have to construct a complex SQL statement to adjust the
my_day
and the
my_month
columns. Plus, you'd have to update the column you create for the year value when a date
change pushes the values into a different year. You'd have similar problems if you tried to use
INT
to
store times. All of this complexity is eliminated by using temporal data types for columns, so that you
can use date functions provided with MySQL and MariaDB. These types have built-into complex calcu-
lations so that you don't have to worry about that.