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

Leave a Comment

Your email address will not be published. Required fields are marked *


Scroll to Top

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close