TIMESTAMP and YEAR Types
Welcome back to this series on working with dates and times in MySQL. In the first two installments, we're looking at MySQL's temporal data types. Part 1 covered the DATE, TIME, and DATETIME data types, while this installment will cover the remaining TIMESTAMP and YEAR types.
The TIMESTAMP Type
The TIMESTAMP type is similar to DATETIME in MySQL in that both are temporal data types that hold a combination of date and time. This begs the question why have two types for the same information? For starters, timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed, whereas datetimes are used to store a specific temporal values. Another way to think about it is that DATETIME represents a date (as found in a calendar) and a time (as seen on a wall clock), while TIMESTAMP represents a well defined point in time. This distinction could be very important if your application handles timezones, as how long ago was '2009-11-01 14:35:00' depends on what timezone you're in. Meanwhile, 1248761460 seconds since '1970-01-01 00:00:00 UTC' always refers to the same point in time.
In terms of storage, a TIMESTAMP requires 4 bytes while DATETIME requires 5. TIMESTAMP columns store 14 characters, but you can display it in different ways, depending on how you define it. For example, if you define the column as TIMESTAMP(2), only the two-digit year will be displayed (even though the full value is stored). The advantage to this approach is that, if you later decide to display the full value, you can change the table definition, and the full value will appear.
Below is a list of various ways to define a TIMESTAMP, and the resultant display format:
- TIMESTAMP(14): YYYY-MM-DD HH:MM:SS
- TIMESTAMP(12): YY-MM-DD HH:MM:SS
- TIMESTAMP(10): YY-MM-DD HH:MM
- TIMESTAMP(8): YYYY-MM-DD
- TIMESTAMP(6): YY-MM-DD
- TIMESTAMP(4): YY-MM
- TIMESTAMP(2): YY
In the Navicat 16 Table Designer, a timestamp's precision may be defined in the Length column:
If no Length is supplied, as in the above example, Navicat displays the full field, as if it was declared as TIMESTAMP(14):
The YEAR Type
Many DBAs opt to store years as integers. While that can certainly work, it is more efficient to use MySQL's dedicate YEAR type for that purpose, as the YEAR type uses a mere 1 byte. It can be declared as YEAR(2) or YEAR(4) to specify a display width of two or four characters. If no width is given the default is four characters. YEAR(4) and YEAR(2) have different display formats but have the same range of values:
- For 4-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000.
- For 2-digit format, MySQL displays only the last two (least significant) digits; for example, 70 (1970 or 2070) or 69 (2069).
Here's an example of a year column in the Navicat Table Designer with a four digit format:
As a result, we see the full year in the table:
Conclusion
That concludes our exploration of the five MySQL temporal data types. The next installment will cover some useful date and time functions.
