Choosing a Temporal Data Type

Problem

You need to store temporal data but aren’t sure which is the most appropriate data type.

Solution

Choose the data type according to the characteristics of the information to be stored and how you need to use it. Consider questions such as these:

  • Do you need times only, dates only, or combined date and time values?

  • What range of values do you require?

  • Do you want automatic initialization of the column to the current date and time?

Discussion

MySQL provides DATE and TIME data types for representing date and time values separately, and DATETIME and TIMESTAMP types for combined date-and-time values. These values have the following characteristics:

  • DATE values are handled as strings inCCYY-MM-DD format, where CC, YY, MM, and DD represent the century, year within century, month, and day parts of the date. The supported range for DATE values is 1000-01-01 to 9999-12-31.

  • TIME values are represented as strings inhh:mm:ss format, where hh, mm, and ss are the hours, minutes, and seconds parts of the time. TIME values often can be thought of as time-of-day values, but MySQL actually treats them as elapsed time. Thus, they may be greater than 23:59:59 or even negative. (The actual range of a TIME column is -838:59:59 to 838:59:59.)

  • DATETIME and TIMESTAMP values are represented as combined date-and-time strings in CCYY-MM-DDhh:mm:ss format. (Before MySQL 4.1, TIMESTAMP display format was CCYYMMDDhhmmss numeric format. Older applications that depend on this display format must be updated for MySQL 4.1 and up.)

    In many respects, you can treat the DATETIME and TIMESTAMP data types similarly, but watch out for these differences:

    • DATETIME has a supported range of 1000-01-01 00:00:00 to 9999-12-31 23:59:59, whereas TIMESTAMP values are valid only from the year 1970 to approximately 2037.

    • The TIMESTAMP type has special auto-initialization and auto-update properties that are discussed further in Using TIMESTAMP to Track Row Modification Times.

    • When a client inserts a TIMESTAMP value, the server converts it from the time zone associated with the client connection to UTC and stores the UTC value. When the client retrieves a TIMESTAMP value, the server performs the reverse operation to convert the UTC value back to the client connection time zone. Clients that are in different time zones from the server can configure their connection so that this conversion is appropriate for their own time zone (Setting the Client Time Zone).

Many of the examples in this chapter draw on the following tables, which contain columns representing TIME, DATE, DATETIME, and TIMESTAMP values. (The time_val table has two columns for use in time interval calculation examples.)

mysql>SELECT t1, t2 FROM time_val;
+----------+----------+
| t1       | t2       |
+----------+----------+
| 15:00:00 | 15:00:00 |
| 05:01:30 | 02:30:20 |
| 12:30:20 | 17:30:45 |
+----------+----------+
mysql> SELECT d FROM date_val;
+------------+
| d          |
+------------+
| 1864-02-28 |
| 1900-01-15 |
| 1987-03-05 |
| 1999-12-31 |
| 2000-06-04 |
+------------+
mysql> SELECT dt FROM datetime_val;
+---------------------+
| dt                  |
+---------------------+
| 1970-01-01 00:00:00 |
| 1987-03-05 12:30:15 |
| 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 |
+---------------------+
mysql> SELECT ts FROM timestamp_val;
+---------------------+
| ts                  |
+---------------------+
| 1970-01-01 00:00:00 |
| 1987-03-05 12:30:15 |
| 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 |
+---------------------+

It is a good idea to create the time_val, date_val, datetime_val, and timestamp_val tables right now before reading further. (Use the appropriate scripts in the tables directory of the recipes distribution.)

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.147.6.10