DATE, TIME, and DATETIME Types
The vast majority of databases store a great deal of "temporal" data. Temporal data is simply data that represents a state in time. An organization may collect temporal data for a variety of reasons, such as to analyze weather patterns and other environmental variables, monitor traffic conditions, study demographic trends, etc. Businesses also routinely need to store temporal data about when orders were placed, stock refilled, staff hired, and a whole host of other information about their day-to-day business.
You may be surprised to learn that relational databases do not store dates and times in the same way. MySQL is especially prescriptive. For instance, It stores date values using the universal yyyy-mm-dd format. This format is fixed and may not be changed. You may prefer to use a mm-dd-yyyy format, but it is not possible to do so. However, you can use the DATE_FORMAT function to format the date the way you want in the presentation layer, usually an application. In the first two installments on working with Dates and Times in MySQL, we'll be looking at MySQL's temporal data types, starting with DATE, TIME, and DATETIME.
Types At a Glance
MySQL provides five types for storing dates and times, some just for dates, others for time, and some that include both. Here's a table that summarizes each type:
| Type Name | Description |
|---|---|
| DATE |
A date value in YYYY-MM-DD format
|
| TIME |
A time value in hh:mm:ss format
|
| DATETIME |
A date and time value inYYYY-MM-DD
hh:mm:ssformat
|
| TIMESTAMP |
A timestamp value in YYYY-MM-DD hh:mm:ss
format
|
| YEAR |
A year value in YYYY or YY
format
|
The rest of this article will cover the first three types in more detail, while the next one will focus on the other two.
The DATE Type
MySQL uses 3 bytes to store a DATE value. The DATE values range from 1000-01-01 to 9999-12-31. Moreover, when strict mode is disabled, MySQL converts any invalid date e.g., 2015-02-30 to the zero date value 0000-00-00. In Navicat 16, you can select the DATE type in the Table Designer from the Types drop-down:
To set a DATE value, you can simply choose it using the calendar control:
Of course, you can also insert a DATE using the INSERT statement:
The TIME Type
MySQL uses the 'HH:MM:SS' format for querying and displaying a time value that represents a time of day, which is within 24 hours. To represent a time interval between two events, MySQL uses the 'HHH:MM:SS' format, which is larger than 24 hours.
Here is the TIME type in the Navicat 16 Types drop-down:
To set a TIME value, Navicat provides the TIME INPUT control:
Here's an INSERT statement that sets a start and end time:
The DATETIME Type
Quite often, you'll need to store both a date and time. To do that, you can use the MySQL DATETIME type. By default, DATETIME values range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. When you query data from a DATETIME column, MySQL displays the DATETIME value in the same YYYY-MM-DD HH:MM:SS format.
A DATETIME value uses 5 bytes for storage. In addition, a DATETIME value can include a trailing fractional second up to microseconds with the format YYYY-MM-DD HH:MM:SS[.fraction], for example, 2015-12-20 10:01:00.999999.
For inputting DATETIME values, Navicat provides the DATETIME INPUT control, which combines the DATE and TIME controls:
DATETIME values may be set using a string literal that contains the "T" time portion delineator or by casting to a DATETIME:
Going Forward
Having explored the DATE, TIME, and DATETIME Types, the next installment will cover the remaining two temporal types: TIMESTAMP and YEAR.
