Oracle 5.0, Reference Manual

Page: 866 / 3232

Date and Time Types
846
The following table contains several
TIMESTAMP
columns that permit
NULL
values:
CREATE TABLE t
(
ts1 TIMESTAMP NULL DEFAULT NULL,
ts2 TIMESTAMP NULL DEFAULT 0,
ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
A
TIMESTAMP
column that permits
NULL
values does not take on the current timestamp at insert time
except under one of the following conditions:
• Its default value is defined as
CURRENT_TIMESTAMP
[918] and no value is specified for the column
•
CURRENT_TIMESTAMP
[918] or any of its synonyms such as
NOW()
[926] is explicitly inserted
into the column
In other words, a
TIMESTAMP
column defined to permit
NULL
values auto-initializes only if its definition
includes
DEFAULT CURRENT_TIMESTAMP
:
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
If the
TIMESTAMP
column permits
NULL
values but its definition does not include
DEFAULT
CURRENT_TIMESTAMP
, you must explicitly insert a value corresponding to the current date and time.
Suppose that tables
t1
and
t2
have these definitions:
CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT NULL);
To set the
TIMESTAMP
column in either table to the current timestamp at insert time, explicitly assign it
that value. For example:
INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
11.1.5.6. Fractional Seconds in Time Values
A trailing fractional seconds part is permissible for temporal values in contexts such as literal values,
and in the arguments to or return values from some temporal functions. Example:
mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473');
+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
| 19473 |
+-------------------------------------------+
However, when MySQL stores a value into a column of any temporal data type, it discards any
fractional part and does not store it.
11.1.5.7. Conversion Between Date and Time Types
To some extent, you can convert a value from one temporal type to another. However, there may be
some alteration of the value or loss of information. In all cases, conversion between temporal types
is subject to the range of legal values for the resulting type. For example, although
DATE
,
DATETIME
,
and
TIMESTAMP
values all can be specified using the same set of formats, the types do not all
have the same range of values.
TIMESTAMP
values cannot be earlier than
1970
UTC or later than
'2038-01-19 03:14:07'
UTC. This means that a date such as
'1968-01-01'
, while legal as a
DATE
or
DATETIME
value, is not valid as a
TIMESTAMP
value and is converted to
0
.
Conversion of
DATE
values:
• Conversion to a
DATETIME
or
TIMESTAMP
value adds a time part of
'00:00:00'
because the
DATE
value contains no time information.
• Conversion to a
TIME
value is not useful; the result is
'00:00:00'
.