<p></p> <p>The use of date and time functions in MySQL requires attention to details. 1. Get the current time to obtain NOW(), CURRENT_TIMESTAMP (including date and time), CURDATE() (date only), and CURTIME() (time only); 2. Use DATE_FORMAT() to format the output, and support custom formats such as %Y year %m month %d day; 3. The calculation interval can be implemented through DATE_ADD(), DATE_SUB() or /- operators, such as adding one hour or reducing half an hour; 4. You can use and other operators for time comparison. Note that the field type should be DATE or DATETIME to avoid string comparison errors. Mastering these functions helps to efficiently process datetime-related queries. </p> <p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175156833230057.jpg" class="lazy" alt="Working with date and time functions in MySQL"></p> <p> It is actually not too difficult to deal with date and time functions in MySQL, but to use them correctly and well, you still have to pay attention to some details. MySQL provides many built-in functions to operate dates and times, such as obtaining the current time, formatting output, calculating intervals, etc. Let’s take a look at some of the most commonly used and useful functions. </p> <img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175156833476324.jpeg" class="lazy" alt="Working with date and time functions in MySQL"><hr> <h3 id="Get-the-current-time-and-date"> Get the current time and date</h3> <p> Sometimes you need to record the user access time or the time point when the system event occurs, and you need to obtain the current time. There are several ways to get this value in MySQL: </p> <img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175156833544308.jpeg" class="lazy" alt="Working with date and time functions in MySQL"><ul> <li> <code>NOW()</code> and <code>CURRENT_TIMESTAMP</code> : These two return the full date and time of the moment when the query is executed, and the format is <code>YYYY-MM-DD HH:MM:SS</code> .</li> <li> <code>CURDATE()</code> : Returns only the current date part.</li> <li> <code>CURTIME()</code> : Returns only the current time part.</li> </ul> <p> If you just want to insert a time-sensitive data, such as logging, just write <code>NOW()</code> in the SQL statement, and there is no need to pass additional parameters.</p> <hr> <h3 id="Format-date-time-output"> Format date time output</h3> <p> Although the default date format is standard, we often want to show it in another way, such as "April 5, 2025" or "Saturday". At this time, you can use the <code>DATE_FORMAT()</code> function. </p> <img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/175156833726147.jpeg" class="lazy" alt="Working with date and time functions in MySQL"><p> For example:</p><pre class='brush:php;toolbar:false;'> SELECT DATE_FORMAT(NOW(), &#39;%Y year %m month %d day&#39;) AS formatted_date;</pre><p> The above sentence will output similar to "April 5, 2025".</p><p> Common formats include:</p><ul><li> <code>%Y</code> four-digit year</li><li> <code>%y</code> double digit year</li><li> <code>%m</code> month (01~12)</li><li> <code>%d</code> date (01~31)</li><li> <code>%H</code> hours (24-hour system)</li><li> <code>%i</code> minutes</li><li> <code>%s</code> seconds</li></ul><p> You can combine these format symbols according to your needs and customize the display method that meets business needs.</p><hr /><h3 id="Calculate-date-and-time-interval"> Calculate date and time interval</h3><p> Another common scenario is to calculate the difference between two times, or add a few days, hours, etc. to a certain time. At this time, you can use <code>DATE_ADD()</code> , <code>DATE_SUB()</code> or a more concise operator.</p><p> For example, if you want to find an order that expires in three days:</p><pre class='brush:php;toolbar:false;'> SELECT * FROM orders WHERE due_date = DATE_ADD(CURDATE(), INTERVAL 3 DAY);</pre><p> Can also be used <code>-</code> or<code> </code>Direct operation:</p><pre class='brush:php;toolbar:false;'> SELECT NOW() INTERVAL 1 HOUR; -- add one hour SELECT order_time - INTERVAL 30 MINUTE FROM orders; -- reduce half an hour</pre><p> One thing to note is that the result type returned by these operations is still <code>DATETIME</code> or <code>DATE</code> . If you want to make further comparisons or formatting, remember to use it with other functions.</p><hr /><h3 id="Comparison-of-dates-and-times"> Comparison of dates and times</h3><p> When doing conditional screening, it is often necessary to determine whether a certain time is earlier than or later than another time. MySQL supports direct comparison using <code><</code> , <code>></code> and other operators.</p><p> For example, find all users created before today:</p><pre class='brush:php;toolbar:false;'> SELECT * FROM users WHERE created_at < CURDATE();</pre><p> However, it should be noted that <code>CURDATE()</code> returns a date without time, so if your data contains specific time points, you may want to consider whether <code>NOW()</code> needs to be replaced.</p> <p> In addition, if the field is a string instead of <code>DATE</code> or <code>DATETIME</code> type, then it cannot be compared directly. It must be converted to date format first, otherwise the result may be errors.</p> <hr> <p> Basically that's it. After mastering these core functions, most queries related to date and time can be handled. The key is to understand the scope of action and return value type of each function to avoid errors due to inconsistent formats.</p>

The above is the detailed content of Working with date and time functions in MySQL. For more information, please follow other related articles on the PHP Chinese website!