![]() 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.īelow is a list of various ways to define a TIMESTAMP, and the resultant display format: 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). TIMESTAMP columns store 14 characters, but you can display it in different ways, depending on how you define it. ![]() In terms of storage, a TIMESTAMP requires 4 bytes while DATETIME requires 5. Meanwhile, 1248761460 seconds since ' 00:00:00 UTC' always refers to the same point in time. This distinction could be very important if your application handles timezones, as how long ago was ' 14:35:00' depends on what timezone you're in. 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 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. The TIMESTAMP type is similar to DATETIME in MySQL in that both are temporal data types that hold a combination of date and time. Part 1 covered the DATE, TIME, and DATETIME data types, while this installment will cover the remaining TIMESTAMP and YEAR types. In the first two installments, we're looking at MySQL's temporal data types. Welcome back to this series on working with dates and times in MySQL. Working with Dates and Times in MySQL - Part 2: TIMESTAMP and YEAR Types by Robert Gravelle TIMESTAMP and YEAR Types
0 Comments
Leave a Reply. |