Learning Mysql
Download 4.24 Mb. View original pdf
|
| -> ('-1:1:1'), ('1:1:1'), -> ('1:23:45'), ('123:4:5'), -> ('123:45:6'), ('-123:45:6'); Query OK, 4 rows affected (0.00 sec) 192 | Chapter 6: Working with Database Structures Records 4 Duplicates 0 Warnings 0 mysql> SELECT * FROM mytime; +------------+ | testtime | +------------+ | -01:01:01 | | 01:01:01 | | 01:23:45 | | 123:04:05 | | 123:45:06 | | -123:45:06 | +------------+ 5 rows inset sec) Note that hours are shown with two digits for values within the range –99 to +99. HHMMSS , MMSS , and SS Punctuation can be omitted, but the digit sequences must be two, four, or six digits in length. Note that the rightmost pair of digits is always interpreted as a SS (seconds) value, the second next rightmost pair (if present) as MM (minutes), and the third rightmost pair (if present) as HH (hours). The result is that a value such as is interpreted as 12 minutes and 22 seconds, not 12 hours and 22 minutes. You can also input a time by providing both a date and time in the formats described for DATETIME and TIMESTAMP, but only the time component is stored in a TIME type column. Regardless of the input type, the storage and display type is always HH:MM:SS . The zero time can be used to represent an unknown or dummy value. If an input date is invalid or out of range, the zero time is stored. The TIME type has an additional fraction component for storing fractions of seconds, but, while a time value can be input with a fractional component, it is presently truncated before storage by MySQL; we’ve therefore omitted it from our discussions. TIMESTAMP Stores and displays a date and time pair in the format YYYY-MM-DD HH:MM:SS for the range 1970-01-01 to sometime in 2037. The behavior of this type has varied over the life of MySQL (and continues to do so, and this section describes only the version implemented since MySQL 4.1. The key features of a TIMESTAMP column are twofold. First, if you assign NULL to it, it’s set to the current date and time. Second, a developer-selected TIMESTAMP column in a table can be automatically updated to the current date and time when a row is inserted or updated. You can always explicitly set a column to a value you want by assigning that value to the column, regardless of whether it’s the automatically updating column. The automatic update feature is discussed later in this section. A nonupdating near- equivalent is the DATETIME type described later in this section. Download 4.24 Mb. Share with your friends:
|