Important Functions
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.
Getting the Current Date and Time
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:
Getting the Current Date Without the Time
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:
Getting the Current Time Only
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:
Parsing Out Individual Date Parts
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:
Constructing a Datetime From Separate Parts
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:
Going Forward
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.
