In MySQL, it includes set of date and time-related functions for performing manipulation over data which indicate the date, time or both. Using these functions, some common operations taken place are listed below.
For performing the above operations, MySQL functions required to specify Date/Time types as DATE, DATETIME, and TIMESTAMP. But some of these functions accepts string value, 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 supported range, these MySQL functions reduce burden as like as 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 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 set of arithmetic operations by using the following functions. These functions accepts 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 to perform 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, as, 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 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 interval with the year month value given.