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