A summary of the temporal data types follows. For additional information about properties of the temporal types, see Section 10.3, “Date and Time Types”. Storage requirements are given in Section 10.5, “Data Type Storage Requirements”. Functions that operate on temporal values are described at Section 11.6, “Date and Time Functions”.
For the DATETIME
and
DATE
range descriptions,
“supported” means that although earlier values
might work, there is no guarantee.
A date. The supported range is
'1000-01-01'
to
'9999-12-31'
. MySQL displays
DATE
values in
'YYYY-MM-DD'
format, but allows
assignment of values to DATE
columns using either strings or numbers.
A date and time combination. The supported range is
'1000-01-01 00:00:00'
to
'9999-12-31 23:59:59'
. MySQL displays
DATETIME
values in
'YYYY-MM-DD HH:MM:SS'
format, but allows
assignment of values to
DATETIME
columns using either
strings or numbers.
A timestamp. The range is '1970-01-01
00:00:01'
UTC to '2038-01-19
03:14:07'
UTC.
TIMESTAMP
values are stored
as the number of seconds since the epoch
('1970-01-01 00:00:00'
UTC). A
TIMESTAMP
cannot represent
the value '1970-01-01 00:00:00'
because
that is equivalent to 0 seconds from the epoch and the value
0 is reserved for representing '0000-00-00
00:00:00'
, the “zero”
TIMESTAMP
value.
A TIMESTAMP
column is useful
for recording the date and time of an
INSERT
or
UPDATE
operation. By default,
the first TIMESTAMP
column in
a table is automatically set to the date and time of the
most recent operation if you do not assign it a value
yourself. You can also set any
TIMESTAMP
column to the
current date and time by assigning it a
NULL
value. Variations on automatic
initialization and update properties are described in
Section 10.3.1.2, “TIMESTAMP
Properties as of MySQL 4.1”.
In MySQL 4.1, TIMESTAMP
is
returned as a string with the format 'YYYY-MM-DD
HH:MM:SS'
. Display widths (used as described in
the following paragraphs) are no longer supported; the
display width is fixed at 19 characters. To obtain the value
as a number, you should add +0
to the
timestamp column.
In MySQL 4.0 and earlier,
TIMESTAMP
values are
displayed in YYYYMMDDHHMMSS
,
YYMMDDHHMMSS
,
YYYYMMDD
, or YYMMDD
format, depending on whether M
is
14 (or missing), 12, 8, or 6, but allows you to assign
values to TIMESTAMP
columns
using either strings or numbers. The
M
argument affects only how a
TIMESTAMP
column is
displayed, not storage. Its values always are stored using
four bytes each. From MySQL 4.0.12, the
--new
option can be used to make the server
behave as in MySQL 4.1.
Note that
TIMESTAMP(
columns where M
)M
is 8 or 14 are
reported to be numbers, whereas other
TIMESTAMP(
columns are reported to be strings. This is just to ensure
that you can reliably dump and restore the table with these
types.
M
)
The behavior of TIMESTAMP
columns changed considerably in MySQL 4.1. For complete
information on the differences with regard to this data
type in MySQL 4.1 and later versions (as opposed to MySQL
4.0 and earlier versions), be sure to see
Section 10.3.1.1, “TIMESTAMP
Properties Prior to MySQL 4.1”, and
Section 10.3.1.2, “TIMESTAMP
Properties as of MySQL 4.1”.
A time. The range is '-838:59:59'
to
'838:59:59'
. MySQL displays
TIME
values in
'HH:MM:SS'
format, but allows assignment
of values to TIME
columns
using either strings or numbers.
A year in two-digit or four-digit format. The default is
four-digit format. In four-digit format, the allowable
values are 1901
to
2155
, and 0000
. In
two-digit format, the allowable values are
70
to 69
, representing
years from 1970 to 2069. MySQL displays
YEAR
values in
YYYY
format, but allows you to assign
values to YEAR
columns using
either strings or numbers. The
YEAR
type is unavailable
prior to MySQL 3.22.
The SUM()
and
AVG()
aggregate functions do not
work with temporal values. (They convert the values to numbers,
which loses the part after the first nonnumeric character.) To
work around this problem, you can convert to numeric units,
perform the aggregate operation, and convert back to a temporal
value. Examples:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col
))) FROMtbl_name
; SELECT FROM_DAYS(SUM(TO_DAYS(date_col
))) FROMtbl_name
;
User Comments
To extract a timestamp in a human-readable format, use:
SELECT DATE_FORMAT(timestamp,'%l:%i:%s %p on %M %D, %Y') as ...
FROM ...
This returns:
9:15:37 PM on April 22nd, 2005
You can change the formatting or ordering in the single quotes as necessary. A complete list of the specifiers (like '%Y') are listed if you look up DATE_FORMAT.
To select from a date range with MySql timestamp using the unix_timestamp, then display in human readable format. This is great with php, when you use drop down date ranges.
The $starttime and $endtime are varibals that I passed in my php script. I also made the varibles into a unix_timestamp in php using the mktime()
select date_format(FieldWithMysqlTimestamp1, '%b-%d-%Y') as Field1, Field2, Field3, date_format(FieldWithMysqlTimestamp2, '%b-%d-%Y') as Field4 from TableName where unix_timestamp(FieldWithMysqlTimestamp1) between $starttime and $endtime
Add your own comment.