MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL TIMESTAMPDIFF in MySQL MySQL Syntax Checker Sudo MySQL Secure Installation

MySQL Date and Time function
2022-04-08 08:11:54

MySQL Date and Time function

The date function displays day, year, month, time, and current date. It shows the date and time as per the requirement of the applications. The data either store on the table or display directly on an output page. Type of the MySQL date and time functions show below. You can see the date function, time function, and date and time function together.

Syntax

MySQL function uses the following syntax to display date and time.

SELECT function_name(date/time);

MySQL date and time function require multiple values or arguments.

SELECT function_name(date_time1, date_time2);

MySQL date and time function syntax with additional values are shown below.

SELECT function_name(date/time) + value;

Date and Time functions

List of the date and time function with description shows below.

FunctionDescription
ADD_DATEThe adddate() function shows a date interval as per requirement.
 ADD_TIMEThe add_time() function displays a time interval as per requirement.
CURDATEThe curdate() function displays a current date interval.
CURRENT_DATEThe current_date() function shows a current date interval.
 CURRENT_TIMESTAMPThe current_timestamp() function is a type of the MySQL date and time function. This function displays the current date and time.
CURTIMEThe curtime() function shows a current time.
DATEThe date() function extracts the date interval as per requirement. This function does not display time as output.
DATEDIFFThe datediff() function displays the days between two dates.
DATE_ADDThe date_add() function adds a date to date and shows the date.
 DATE_SUBThe date_sub() function subtracts a date to date and returns the date.
DAYThe day() function displays the day of the date or month.
 DAYOFMONTHThe dayofmonth() shows the month of the date.
DAY OF WEEKThe dayofweek() function is a type of the MySQL date and time function. This function returns the week of the date.
DAYOFYEARThe dayofyear() function is a type of the MySQL date and time function. This function displays the year of the date.
 FROM_DAYSThe from_days() function returns the numerical date value of the given date. 
HOURThe hour() function is a type of the MySQL date and time function.  This function shows the hours of the date and time.
LOCAL TIMEThe localtime() function is a type of the MySQL time function. This function displays the current date and time.
LOCALTIMESTAMPThe localtimestamp() function displays the current date and time
MAKEDATEThe makedate() function creates and displays the date. 
MAKETIMEThe maketime() function is a type of the MySQL time function. This function creates and displays the time (hour, minute, and second).
MICROSECONDThe microsecond() function shows the microsecond of the given date and time.
MINUTEThe minute() function displays the minute of the given time or date-time.
MONTHThe month() function shows the month of the given date.
MONTHNAMEThe monthname() function returns the month of the given date.
NOWThe now() function is a type of the MySQL date and time function.  This function shows the current date and time.
PERIOD_ADDThe period_add() function is type of the MySQL date function. This function inserts months of the date as a period. This function displays year and month but not date.
PERIOD_DIFFThe period_diff() function displays the difference between the two periods.
QUARTERThe quarter() function is a type of the MySQL date and time function. This function shows the quarter of the given year.
SECONDThe second() function returns the second of the given datetime. 
SEC_TO_TIMEThe sec_to_time() function displays a time of the datetime into seconds.
STR_TO_DATEThe str_to_date() function shows a date into a string format.
SUBDATEThe subdate() function subtracts a date of the datetime and returns the date.
SUBTIMEThe subtime() function subtracts a time of the datetime and returns the time.
TIMEThe time() function is type of MySQL time function. This function shows the time of the given datetime. 
TIME_FORMATThe time_format() function displays time with the required format.
TIME_TO_SECThe time_to_sec() function converts a given time into seconds.
TIMEDIFFThe timediff() function is a type of the MySQL time function. This function differences between two times or datetime.
 TIMESTAMPThe timestamp() function returns a date or datetime.
TO_DAYSThe to_days() function shows the number of days between two dates.
 WEEKThe week() function displays the week number of the date.
 WEEKDAY The weekday() function displays the number of the weekday of the date.
 WEEKOFYEARThe weekofyear() function shows the weeks of the date.
YEARThe year() function returns the year of the date.
 YEAR WEEKThe yearweek() function displays the year and week of the given date.

Examples of the MySQL Date and Time functions

The following example determines and returns a date, time and both date/time information. There are multiple functions to displays current, past and required dates and times.

The following statements shows you the current date, time with the respective function. You can return date and time simultaneously using the MySQL function. Here, you can use a single function or multiple functions in the query.

Example

The single date and time function's example and output are shown below.

mysql> SELECT CURRENT_TIMESTAMP();

Output  

MySQL Date and Time function

In this output image, the table returns the current date, time in a particular format. The date function displays in the "year-month-day" format. The time displays in the "hour:minute:second" format.

Example

Multiple MySQL functions used to display the date and time output. Here is the statement:

