MySQL Date Time

by Vincy. Last modified on July 3rd, 2022.

In MySQL, it includes set of date and time-related functions for performing manipulation over data that 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 are required to specify Date/Time types as DATE, DATETIME, and TIMESTAMP. But some of these functions accept string values, 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 the supported range, these MySQL functions reduce the burden like PHP Date Time functions, from making temporal data manipulation by our own logic.

Getting Current Date/Time/Timestamp

  • CURDATE() / CURRENT_DATE() – This returns the 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 the current time in either HH: MM: SS or HHMMSS format, depending 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, depending on where this function is invoked.

MySQL Date/Time Formatting

As like as we have performed date formatting using PHP, MySQL also provides a 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 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 a set of arithmetic operations by using the following functions. These functions accept two arguments,

  1. Initial date or date-time value
  2. Imploded parameter by joining INTERVAL keyword, expression, and the 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 the same functions that are to add the given interval with the initial value.
  • SUBDATE() / DATE_SUB() – Similar to date add functions.

MySQL also supports performing 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,, 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 return the 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 similar to 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 the 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 intervals with the year month value given.

Vincy
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

Leave a Reply

Your email address will not be published. Required fields are marked *

↑ Back to Top

Share this page