MySQL Date Time

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.

  • 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 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.

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 string, or YYYYMMDD if used with 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 parameter, that is, 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 to perform set of arithmetic operations by using the following functions. These functions accepts two arguments,

  1. Initial date or datetime 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 day, month and etc.

And the functions are,

  • ADDDATE() / DATE_ADD() – Both are performing same functions that is 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 subtract 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 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.

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 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 extract time part from the given temporal data.

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.

↑ Back to Top