How To Insert Date In Mysql Workbench

In this tutorial, you will learn how to insert a date into a MySQL Workbench Database. Dates are essential for keeping records of events, transactions, or any other occurrence that has a specific date associated with it.

To store dates efficiently in a MySQL database, we use the DATE or TIMESTAMP data types. The DATE format is ‘YYYY-MM-DD’, while the TIMESTAMP includes both date and time.

Step 1: Create a New Table

First and foremost, we will create a new table with a DATE or TIMESTAMP field to demonstrate the process of inserting a date. In the MySQL Workbench, create a new table with the following SQL query:

1
2
3
4
5
6
CREATE TABLE IF NOT EXISTS `events` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `event_name` VARCHAR(255) NOT NULL,
  `event_date` DATE,
  `event_timestamp` TIMESTAMP
);

This query creates a new table named ‘events’ with four columns: id, event_name, event_date (of type DATE), and event_timestamp (of type TIMESTAMP).

Step 2: Insert Data with Specific Dates

To insert data with specific dates, use the following SQL query:

1
2
INSERT INTO `events` (`event_name`, `event_date`, `event_timestamp`)
VALUES ('Product Launch', '2022-10-20', '2022-10-20 10:00:00');

This query inserts a new row into the ‘events’ table with an event_name ‘Product Launch’, a specific event_date ‘2022-10-20’, and a specific event_timestamp ‘2022-10-20 10:00:00’.

Step 3: Insert Data with the Current Date

To insert data using the current date, you can use the NOW() or CURRENT_TIMESTAMP functions, depending on your desired format:

1
2
INSERT INTO `events` (`event_name`, `event_date`, `event_timestamp`)
VALUES ('Board Meeting', CURRENT_DATE, CURRENT_TIMESTAMP);

This query inserts a new row into the ‘events’ table with an event_name ‘Board Meeting’, the event_date as the current date, and event_timestamp as the current date and time.

Step 4: Update Data with a New Date

If you want to update the date in an existing record, you can use the UPDATE statement:

1
2
3
UPDATE `events`
SET `event_date` = '2022-11-05', `event_timestamp` = '2022-11-05 15:00:00'
WHERE `event_name` = 'Product Launch';

This query updates the event_date and event_timestamp fields for the record with the event_name ‘Product Launch’ to a new specified date and time.

Full Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Create table
CREATE TABLE IF NOT EXISTS `events` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `event_name` VARCHAR(255) NOT NULL,
  `event_date` DATE,
  `event_timestamp` TIMESTAMP
);
 
-- Insert data with specific dates
INSERT INTO `events` (`event_name`, `event_date`, `event_timestamp`)
VALUES ('Product Launch', '2022-10-20', '2022-10-20 10:00:00');
 
-- Insert data with current date
INSERT INTO `events` (`event_name`, `event_date`, `event_timestamp`)
VALUES ('Board Meeting', CURRENT_DATE, CURRENT_TIMESTAMP);
 
-- Update data with a new date
UPDATE `events`
SET `event_date` = '2022-11-05', `event_timestamp` = '2022-11-05 15:00:00'
WHERE `event_name` = 'Product Launch';

Conclusion

Inserting dates and timestamps in MySQL might seem confusing initially, but understanding the DATE and TIMESTAMP formats, as well as the use of specific date functions, will greatly simplify this process.

This tutorial has demonstrated how to create tables with date fields, insert records with specific dates, insert records with the current date, and update records with new dates using various SQL queries in MySQL Workbench.