5.5. Temporal Data Types


MySQL provides data types for storing different kinds of temporal information. In the following descriptions, the terms YYYY, MM, DD, hh, mm, and ss stand for a year, month, day of month, hour, minute, and second value, respectively.

The following table summarizes the storage requirements and ranges for the date and time data types.

Type

Storage Required

Range

DATE

3 bytes

'1000-01-01' to '9999-12-31'

TIME

3 bytes

'-838:59:59' to '838:59:59'

DATETIME

8 bytes

'1000-01-01 00:00:00' to '9999-12-31 23:59:59'

TIMESTAMP

4 bytes

'1970-01-01 00:00:00' to mid-year 2037

YEAR

1 byte

1901 to 2155 (for YEAR(4)), 1970 to 2069 (for YEAR(2))


Each temporal data type also has a "zero" value that's used when you attempt to store an illegal value. The "zero" value is represented in a format appropriate for the type (such as '0000-00-00' for DATE values and '00:00:00' for TIME) values.

MySQL represents date values in 'YYYY-MM-DD' format when it displays them. This representation corresponds to the ANSI SQL date format, also known as ISO 8601 format. If necessary, you can reformat date values into other display formats using the DATE_FORMAT() function.

For date entry, MySQL also expects to receive dates in ISO format, or at least close to ISO format. That is, date values must be given in year-month-day order, although some deviation from strict ISO format is allowed:

  • Leading zeros on month and day values may be omitted. For example, both '2000-1-1' and '2000-01-01' are accepted as legal.

  • The delimiter between date parts need not be '-'; you can use other punctuation characters, such as '/'.

  • Two-digit years are converted to four-digit years. You should be aware that this conversion is done based on the rule that year values from 70 to 99 represent the years 1970 to 1999, whereas values from 00 to 69 represent the years 2000 to 2069. It's better to provide values with four-digit years to avoid problems with conversion of values for which the rule does not apply.

If you need to load values that aren't in an acceptable format into a DATE column, you should convert them into ISO format before loading them. An alternative approach that's useful in some circumstances is to load the values into a string column and perform reformatting operations using SQL string functions to produce ISO format values that can be assigned to a DATE column.

MySQL represents time values in 'hh:mm:ss' format. For TIME value entry, some variation on this format is allowed. For example, leading zeros on TIME parts may be omitted.

MySQL represents time values in 'hh:mm:ss' format when displaying them. If necessary, you can reformat time values into other display formats using the TIME_FORMAT() function.

For time value entry, some variation on this format is allowed. For example, leading zeros on TIME parts may be omitted.

5.5.1. The DATE, TIME, DATETIME, and YEAR Data Types

The DATE data type represents date values in 'YYYY-MM-DD' format. The supported range of DATE values is '1000-01-01' to '9999-12-31'. You might be able to use earlier dates than that, but it's better to stay within the supported range to avoid unexpected behavior.

The TIME data type represents time values in 'hh:mm:ss' format. The range of TIME columns is '-838:59:59' to '838:59:59'. This is outside the time-of-day range of '00:00:00' to '23:59:59' because TIME columns can be used to represent elapsed time. Thus, values might be larger than time-of-day values, or even negative.

The DATETIME data type stores date-and-time values in 'YYYY-MM-DD hh:mm:ss' format. It's similar to a combination of DATE and TIME values, but the TIME part represents time of day rather than elapsed time and has a range limited to '00:00:00' to '23:59:59'. The date part of DATETIME columns has the same range as DATE columns; combined with the TIME part, this results in a DATETIME range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The YEAR data type represents year-only values. You can declare such columns as YEAR(4) or YEAR(2) to obtain a four-digit or two-digit display format. If you don't specify any display width, the default is four digits.

If you don't need a full date and the range of values you need to store falls into the YEAR range, consider using YEAR to store temporal values. It's a very space-efficient data type because values require only one byte of storage each.

5.5.2. The TIMESTAMP Data Type

The TIMESTAMP type, like DATETIME, stores date-and-time values, but has a different range and some special properties that make it especially suitable for tracking data modification times.

MySQL displays TIMESTAMP values using the same format as DATETIME values; that is, 'YYYY-MM-DD hh:mm:ss'.

