In MySQL, it includes set of date and time related functions for performing manipulation over data which indicate 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 string that is to be converted to Date.
Since date time calculation has a challenge over creating valid result with in 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 to perform 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 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 in-built function to convert timezone.
CONVERT_TZ() – It accepts three arguments, as, datetime value, from timezone and to timezone.
TIME_TO_SEC() – This functions is used to convert the given time value into number of seconds.
MySQL contains 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 day of a week, week of a month, 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.
This MySQL code tutorial was published on June 30, 2013.