MySQL Insert Date Time

In today’s world, working with data is now a data-to-data activity, so therefore managing data with proper data and time is also crucial. MySQL provides functionalities to handle data and time properly in the database. Understanding how to insert data and time into MySQL database with functions provided by MySQL. Let’s understand with the help of examples.

In this article, we are going discuss how we can deal with date and time in MySQL, As it is very important to set the appropriate data types. MySQL provides data types for saving date and time values, such as DATE, TIME, DATETIME, and TIMESTAMP. Each data type has a specific purpose in MySQL.

Date-Time Data Types in MySQL

MySQL provides some data type formats to handle date and time values:

• DATE: Stores date values in the format “YYYY-MM-DD”. example – “2023-12-20”.
• TIME: Stores time values in the format “HH:MM: SS”. example – “07:23:30”.
• DATETIME: It combines data and time format “YYYY-MM-DD HH:MM:SS”. example – “2023-12-20 07:23:30”.
• TIMESTAMP: Stores the time stamp format “YYYY-MM-DD HH:MM:SS TIMESTAMP”. example – “1970-01-01 00:00:00 UTC”.

Inserting Date-Time Values in MySQL

Using INSERT Statement

To insert the data into the MySQL table you can use the “INSERT” statement with the the format.

Let’s take an example to understand each of the data and time format

1. INSERT syntax for DATE.

INSERT INTO table_name (dateColumn) VALUES ('2023-12-31');

2. INSERT syntax for TIME.

INSERT INTO table_name (timeColumn) VALUES ('14:30:00');

3. INSERT syntax for DATETIME.

INSERT INTO table_name (datetimeColumn) VALUES ('2023-12-31 14:30:00');

4. INSERT syntax for TIMESTAMP.

INSERT INTO table_name (timestampColumn) VALUES (NOW());

“NOW()” function to insert the current date and time.

The “INSERT” query statement is used to inserting values into columns. In above mention queries replace “table_name with your table name in database and also similar add the column names accordingly.

Run Query into MySQL

Let’s take an example and Run each and every query into Database step by step.

Step 1: Create a table in Database.

CREATE TABLE DateAndTime (Date DATE);

Step 2: Show table is created.

SHOW TABLES;

Output:

Output

Step 3: Insert values into table.

INSERT INTO DateAndTime VALUES ('2023-12-31');

Step 4: Now Select whole table to see the values into table.

SELECT * FROM DateAndTime;

Output:

Output

Step 5. Add another columns TIME, DATETIME, and TIMESTAMP into DateAndTime(table).

ALTER TABLE DateAndTime ADD Time TIME;
ALTER TABLE DateAndTime ADD DateTime DATETIME;
ALTER TABLE DateAndTime ADD TimeStamp TIMESTAMP;

Step 6: Insert values into database.

INSERT INTO DateAndTime (Time) VALUES ('14:30:00');
INSERT INTO DateAndTime (DateTime) VALUES ('2023-12-31 14:30:00');

Step 7: Now Select whole table to see all the rows.

SELECT * FROM DateAndTime;

Output:

output

Date and Time Functions

MySQL provides some date and time functions to manipulate values at the time of insertion:

1. “DATE_ADD()“: This function is use to adds a specified time interval to a date.
2. “DATE_SUB()”: This function is use to subtract a specified time interval from a date.
3. “DATE_FORMAT()“: This Function helps in format a date as specified.
4. “STR_TO_DATE()“: This function is to convert a string to a date.

Handling Timezone Issues

So while dealing with different timezones in global applications or systems to manage time accurately, MySQL provides a setting to add timezone at different levels.

Syntax to set the timezone:

SET time_zone = 'timezone';

Here in the above query we can add any timezone by replacing “timezone” with a timezone name example “UTC”, or “America/New_York”.

Conclusion

Managing date-time values in MySQL is important for database operations. MySQL provides datatype and functions which helps in set data and time in format. Understanding these concepts developers know how to maintain records properly formate. Effectively managing date and time values in MySQL gives several ways to set the right data types and handle timezones in proper format. Understanding about these concepts developers know how to maintain records properly.