Oracle 5.0, Reference Manual

Page: 849 / 3232

Date and Time Type Overview
829
UNSIGNED
, if specified, disallows negative values.
•
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
,
REAL[(M,D)] [UNSIGNED]
[ZEROFILL]
These types are synonyms for
DOUBLE
. Exception: If the
REAL_AS_FLOAT
[539] SQL mode is
enabled,
REAL
is a synonym for
FLOAT
rather than
DOUBLE
.
•
FLOAT(p) [UNSIGNED] [ZEROFILL]
A floating-point number.
p
represents the precision in bits, but MySQL uses this value only to
determine whether to use
FLOAT
or
DOUBLE
for the resulting data type. If
p
is from 0 to 24, the data
type becomes
FLOAT
with no
M
or
D
values. If
p
is from 25 to 53, the data type becomes
DOUBLE
with no
M
or
D
values. The range of the resulting column is the same as for the single-precision
FLOAT
or double-precision
DOUBLE
data types described earlier in this section.
FLOAT(p)
syntax is provided for ODBC compatibility.
11.1.2. Date and Time Type Overview
A summary of the temporal data types follows. For additional information about properties and storage
requirements of the temporal types, see
Section 11.1.5, “Date and Time Types” , and Section 11.2,
“Data Type Storage Requirements” . For descriptions of functions that operate on temporal values, see
Section 12.7, “Date and Time Functions” .
For the
DATE
and
DATETIME
range descriptions, “supported” means that although earlier values might
work, there is no guarantee.
•
DATE
A date. The supported range is
'1000-01-01'
to
'9999-12-31'
. MySQL displays
DATE
values
in
'YYYY-MM-DD'
format, but permits assignment of values to
DATE
columns using either strings or
numbers.
•
DATETIME
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 permits
assignment of values to
DATETIME
columns using either strings or numbers.
•
TIMESTAMP
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.
Unless specified otherwise, the first
TIMESTAMP
column in a table is defined to be automatically set
to the date and time of the most recent modification if not explicitly assigned a value. This makes
TIMESTAMP
useful for recording the timestamp of an
INSERT
or
UPDATE
operation. You can also set
any
TIMESTAMP
column to the current date and time by assigning it a
NULL
value, unless it has been
defined with the
NULL
attribute to permit
NULL
values. The automatic initialization and updating to
the current date and time can be specified using
DEFAULT CURRENT_TIMESTAMP
and
ON UPDATE
CURRENT_TIMESTAMP
clauses, as described in Section 11.1.5.5, “Automatic Initialization and
Updating for
TIMESTAMP
” .