MySQL Date Time

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.

mysql_time_stamp

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,

  1. Initial date or date time value
  2. 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.

This MySQL code tutorial was published on June 30, 2013.

↑ Back to Top