In MySQL, date and time can be stored using 3 types, DATE, DATETIME, and TIMESTAMP. They are related.
MySQL recognizes DATE, DATETIME, and TIMESTAMP values in several formats.
- DATE type format: ‘YYYY-MM-DD’
- DATETIME type format: ‘YYYY-MM-DD hh:mm:ss’
- TIMESTAMP type format: a timestamp integer
Basics
Here’s a brief overview of these data types and how to use them in MySQL:
- DATE: The DATE data type is used to store dates in the format YYYY-MM-DD. This data type has a range from ‘1000-01-01’ to ‘9999-12-31’.
To create a table with a DATE column, you can use the following SQL statement:
CREATE TABLE table_name (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
birth_date DATE,
PRIMARY KEY (id)
);
To insert a date value into the birth_date column, you can use the following SQL statement:
sqlCopy codeINSERT INTO table_name (name, birth_date)
VALUES ('John Doe', '1990-01-01');
- DATETIME: The DATETIME data type is used to store date and time values in the format YYYY-MM-DD HH:MM:SS. This data type has a range from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
To create a table with a DATETIME column, you can use the following SQL statement:
CREATE TABLE table_name (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
created_at DATETIME,
PRIMARY KEY (id)
);
To insert a date and time value into the created_at column, you can use the following SQL statement:
sqlCopy codeINSERT INTO table_name (name, created_at)
VALUES ('John Doe', '2022-02-26 15:30:00');
- TIMESTAMP: The TIMESTAMP data type is used to store date and time values in the format YYYY-MM-DD HH:MM:SS. This data type has a range from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. The TIMESTAMP data type also has an auto-update feature that updates the column with the current timestamp value whenever the row is inserted or updated.
To create a table with a TIMESTAMP column, you can use the following SQL statement:
CREATE TABLE table_name (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
To insert a row and automatically update the last_login column with the current timestamp value, you can use the following SQL statement:
INSERT INTO table_name (name)
VALUES ('Mary Town');
I hope this helps! Let me know if you have any further questions.
Set default value as today or now
CREATE TABLE sale (
`order_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
`last_modified` DATETIME ON UPDATE CURRENT_TIMESTAMP,
`confirm_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)