Working with Dates and Times in MySQL – Part 3:

Mar 14, 2022 by Robert Gravelle

In the first two installments of this series on Dates and Times, we covered MySQL’s five temporal data types. Now it’s time to turn our attention to some of MySQL’s many date/time-oriented functions.

Back in May of 2021, we covered some of SQL Server’s notable Date & Time functions, starting with how to obtain the current date and time. It offers the GETDATE() function for that purpose. MySQL’s equivalent function is simply called NOW(). In Navicat for MySQL 16, we can invoke this function without connecting to a database, since we aren’t selecting any table columns:

As mentioned in Part2, the TIMESTAMP type is similar to DATETIME, but are generally used to track changes to records. To obtain the current date and time as a TIMESTAMP, we can use the current_timestamp() function. Here’s its output:

If you only want to get current date in MySQL, you can use either the curdate() or current_date() functions. The system variable current_date also works. In any event, all three give latest date in YYYY-MM-DD format:

Likewise, we can get the current time in MySQL using the curtime() or current_time() functions, as well as the current_time system variable. These all give the latest time in HH:MM:SS format:

SQL Server offers the versatile DATEPART() function to extract part of a datetime. MySQL provides the equivalent EXTRACT() function for this purpose. Similar to the SQL Server function, EXTRACT() accepts a part unit and the date:

EXTRACT(part FROM date)

Here are all of the valid part values:

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

Being February at the time of this writing, the following call to EXTRACT() yields a value of “2”:

As the following query shows, it is currently 43 minutes past the hour:

Additional Date Parsing Functions

Having trouble remembering all of the part units? That’s OK, because MySQL provides separate functions for date and time parsing a well.

For parsing either the date or time from a datetime value, there are the DATE() and TIME() functions, respectively:

To split a date into its constituent parts, we can use the YEAR(), MONTH(), and DAYOFMONTH() (or DAY()) functions:

Time portions also get their own function: HOUR(), MINUTE(), and SECOND() respectively:

In MySQL, there are many ways to create a datetime from separate date and time parts, enough to garner their own article. For now, let’s look at one way to create a Date. The MAKEDATE() function returns a date given a year and dayofyear. Here’s an example:

In this blog, we explored some of MySQL’s many date/time-oriented functions. In the next installment, we’ll cover some other ways to create dates and times in MySQL.