The range of TIMESTAMP values begins at 1970-01-01 00:00:00 (UTC) and extends partway into the year 2037. TIMESTAMP values actually represent the number of seconds elapsed since the beginning of 1970 and are stored using four bytes. This provides room for sufficient seconds to represent a date in the year 2037. MySQL Server stores TIMESTAMP values internally in UTC. It converts TIMESTAMP values from the server's current time zone for storage, and converts back to the current time zone for retrieval. It is possible for individual clients to use connection-specific time zone settings, as described in Section 5.5.3, "Per-Connection Time Zone Support."

The TIMESTAMP data type in MySQL is special in that you can cause a TIMESTAMP column to be initialized or updated automatically to the current date and time without explicitly assigning it a value. That is, you can specify that any single TIMESTAMP column in a table should be initialized with the current timestamp when the record is created with INSERT or REPLACE, updated with the current timestamp when the record is changed with UPDATE, or both. (Setting a column to its current value doesn't count as updating it.)

It's important to know about the automatic initialization and update properties of TIMESTAMP. These properties make TIMESTAMP columns useful for tracking record modification times, but can be a source of confusion if you're not aware of them. Do not choose TIMESTAMP for a column on the basis of the fact that it stores date-and-time values unless you also understand the circumstances under which the column will update automatically when other columns in a record change.

To control the initialization and update behavior of a TIMESTAMP column, you add either or both of the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes to the column definition when creating the table with CREATE TABLE or changing it with ALTER TABLE.

The DEFAULT CURRENT_TIMESTAMP attribute causes the column to be initialized with the current timestamp at the time the record is created. The ON UPDATE CURRENT_TIMESTAMP attribute causes the column to be updated with the current timestamp when the value of another column in the record is changed from its current value.

For backward compatibility with older versions of MySQL (before 4.1), if you do not specify either of the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes when creating a table, the MySQL server automatically assigns both attributes to the first TIMESTAMP column:

 mysql> CREATE TABLE ts_test1 (     ->   ts1 TIMESTAMP,     ->   ts2 TIMESTAMP,     ->   data CHAR(30)     -> ); Query OK, 0 rows affected (0.00 sec) mysql> DESCRIBE ts_test1; +-------+-----------+------+-----+---------------------+-------+ | Field | Type      | Null | Key | Default             | Extra | +-------+-----------+------+-----+---------------------+-------+ | ts1   | timestamp | YES  |     | CURRENT_TIMESTAMP   |       | | ts2   | timestamp | YES  |     | 0000-00-00 00:00:00 |       | | data  | char(30)  | YES  |     | NULL                |       | +-------+-----------+------+-----+---------------------+-------+ 3 rows in set (0.01 sec) mysql> INSERT INTO ts_test1 (data) VALUES ('original_value'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ts_test1; +---------------------+---------------------+----------------+ | ts1                 | ts2                 | data           | +---------------------+---------------------+----------------+ | 2005-01-04 14:45:51 | 0000-00-00 00:00:00 | original_value | +---------------------+---------------------+----------------+ 1 row in set (0.00 sec) mysql> . . . time passes . . . mysql> UPDATE ts_test1 SET data='updated_value'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM ts_test1; +---------------------+---------------------+---------------+ | ts1                 | ts2                 | data          | +---------------------+---------------------+---------------+ | 2005-01-04 14:46:17 | 0000-00-00 00:00:00 | updated_value | +---------------------+---------------------+---------------+ 1 row in set (0.00 sec) 

The same behavior occurs if you specify both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP explicitly for the first TIMESTAMP column. It is also possible to use just one of the attributes. The following example uses DEFAULT CURRENT_TIMESTAMP, but omits ON UPDATE CURRENT_TIMESTAMP. The result is that the column is initialized automatically, but not updated when the record is updated:

 mysql> CREATE TABLE ts_test2 (     ->   created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     ->   data CHAR(30)     -> ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO ts_test2 (data) VALUES ('original_value'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM ts_test2; +---------------------+----------------+ | created_time        | data           | +---------------------+----------------+ | 2005-01-04 14:46:39 | original_value | +---------------------+----------------+ 1 row in set (0.00 sec) mysql> . . . time passes . . . mysql> UPDATE ts_test2 SET data='updated_value'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM ts_test2; +---------------------+---------------+ | created_time        | data          | +---------------------+---------------+ | 2005-01-04 14:46:39 | updated_value | +---------------------+---------------+ 1 row in set (0.00 sec) 

Note that even though the record is updated, the created_time column is not. In versions of MySQL Server before 4.1, the UPDATE statement would have caused the created_time column to be updated as well.

The next example demonstrates how to create a TIMESTAMP column that is not set to the current timestamp when the record is created, but only when it is updated. In this case, the column definition includes ON UPDATE CURRENT_TIMESTAMP but omits DEFAULT CURRENT_TIMESTAMP:

 mysql> CREATE TABLE ts_test3 (     ->   updated_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     ->   data CHAR(30)     -> ); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO ts_test3 (data) VALUES ('original_value'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ts_test3; +---------------------+----------------+ | updated_time        | data           | +---------------------+----------------+ | 0000-00-00 00:00:00 | original_value | +---------------------+----------------+ 1 row in set (0.00 sec) mysql> UPDATE ts_test3 SET data='updated_value'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM ts_test3; +---------------------+---------------+ | updated_time        | data          | +---------------------+---------------+ | 2005-01-04 14:47:10 | updated_value | +---------------------+---------------+ 1 row in set (0.00 sec) 

Note that you can choose to use CURRENT_TIMESTAMP with neither, either, or both of the attributes for a single TIMESTAMP column, but you cannot use DEFAULT CURRENT_TIMESTAMP with one column and ON UPDATE CURRENT_TIMESTAMP with another:

 mysql> CREATE TABLE ts_test4 (     ->   created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     ->   updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     ->   data CHAR(30)     -> ); ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause 

Nevertheless, you can achieve the effect of having one column with the creation time and another with the time of the last update. To do this, create two TIMESTAMP columns. Define the column that should hold the creation time with DEFAULT 0 and explicitly set it to NULL whenever you INSERT a new record. Define the column that should hold the updated time with DEFAULT CURRENT_TIMESTAMP:

 mysql> CREATE TABLE ts_test5 (     ->   created TIMESTAMP DEFAULT 0,     ->   updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     ->   data CHAR(30)     -> ); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO ts_test5 (created, data)     -> VALUES (NULL, 'original_value'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ts_test5; +---------------------+---------------------+----------------+ | created             | updated             | data           | +---------------------+---------------------+----------------+ | 2005-01-04 14:47:39 | 0000-00-00 00:00:00 | original_value | +---------------------+---------------------+----------------+ 1 row in set (0.00 sec) mysql> . . . time passes . . . mysql> UPDATE ts_test5 SET data='updated_value'; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM ts_test5; +---------------------+---------------------+---------------+ | created             | updated             | data          | +---------------------+---------------------+---------------+ | 2005-01-04 14:47:39 | 2005-01-04 14:47:52 | updated_value | +---------------------+---------------------+---------------+ 1 row in set (0.00 sec) 

By default, MySQL defines TIMESTAMP columns as NOT NULL and stores the current timestamp in the column if you assign it a value of NULL. If you want to be able to store NULL in a TIMESTAMP column, you must explicitly write the column definition to allow NULL when creating or altering the column:

 mysql> CREATE TABLE ts_null (ts TIMESTAMP NULL); Query OK, 0 rows affected (0.04 sec) mysql> DESCRIBE ts_null; +-------+-----------+------+-----+---------+-------+ | Field | Type      | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | ts    | timestamp | YES  |     | NULL    |       | +-------+-----------+------+-----+---------+-------+ 1 row in set (0.10 sec) 

Note that specifying NULL for a TIMESTAMP column implicitly changes its default value from CURRENT_TIMESTAMP to NULL if no explicit default value is given.

5.5.3. Per-Connection Time Zone Support

In MySQL Server, it is possible to set the current time zone on a per-connection basis.

To discuss time zones, we must first introduce a number of concepts:

  • UTC is "Coordinated Universal Time" and is the common reference point for time measurement. For purposes of this discussion, UTC is the same as Greenwich Mean Time (GMT), although time zone aficionados get into long discussions about astronomical observations, atomic clocks, "Universal Time" versus "Greenwich Mean Time" versus "Coordinated Universal Time," and much else.

  • There are three time zone formats available to use with MySQL:

    • The signed hour/minute offset of a time zone is expressed as '+hh:mm' or '-hh:mm', where hh and mm stand for two-digit hours and minutes, respectively. UTC is, in this format, commonly expressed as '+00:00'. Each time zone bases its offset according to the distance between it and the UTC time zone. Berlin, Germany, is one hour ahead of Greenwich, England (for example, the sun rises in Berlin approximately one hour before it does in Greenwich), so the hour/minute offset for Berlin is expressed as '+01:00'. In New York, where the sun rises some five hours after it does in Greenwich, the hour/minute offset is expressed as '-05:00'.

    • The named time zone for a given location is defined by a string such as 'US/Eastern', which is translated into the correct time zone by the server. MySQL supports named time zones through a set of time zone tables in the mysql database. (For named time zones to work, these tables must be properly populated by the MySQL administrator. See Section 24.6, "Loading Time Zone Tables.")

    • The third format is the SYSTEM time zone. This stands for the time zone value that the MySQL server retrieves from the server host. The server uses this value as its default time zone setting when it begins executing.

The exact details of support for named time zones differ slightly from one operating system to the next, and are not covered in any detail on the Developer certification exam. However, knowing how to use time zone support using signed offsets is mandatory.

Time zone settings are determined by the time_zone system variable. The server maintains a global time_zone value, as well as a session time_zone value for each client that connects. The session value is initialized for a given client, from the current value of the global time_zone variable, when the client connects.

The default setting for the global value is SYSTEM, which thus also becomes each client's initial session time_zone value. The global and session time zone settings can be retrieved with the following statement:

 mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM             | SYSTEM              | +--------------------+---------------------+ 1 row in set (0.00 sec) 

MySQL Server stores TIMESTAMP values internally in UTC. It converts TIMESTAMP values from the server's current time zone for storage, and converts back to the current time zone for retrieval. The standard setting for both the server and the per-client connection is to use the SYSTEM setting, which the server retrieves from the host at startup.

If the time zone setting is the same for both storage and retrieval, you will get back the same value you store. If you store a TIMESTAMP value, and then change the time zone to a different value, the returned TIMESTAMP value will be different from the one you stored.

The following examples demonstrate how to change the session time zone settings to store and retrieve TIMESTAMP data. First, we set the session time zone to UTC, that is, '+00:00':

 mysql> SET time_zone = '+00:00'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@session.time_zone; +---------------------+ | @@session.time_zone | +---------------------+ | +00:00              | +---------------------+ 1 row in set (0.00 sec) 

Next, we create a simple table containing just a TIMESTAMP column named ts and insert one record that assigns the current time to ts. Then we retrieve the record:

 mysql> CREATE TABLE ts_test (ts TIMESTAMP); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO ts_test (ts) VALUES (NULL); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM ts_test; +---------------------+ | ts                  | +---------------------+ | 2005-01-04 20:50:18 | +---------------------+ 1 row in set (0.00 sec) 

Finally, we change the session time zone twice, each time retrieving the value after the change. This demonstrates that, even though we're retrieving the same TIMESTAMP value, the change in time zone setting causes the "localized" display value to be different each time:

 mysql> SET time_zone = '+02:00'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM ts_test; +---------------------+ | ts                  | +---------------------+ | 2005-01-04 22:50:18 | +---------------------+ 1 row in set (0.00 sec) mysql> SET time_zone = '-05:00'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM ts_test; +---------------------+ | ts                  | +---------------------+ | 2005-01-04 15:50:18 | +---------------------+ 1 row in set (0.00 sec) 

The per-connection time zone settings also influence other aspects of the MySQL server that depend on the current time, most notably the function NOW().

MySQL Server also supports the CONVERT_TZ() function, which performs time zone conversions of datetime values:

 mysql> SELECT CONVERT_TZ('2005-01-27 13:30:00', '+01:00', '+03:00'); +-------------------------------------------------------+ | CONVERT_TZ('2005-01-27 13:30:00', '+01:00', '+03:00') | +-------------------------------------------------------+ | 2005-01-27 15:30:00                                   | +-------------------------------------------------------+ 1 row in set (0.00 sec) 

CONVERT_TZ() assumes that the given datetime value has the time zone represented by the first hour/minute offset argument, and converts it to a value in the time zone represented by the second offset argument. The result is that you get the same datetime value, from the point of view of a different time zone.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net