mysql> SELECT now(), CURTIME(), CURDATE();

Output  

MySQL Date and Time function

You can get three columns and one row. These three columns represent three date and time functions. The Now() function return instant date and time as output. The CURTIME() and CURDATE() displays time and date values, respectively.

Examples of specific dates and times with the output

MySQL date/time function displays a specific feature of the date or time. You can return only the day, month, year, and week of the date. Here, you can get the second, hours, and minutes of the time.

Example

Below query shows you date, day, week, month and year. If you input time value in the specific date function then output returns only date and its features. This function does not show you time value.

mysql> select date('2021-08-30, 14:57:40') as date, 
 day('2021-08-30, 14:57:40') AS day,
week('2021-08-30, 14:57:40') AS week,
month('2021-08-30, 14:57:40') AS month, 
year('2021-08-30, 14:57:40') AS year;

Output

MySQL Date and Time function

Here, you see the specific date information like day, month, year, and week. Here time is not working with any function. Each column specifies a particular date value as per function. This example uses the day, week, date, year, and month function with values.

Example

The following query displays time, second, minute, hours using MySQL date/time function. If you enter date in the function then output returns only date and its function.

mysql> select time('2021-08-30, 14:57:40') as time, 
minute('2021-08-30, 14:57:40') AS minute,
hour('2021-08-30, 14:57:40') AS hour, 
second('2021-08-30, 14:57:40') AS second;

Output

MySQL Date and Time function

The above output works on the time function of MySQL. The first column shows the required time in the table. The minute(), hour(), and second() functions used to display specific time part as per functions. You cannot use to display the date using these functions.

Examples of specific date functions with output

MySQL date function works on the date function as per requirement. You can modify, add, and maintain the date information of the table. The following examples show you the operation of the date using some functions.

Example

MySQL date function uses four functions or methods in the system. You can add and subtract date as per interval. The query displays difference between two dates and shows dates with string value.

mysql> select adddate('2021-06-21', interval 13 hour) AS date,
    -> datediff('2021-06-26', '2021-06-22') AS differences,
    -> from_days(3401232) AS days,
    -> subdate('2021-06-21', interval 13 hour) AS dates;

Output

MySQL Date and Time function

The above table image shows different functions and their value as output. The first column adds date with the 13-hour interval, and the last column subtracts date with the given interval. The second column returns the difference between the two dates, and the third column shows a date of the given number.

Example

MySQL example displays date as per required format. You can displays weekday and month of the date using expressions. You can choose date format with display method using date_format() condition. The date format function provides several expressions with percentage sign. Here, you can change expressions sequence to display date in the table.

mysql> select date_format('2021-06-21', '%a  -  %b') AS day_month, 
date_format('2021-06-21', '%e - %M - %Y') AS date,
date_format('2021-06-21', '%d / %c / %y') AS dates;

Output

MySQL Date and Time function

The above image shows you three columns and one row of the date value. The first column displays the day of the week and month of the date using "%a" and "%b" expressions. The second and third columns show the date with the given format. You can use four-digit and two-digit year format with the "%Y" AND %y" operators, respectively.

Examples of specific dates and times with

You can use several functions to operate and maintain time information. The date/time function required or current time for their operation. You can display time as per the required format.  

Example

The time function uses several functions with time value in the system. You can operate time and maintain it using add and subtract time function. The query displays time information with required interval and operations.

mysql> select addtime('14:57:40', '01:57:40') AS aadtime,
     timediff('12:57:40', '02:57:40') AS differences,
     time_to_sec('01:57:40') AS second, 
     sec_to_time('440') AS time,
     subtime('14:57:40', '01:57:40') AS subtime;

Output

MySQL Date and Time function

The above table shows five columns and one row with time information. Each column represents a different date/time function. The first column returns added time, and the last column returns subtracted time of the data. The second column shows the difference between times in time format. The third function converts time into second. The fourth function converts the given second into time.

Example

MySQL time function displays time in different format. You can displays weekday and month of the date using expressions. You can choose date format with display method using date_format() condition. The date format function provides several expressions with percentage sign. Here, you can change expressions sequence to display date in the table.

mysql> select time_format('14:57:40', '%h  %i  %s') AS time1, 
time_format('14:57:40', '%h  %i  %s  %p') AS time2, 
time_format('14:57:40', '%H  %I  %S') AS time3,
time_format('14:57:40', '%r') AS time4;

Output

MySQL Date and Time function

The above output displays four columns for display different time formats. If you use the uppercase expression, then time displays in 24-hour format. If time uses the lowercase expression, then the time function returns the 12-hour format. The "%r" and "%p" expressions show time is either AM or PM. The "%h", "%i", and "%s" shows hour, minute, and second respectively.