MySQL Date Time
by
Vincy. Last modified on June 2nd, 2021.
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.
- Getting current Date/Time/Timestamp.
- MySQL Date/Time formatting.
- Date/Time/Timestamp arithmetic operations.
- Converting MySQL Date/Time/Timezone.
- Extracting date from MySQL Temporal Data.
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.
Getting Current Date/Time/Timestamp
- CURDATE() / CURRENT_DATE() – This returns current date in YYYY-MM-DD format if this function is used with a string, or YYYYMMDD if used with an integer value.
- CURTIME() / CURRENT_TIME() – This returns current time in either HH: MM: SS or HHMMSS format, depends on where this function is invoked.
- NOW() / CURRENT_TIMESTAMP() – This returns either DateTime value in YYYY-MM-DD HH: MM: SS or timestamp YYYYMMDDHHMMSS format, depends on where this function is invoked.
MySQL Date/Time Formatting
As like as we have performed date formatting using PHP, MySQL also provides set of functions to work with date format.
- DATE_FORMAT() – It accepts two parameters, that is, a date value, and the format string which will be a collection of date component specifiers. For example,
//Output: November 4th, 2008 11:45:00 AM
SELECT DATE_FORMAT( '2008-11-04 11:45:00', '%M %D, %Y %r' );
- TIME_FORMAT() – As Like as DATE_FORMAT(), it accepts time to be formatted with the group of specifiers given as the second argument.
Date/Time/Timestamp Arithmetic Operations
MySQL allows performing set of arithmetic operations by using the following functions. These functions accepts two arguments,
- Initial date or date time value
- Imploded parameter by joining INTERVAL keyword, expression and unit, like,
INTERVAL_expression_unit
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,
- ADDDATE() / DATE_ADD() – Both are performing same functions that are to add the given interval with the initial value.
- SUBDATE() / DATE_SUB() – Similar to date add functions.
MySQL also supports to perform arithmetic operations between two dates, that is
- DATEDIFF() – It subtracts one date from another and returns the number of days between two given dates.
Converting MySQL Date/Time/Timezone
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.
Extracting date from MySQL Temporal Data
- DATE() – This function is used to returns date from the given temporal data. For that, it accepts either date or date time value as its argument.
- EXTRACT() – This function is used to extract the unit of date as specified. For that, it requires two arguments, like, date units like a day, month and etc, and the date value from which the specified unit has to be extracted.
- TIME() – Works as similar as DATE(), but it extracts time to part from the given temporal data.
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.
↑ Back to Top