MySQL column 'not null default current_timestamp'

Question

In a MySQL table, we can't have 2 columns type timestamp with default values is current_timestamp.

SO is there anyway to have 2 or more columns in a same table with default value is current time. This can be done easily in Postgres, with now().

Many thanks.

a table have created, modified columns. Just in case, I want them all have default values are current time when they are modified. — novo, Oct 04 '13 at 00:37

b.b3rn4rd · Accepted Answer · 2013-10-04T23:27:08.383

if timestamp column is defined as NOT NULL MySQL stores current timestamp in the column if you assign it a value of NULL

CREATE TABLE `t1` (
  `name` varchar(100) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT 0,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO `t1` SET name='abc', created=null;
mysql> select * from t1;
+------+---------------------+---------------------+
| name | created             | updated             |
+------+---------------------+---------------------+
| abc  | 2013-10-04 10:48:03 | 2013-10-04 10:48:03 |
+------+---------------------+---------------------+

UPDATE `t1` SET name='abc2' WHERE name = 'abc';
mysql> select * from t1;
+------+---------------------+---------------------+
| name | created             | updated             |
+------+---------------------+---------------------+
| abc2 | 2013-10-04 10:48:03 | 2013-10-04 11:42:04 |
+------+---------------------+---------------------+

WARNING: I couldn't believe `DEFAULT 0` meant "now", and on my installation it doesn't. I get created="0000-00-00 00:00:00". Presumably a config-setting changes that. — Richard Huxton, Oct 04 '13 at 10:18
CORRECTION: it relies on inserting `null` for `created`. If you don't specify it then you get a 0 date. The `NOT NULL` seems to be ignored no matter what you do. Bizarre. — Richard Huxton, Oct 04 '13 at 10:39

asantaballa · Answer 2 · 2013-10-04T00:45:47.027

0

Not sure if this would work, but maybe make FieldA default current_timestamp and have FieldB default be FieldA (or FieldA + 0 days if need formula).

Create Table Tbl1 
(
    ...
    , TS1 TimeStamp Default Current_TimeStamp
    , TS2 TimeStamp Default TS1
    ...
);

edited Oct 04 '13 at 00:45

answered Oct 04 '13 at 00:29

asantaballa

3,919
1
21
22

I'm not sure I get what you mean. Can you give an example codes. Thanks – novo Oct 04 '13 at 00:38
Sorry that don't have MySql, but was thinking along these lines (see edit) – asantaballa Oct 04 '13 at 00:46

score 0 · Answer 3 · edited May 23 '17 at 10:28

0

It seems that this page is referring to the same issue and it's a well-known bug in MySQL have a look to see if it helps MySQL two column timestamp default NOW value ERROR 1067

edited May 23 '17 at 10:28

Community

1
1

answered Oct 04 '13 at 00:38

Siavosh

2,314
4
26
50

MySQL column 'not null default current_timestamp'

3 Answers3