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.
Search WWH ::




Custom Search