Adding a new column with the current time as a default value

Question

I am looking for the syntax to add a column to a MySQL table with the current time as a default value.

Read the [mysql doumatation](http://dev.mysql.com/doc/refman/5.6/en/alter-table.html) about alter table syntax — Jens, Jul 31 '15 at 10:10

score 13 · Answer 1 · edited Aug 20 '20 at 14:02

13

Even so many persons have provided solution but this is just to add more information-

If you just want to insert current timestamp at the time of row insertion-

    ALTER TABLE mytable ADD mytimestampcol TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

If you also want that this column should update if any update this row then use this-

    ALTER TABLE mytable ADD mytimestampcol TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

edited Aug 20 '20 at 14:02

Akash Kumar Verma

3,185
2
16
32

answered Jul 31 '15 at 10:15

Zafar Malik

6,734
2
19
30

Zafar Malik, I m looking for only the time format "HH:MM:SS" – RedPe4rl Jul 31 '15 at 10:17
Even there is time datatype in mysql but it is not with auto fill property, buty yes you can keep column property timestamp for auto fill and then select only time from that. – Zafar Malik Jul 31 '15 at 10:28
How can I select just time ?, the sql syntax please. – RedPe4rl Jul 31 '15 at 10:33
select time(timestampcol) from mytable; – Zafar Malik Jul 31 '15 at 10:50

score 8 · Accepted Answer · edited May 23 '17 at 12:31

8

IMPORTANT EDIT: It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...

It is now possible to achieve this with DATETIME fields since MySQL 5.6.5
But you can do it with TIMESTAMP:

 create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP)

edited May 23 '17 at 12:31

Community

1
1

answered Jul 31 '15 at 10:10

Roman Marusyk

23,328
24
73
116

I am looking for the syntax to add just the time (HH:MM:SS) – RedPe4rl Jul 31 '15 at 10:13
What is type of your column? – Roman Marusyk Jul 31 '15 at 10:20
I can change it, it is not a problem, I can fit it to the type of the return value function – RedPe4rl Jul 31 '15 at 10:25
The function CURTIME() doesn't work, it indicat a syntax error – RedPe4rl Jul 31 '15 at 10:28
"MySQL retrieves and displays `DATETIME` values in `YYYY-MM-DD HH:MM:SS` format." This is from mysql site. You can store only this type, but you can use one of the many time format functions to change it, when you need to display it. For excample: `SELECT TIME_FORMAT(ts, '%T') FROM test` – Roman Marusyk Jul 31 '15 at 10:30

score 4 · Answer 3 · answered Jul 31 '15 at 10:11

4

You can specify a default clause:

ALTER TABLE mytable ADD COLUMN (mytimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

answered Jul 31 '15 at 10:11

Mureinik

297,002
52
306
350

score 0 · Answer 4 · answered Jul 31 '15 at 10:11

I think these sql will useful to you.

create table test (column_name varchar(32), time_column TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

mysql> insert into test (column_name) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select column_name as name, time_column as ts from  test;
+------+---------------------+
| name | ts                  |
+------+---------------------+
| demo | 2014-07-31 15:42:52 | 
+------+---------------------+

1 row in set (0.00 sec)

Thank you.

score 0 · Answer 5 · answered Jul 31 '15 at 12:28

0

create table test (col1 varchar(10), lastaccessdate TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6));

this will update time with fractional seconds in time, and also update when record is last inserted or updated.

answered Jul 31 '15 at 12:28

Manish Sapkal

5,591
8
45
74

Adding a new column with the current time as a default value

5 Answers5