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
– novo, Commented Oct 4, 2013 at 0:37

b.b3rn4rd · Accepted Answer · 2013-10-04 23:27:08Z

2

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 |
+------+---------------------+---------------------+

edited Oct 4, 2013 at 23:27

answered Oct 4, 2013 at 0:46

b.b3rn4rd

8,8602 gold badges50 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Richard Huxton Over a year ago

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 Over a year ago

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.

Community · Accepted Answer · 2017-05-23 10:28:50Z

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, 2017 at 10:28

CommunityBot

11 silver badge

answered Oct 4, 2013 at 0:38

Siavosh

2,3645 gold badges27 silver badges52 bronze badges

Comments

asantaballa · Accepted Answer · 2013-10-04 00:45:47Z

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 4, 2013 at 0:45

answered Oct 4, 2013 at 0:29

asantaballa

4,0581 gold badge24 silver badges23 bronze badges

2 Comments

novo Over a year ago

I'm not sure I get what you mean. Can you give an example codes. Thanks

asantaballa Over a year ago

Sorry that don't have MySql, but was thinking along these lines (see edit)

Collectives™ on Stack Overflow

MySQL column 'not null default current_timestamp'

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related