Miscellaneous Date and Time Functions in MySQL
Date and Time in MySQL – Part 9
Forward: In this part of the series I explain important date and time functions in MySQL that I have not explained in the previous parts of the series.
By: Chrysanthus Date Published: 4 Sep 2012
Introduction
The NOW() Function
The NOW() function returns the current date and time (datetime) of the computer that has the MySQL server. The syntax is:
NOW()
The following statement illustrates its use:
SELECT now();
In my computer, the returned value is, 2012-06-08 16:40:05 .
The QUARTER() Function
This function returns the quarter of the year of a given date in the range 1 to 4. The first quarter is Jan-Mar, the second is Apri-Jun, etc. The syntax is:
QUARTER(date)
The date value can be of any year. The following statement illustrates this:
SELECT quarter('2015-05-01');
The return value for this statement is 2.
The STR_TO_DATE() Function
This function converts a string to a date. The syntax is:
STR_TO_DATE(str,format)
where str is the string and format is also a string but having the formatting requirements (specifiers in their positions). str is a string with the month day, month and year separated by commas. The following statement illustrates the use of the function.
SELECT str_to_date('01,5,2014', '%d,%m,%Y');
The return value for this is, 2014-05-01. Note that the positions of the items in the str or format argument do not correspond to the positions in the output. However, the positions in str must correspond to the positions in the format argument.
In one of the previous parts of the series, I talked about the DATE_FORMAT function. The date format function is used to give proper formatting to dates and datetime types. If you want proper formatting for time alone, you have to use the TIME_FORMAT() Function. The syntax is:
TIME_FORMAT(time,format)
The rules for formatting time are the same as the rules for formatting date. You need to use the list titled, Specifiers and their Meanings, in the tutorial titled, MySQL Date and Time Formatting Details, of this series. The following statement illustrates how to use the TIME_FORMAT function.
SELECT time_format('22:13:35', '%l:%i:%s %p');
The output of this statement is: 10:13:35 PM. This statement converts a 24-hour time to a 12-hour time indicating whether it is AM or PM. To understand the logic of the arguments of the function, you should refer to the list mentioned above; I explained the logic in the tutorial of the list. Note: time formatting is not a common thing; that is why I have not placed emphasis on it, in this series.
Chrys