MySQL DATE, DATETIME, and TIMESTAMP Tutorials

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:

1. 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');

2. 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');

3. 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
)