MySQL Date and Time

Last Updated: 04 July, 2023

In MySQL, we can work with dates and times using various date and time functions and data types. Here are some key aspects related to dates and times in MySQL:

Date and Time Data Types:

• DATE: It represents a date value in the format 'YYYY-MM-DD'. For example, '2023-07-04'.
• TIME: It represents a time value in the format 'HH:MM:SS'. For example, '14:30:45'.
• DATETIME: It represents a combination of date and time in the format 'YYYY-MM-DD HH:MM:SS'. For example, '2023-07-04 14:30:45'.
• TIMESTAMP: It represents a timestamp value, which is a binary value that indicates the number of seconds since the Unix epoch (1970-01-01 00:00:00 UTC). It is commonly used to store the current date and time when a row is inserted or updated. It can store dates and times in the range from '1970-01-01 00:00:01' to '2038-01-19 03:14:07'.
• YEAR: The YEAR data type is used to store a year in a two-digit or four-digit format. For a two-digit year, values from '00' to '69' are converted to the range 2000 to 2069, while values from '70' to '99' are converted to the range 1970 to 1999.

Here are some examples of creating tables with date and time columns:

  -- Creating a table with DATE column
  CREATE TABLE my_table (
      id INT PRIMARY KEY AUTO_INCREMENT,
      event_date DATE
  );
  
  -- Creating a table with TIME column
  CREATE TABLE my_table (
      id INT PRIMARY KEY AUTO_INCREMENT,
      event_time TIME
  );
  
  -- Creating a table with DATETIME column
  CREATE TABLE my_table (
      id INT PRIMARY KEY AUTO_INCREMENT,
      event_datetime DATETIME
  );
  
  -- Creating a table with TIMESTAMP column
  CREATE TABLE my_table (
      id INT PRIMARY KEY AUTO_INCREMENT,
      event_timestamp TIMESTAMP
  );
  
  -- Creating a table with YEAR column
  CREATE TABLE my_table (
      id INT PRIMARY KEY AUTO_INCREMENT,
      event_year YEAR
  );

When inserting data into these tables, we need to format your date and time values accordingly:

 INSERT INTO my_table (event_date) VALUES ('2023-07-04');
 INSERT INTO my_table (event_time) VALUES ('12:34:56'); 
 INSERT INTO my_table (event_datetime) VALUES ('2023-07-04 12:34:56');
 -- TIMESTAMP values can be inserted using the NOW() function
 INSERT INTO my_table (event_timestamp) VALUES (NOW());
 INSERT INTO my_table (event_year) VALUES (2023);

Date and Time Functions:

• CURDATE(): Returns the current date.
• CURTIME(): Returns the current time.
• NOW(): Returns the current date and time.
• DATE_FORMAT(date, format): Formats a date or time value according to the specified format.
• DATE_ADD(date, INTERVAL value unit): Adds a specific value and unit to a date or time.
• DATE_SUB(date, INTERVAL value unit): Subtracts a specific value and unit from a date or time.
• DATEDIFF(date1, date2): Calculates the difference in days between two dates.

Formatting date or time values in a query:

 SELECT DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date FROM your_table;