In MySQL, it includes set of date and time-related functions for performing manipulation over data that indicate the date, time or both. Using these functions, some common operations taken place are listed below.
For performing the above operations, MySQL functions are required to specify Date/Time types as DATE, DATETIME, and TIMESTAMP. But some of these functions accept string values, for example, STR_TO_DATE() accepts a string that is to be converted to Date.
Since date-time calculation has a challenge over creating a valid result within the supported range, these MySQL functions reduce the burden like PHP Date Time functions, from making temporal data manipulation by our own logic.
As like as we have performed date formatting using PHP, MySQL also provides a set of functions to work with date format.
# Output: November 4th, 2008 11:45:00 AM SELECT DATE_FORMAT('2008-11-04 11:45:00', '%M %D, %Y %r');
MySQL allows performing a set of arithmetic operations by using the following functions. These functions accept two arguments,
expression – string denotes the value to be added or subtracted with the initial value.
unit – unit of the interval like a day, month and etc.
And the functions are,
MySQL also supports performing arithmetic operations between two dates, that is
Unlike PHP timezone conversion, MySQL provides an inbuilt function to convert timezone.
CONVERT_TZ() – It accepts three arguments,, DateTime value, from time zone and to timezone.
TIME_TO_SEC() – This function is used to convert the given time value into a number of seconds.
MySQL contains a huge list of functions related to date, time and timezone, apart from the above list of operations. For example, it can extract the date components like a day of a week, week of a month, the month of the year and etc, by using functions like DAYOFWEEK(), DAYOFYEAR() and etc.
And, other arithmetic functions like PERIOD_ADD() and PERIOD_DIFF() are used to add and subtract specified intervals with the year month value given.