Understanding the DATETIME Data Type in MySQL
Managing dates and times is essential in almost any database, and MySQL is no exception. The data type DATETIME It is used to store combinations of dates and times with a precision of seconds. This capability makes it essential for logs that require the exact timestamp of when events occurred, such as activity logs, transaction logs, or event tracking in applications.
A DATETIME in MySQL you can store data ranging from the year 1000-01-01 00:00:00 to the year 9999-12-31 23:59:59, and it is stored in a format YYYY-MM-DD HH:MM:SS.
How a DATETIME is Stored and Retrieved
To insert a value into a field DATETIME, you can use a string in the format 'YYYY-MM-DD HH:MM:SS':
INSERT INTO events (name, date_time) VALUES ('Login', '2023-09-15 08:25:47');To retrieve this data, simply select from the database:
SELECT name, datetime FROM events;Useful Functions to Manipulate DATETIME
MySQL offers a variety of functions to manipulate and extract information from datetime data. Here we explore the most useful ones:
NOW() – Get the Current Date and Time
The function NOW() It is perhaps the most direct and used, since it returns the current date and time of the system in which the database is running:
SELECT NOW();CURDATE() – Get the Current Date
If you only need the date part of the current timestamp, CURDATE() comes to the rescue:
SELECT CURDATE();CURTIME() – Get the Current Time
Similarly, if you only need the time, CURTIME() is the appropriate function:
SELECT CURTIME();DATE() – Extract the Date from a DATETIME
To get only the date part of a column DATETIME, you can use the function DATE():
SELECT DATE(date_time) FROM events;TIME() – Extract Time from a DATETIME
And to get just the time part:
SELECT TIME(date_time) FROM events;DATEDIFF() – Difference Between Two Dates
The function DATEDIFF() returns the difference in days between two dates:
SELECT DATEDIFF('2023-12-25', '2023-01-01');DATE_ADD() and DATE_SUB() – Add and Subtract Dates
To manipulate dates by adding or subtracting a specific interval, you can use DATE_ADD() y DATE_SUB():
SELECT DATE_ADD('2023-01-01', INTERVAL 1 MONTH); SELECT DATE_SUB('2023-01-01', INTERVAL 15 DAY);TIMESTAMPDIFF() – Difference in Specific Units
If you need the difference in units other than days, TIMESTAMPDIFF() It is extremely useful. You can specify the unit, which can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER or YEAR:
SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2024-01-01');Formatting Functions: DATE_FORMAT() and TIME_FORMAT()
To present dates and times in readable or custom formats, DATE_FORMAT() y TIME_FORMAT() allow you to specify how you want the output to be displayed:
SELECT DATE_FORMAT(NOW(), '%W, %M %d %Y'); SELECT TIME_FORMAT(NOW(), '%H:%i:%s %p');Practical Use Cases of DATETIME
Event Registration
Let's say you are building a system where you need to record events and the exact time they occur. Wear DATETIME It allows you to know not only the day of the event, but also the exact time, something crucial for logs or audit trails.
Agendas and Calendars
In appointment management applications, fields DATETIME They allow you to schedule events and reminders on specific dates and times, facilitating personal or business organization and planning.
Temporary Reports
Generating reports that depend on performance analysis between periods requires detailed time management, where the difference and format functions of DATETIME They greatly facilitate the task.
Conclusion
Master the fields DATETIME and its associated functions in MySQL not only increases the precision with which dates and times are handled in applications, but also opens up a wide range of possibilities for the processing and analysis of temporal data. From event planning to detailed reporting, the correct use of these elements can significantly improve the quality and efficiency of databases.
To learn more about effective database development and manipulation practices, I invite you to visit my blog at NelkoDev. If you have questions or need specific help, feel free to contact me at NelkoDev Contact.
English 
Spanish