You can specify time without delimiters, as in 083220 or 83220. However, be careful before getting too
minimalist: MySQL reads times from the right, expecting seconds to be declared but not necessarily
hours! Therefore 8:32 or 0832 will be understood as 00:08:32 (eight minutes and 32 seconds past
midnight) rather than 8:32 a.m.
Caution Valid time values outside the allowable time range will be clipped at the -
838:59:59 and 838:59:59 ceilings, but not set to 00:00:00. However, invalid
times, such as with minutes or seconds greater than 59, will be set to
00:00:00. This means that if you see a time of 00:00:00, which is itself a valid
time, you have no way of knowing if the data is correct or if there has been an
attempt to store an illegal time.YEAR
YEAR records the year of an event and occupies just 1 byte of data.
YEAR values can range from 1901 to 2155. You can specify a year in either string or numeric notation.
YEAR values can be specified in either two- or four-digit form, but two-digit years are converted to four
digits. Thus, years can be given by numbers (in the range 1 to 99) or by strings (in the range '0' or '00' to
'99').
Two-digit years specified in the range 1 to 69 (or '0' to '69') will be taken as meaning years from 2001
through 2069 (or 2000 through 2069). Years in the range 70 to 99 will be interpreted as being in the
range 1970 through 1999.TIMESTAMP
TIMESTAMP is a useful field format whereby that column will be set to the current date and time whenever
that row is updated or inserted in the table. It conveniently gives you a "last updated" stamp without having to
set it each time you change some data in that row.
TIMESTAMP can only handle dates in the range 1970 through 2037, with the data being held with the
maximum resolution of one second.
Although a TIMESTAMP field is always held in the same way internally, it can have a number of external
formats. These can be made up of any even number of digits from 2 to 14. Table 16.2 shows a list of
the formats that TIMESTAMP offers.
Table 16.2 TIMESTAMP Display Formats
Data Type Display formatTIMESTAMP(14) (^) YYYYMMDDHHMMSS
TIMESTAMP(12)
YYMMDDHHMMSS
TIMESTAMP(10)
YYMMDDHHMM
TIMESTAMP(8) (^) YYYYMMDD
TIMESTAMP(6) (^) YYMMDD
TIMESTAMP(4)
YYMM
TIMESTAMP(2)
YY
Look briefly at what this table means. If an update is made at 9:30 on 12 May 2000, a TIMESTAMP(14)
would be set to 20000512093000 , while a TIMESTAMP(8) would be set to 20000512 because it
contains no time information.
Note TIMESTAMP fields are set to the current time if you do not write anything to them.
In other words, you must either not specify the column name in an INSERT or
UPDATE statement, or you must write NULL to it. Either of these will set the
TIMESTAMP field to the current time. The same will be achieved if you write
NOW() to the field, a function for retrieving the current time and date that you will
look at later.
You can write new data to a TIMESTAMP field just by specifying the new data explicitly. You can set it to
whatever date and time you want within its legal range. (You might consider this is faking its true
purpose!) Illegal values will be discarded and replaced with the "zero" value for this field type.
As in DATETIME, DATE, and TIME data types, MySQL is flexible in the formats it will accept for the
TIMESTAMP. For example, it will accept 2000-05-12, 2000+05+12, and so on, allowing you to use
whatever separator you want, or none at all, as in 20000512.
Note When setting a TIMESTAMP field, values specified as numbers, rather than
strings, should be 14, 12, 8, or 6 digits long.