35
TIMESTAMP type holds datetime values in range from 1970-01-01 00:00:01 UTC to
36
2038-01-01 00:00:00 UTC stored as number of seconds since Unix
35
TIMESTAMP type holds datetime values in range from 1970-01-01 00:00:01 UTC to
36
2038-01-01 00:00:00 UTC stored as number of seconds since Unix
39
Up to one of timestamps columns in the table can be automatically
39
Up to one of timestamps columns in the table can be automatically
40
40
set on row update and/or have NOW() as default value.
41
TABLE::timestamp_field points to Field object for such timestamp with
41
TABLE::timestamp_field points to Field object for such timestamp with
42
42
auto-set-on-update. TABLE::time_stamp holds offset in record + 1 for this
43
43
field, and is used by handler code which performs updates required.
45
45
Actually SQL-99 says that we should allow niladic functions (like NOW())
46
as defaults for any field. Current limitations (only NOW() and only
47
for one TIMESTAMP field) are because of restricted binary .frm format
46
as defaults for any field. Current limitations (only NOW() and only
47
for one TIMESTAMP field) are because of restricted binary .frm format
48
48
and should go away in the future.
50
50
Also because of this limitation of binary .frm format we use 5 different
51
51
unireg_check values with TIMESTAMP field to distinguish various cases of
52
52
DEFAULT or ON UPDATE values. These values are:
54
54
TIMESTAMP_OLD_FIELD - old timestamp, if there was not any fields with
55
auto-set-on-update (or now() as default) in this table before, then this
56
field has NOW() as default and is updated when row changes, else it is
55
auto-set-on-update (or now() as default) in this table before, then this
56
field has NOW() as default and is updated when row changes, else it is
57
57
field which has 0 as default value and is not automatically updated.
58
58
TIMESTAMP_DN_FIELD - field with NOW() as default but not set on update
59
59
automatically (TIMESTAMP DEFAULT NOW())
60
TIMESTAMP_UN_FIELD - field which is set on update automatically but has not
61
NOW() as default (but it may has 0 or some other const timestamp as
60
TIMESTAMP_UN_FIELD - field which is set on update automatically but has not
61
NOW() as default (but it may has 0 or some other const timestamp as
62
62
default) (TIMESTAMP ON UPDATE NOW()).
63
TIMESTAMP_DNUN_FIELD - field which has now() as default and is auto-set on
63
TIMESTAMP_DNUN_FIELD - field which has now() as default and is auto-set on
64
64
update. (TIMESTAMP DEFAULT NOW() ON UPDATE NOW())
65
NONE - field which is not auto-set on update with some other than NOW()
65
NONE - field which is not auto-set on update with some other than NOW()
66
66
default value (TIMESTAMP DEFAULT 0).
68
Note that TIMESTAMP_OLD_FIELDs are never created explicitly now, they are
69
left only for preserving ability to read old tables. Such fields replaced
70
with their newer analogs in CREATE TABLE and in SHOW CREATE TABLE. This is
71
because we want to prefer NONE unireg_check before TIMESTAMP_OLD_FIELD for
72
"TIMESTAMP DEFAULT 'Const'" field. (Old timestamps allowed such
73
specification too but ignored default value for first timestamp, which of
68
Note that TIMESTAMP_OLD_FIELDs are never created explicitly now, they are
69
left only for preserving ability to read old tables. Such fields replaced
70
with their newer analogs in CREATE TABLE and in SHOW CREATE TABLE. This is
71
because we want to prefer NONE unireg_check before TIMESTAMP_OLD_FIELD for
72
"TIMESTAMP DEFAULT 'Const'" field. (Old timestamps allowed such
73
specification too but ignored default value for first timestamp, which of
74
74
course is non-standard.) In most cases user won't notice any change, only
75
75
exception is different behavior of old/new timestamps during ALTER TABLE.