MySQL Date Time

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.

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.

↑ Back to Top

• PHP
• jQuery
• JavaScript
• WordPress
• MySQL
  • MySQL Basics
    • MySQL Data Types
    • MySQL Database Files
    • MySQL SHOW
    • MySQL CONCAT
    • MySQL OrderBy
    • MySQL Date Time
    • MySQL JOINs: An Introduction
    • MySQL Left Join
    • MySQL Right JOIN
    • MySQL vs MySQLi in PHP
  • phpMyAdmin
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js