How To Set A Default Value For A MySQL Datetime Column


Short Answer

1
2
3
4
CREATE TABLE test (
    creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    modification_time DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  creation_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  modification_time DATETIME ON UPDATE CURRENT_TIMESTAMP
);

-- insert
INSERT INTO EMPLOYEE (empId, name, dept) VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE (empId, name, dept) VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE (empId, name, dept) VALUES (0003, 'Ava', 'Sales');

-- fetch 
SELECT * FROM EMPLOYEE;

Query output:

1
2
3
4
5
6
7
+-------+-------+------------+---------------------+-------------------+
| empId	| name  | dept       | creation_time       | modification_time |
+-------+-------+------------+---------------------+-------------------+
|     1 | Clark	| Sales      | 2022-09-28 06:39:17 | NULL              |
|     2	| Dave	| Accounting | 2022-09-28 06:39:17 | NULL              |
|     3	| Ava	| Sales      | 2022-09-28 06:39:17 | NULL              |
+-------+-------+------------+---------------------+-------------------+




Related Posts

How To Specify Unique Constraint For Multiple Columns In MySQL

How to make multiple columns unique (together) in an existing...

How To Select One Row Per ID With Max Value On A Column

Suppose we have the following REIMBURSEMENT table, find the max...

How To Get The Size Of A Table In MySQL

How to get the size of a table in MySQL...

How To Get The Size Of A Database In MySQL

How to get the size of a database in MySQL...

How To Concatenate Multiple Rows Into One Field In MySQL

How to concatenate multiple rows into one field in MySQL...

How To Change The Data Type Of A Column In MySQL

Given a table EMPLOYEE, change the data type of the...