Arithmetic functions in MySQL are essential tools for performing a variety of numerical operations
directly within SQL queries.
These built-in functions enable users to execute calculations, manipulate numeric data, and
perform statistical analysis efficiently. In this article, We will learn about Mathematical functions in
MySQL by understanding various methods.
Arithmetic functions in MySQL
Arithmetic functions in MySQL are built-in functions that perform mathematical operations
on numerical values.
They are essential for executing calculations, manipulating numeric data, and performing
statistical analysis within SQL queries.
These functions cover a wide range of mathematical operations, including basic arithmetic,
rounding, and trigonometric calculations.
Various Mathematical functions in MySQL
Here’s a tabular representation of commonly used Mathematical functions in MySQL:
Function Description Example Result
Returns the absolute value of a
ABS() SELECT ABS(-10); 10
number.
Rounds a number up to the nearest
CEIL() or CEILING() SELECT CEIL(4.3); 5
integer.
Rounds a number down to the
FLOOR() SELECT FLOOR(4.7); 4
nearest integer.
Rounds a number to a specified SELECT
ROUND() 123.46
number of decimal places. ROUND(123.456, 2);
POW() or POWER( Returns the result of a number raised
SELECT POW(2, 3); 8
) to a specified power.
SQRT() Returns the square root of a number. SELECT SQRT(16); 4
Advantages of Arithmetic Functions in MySQL
1. Data Analysis: It Enable complex mathematical operations and data analysis directly within
SQL queries.
2. Data Transformation: It Facilitate the transformation of raw data into meaningful insights
through calculations and manipulations.
3. Simplify Queries: It Reduce the need for application-level processing by performing
calculations and transformations in SQL.
4. Enhanced Reporting: It Improve reporting capabilities by allowing dynamic calculations and
aggregations based on the data.
DATE and TIME Functions
MySQL comes with many built-in functions that allow you to manipulate data. These functions are
grouped into categories – date functions, string functions, mathematic functions, and others.
Date functions give you numerous options on how to modify, calculate, and convert date, time, and
datetime expressions in MySQL.
In MySQL, date functions are used to work with DATE, TIME, DATETIME, and TIMESTAMP values.
Here are the most commonly used MySQL date functions with clear examples 👇
1. CURDATE()
Returns the current date (YYYY-MM-DD)
SELECT CURDATE();
🟢 Output: 2026-01-11
2. NOW()
Returns the current date and time
SELECT NOW();
🟢 Output: 2026-01-11 17:10:30
3. CURRENT_TIMESTAMP()
Same as NOW()
SELECT CURRENT_TIMESTAMP();
4. DATE()
Extracts the date part from a datetime
SELECT DATE('2026-01-11 10:30:45');
🟢 Output: 2026-01-11
5. YEAR(), MONTH(), DAY()
Extract year, month, or day
SELECT
YEAR('2026-01-11'),
MONTH('2026-01-11'),
DAY('2026-01-11');
🟢 Output: 2026 | 1 | 11
6. DATEDIFF()
Returns difference between two dates (in days)
SELECT DATEDIFF('2026-01-20', '2026-01-11');
🟢 Output: 9
7. DATE_ADD()
Adds days/months/years to a date
SELECT DATE_ADD('2026-01-11', INTERVAL 5 DAY);
🟢 Output: 2026-01-16
8. DATE_SUB()
Subtracts days/months/years from a date
SELECT DATE_SUB('2026-01-11', INTERVAL 1 MONTH);
🟢 Output: 2025-12-11
9. DAYNAME()
Returns the day name
SELECT DAYNAME('2026-01-11');
🟢 Output: Sunday
10. MONTHNAME()
Returns the month name
SELECT MONTHNAME('2026-01-11');
🟢 Output: January
11. TIMESTAMPDIFF()
Difference between two dates in a specific unit
SELECT TIMESTAMPDIFF(YEAR, '2000-04-19', CURDATE());
In MySQL, time functions are used to work with TIME, DATETIME, and TIMESTAMP values.
Below are the most important MySQL TIME functions with easy examples 👇
1. CURTIME()
Returns the current time
SELECT CURTIME();
🟢 Output: 17:20:45
2. NOW()
Returns current date and time
SELECT NOW();
🟢 Output: 2026-01-11 17:20:45
3. CURRENT_TIME()
Same as CURTIME()
SELECT CURRENT_TIME();
4. TIME()
Extracts time from datetime
SELECT TIME('2026-01-11 17:20:45');
🟢 Output: 17:20:45
5. HOUR(), MINUTE(), SECOND()
Extract hour, minute, second
SELECT
HOUR('17:20:45'),
MINUTE('17:20:45'),
SECOND('17:20:45');
🟢 Output: 17 | 20 | 45
➕ 6. ADDTIME()
Adds time to a given time
SELECT ADDTIME('10:30:00', '02:15:00');
🟢 Output: 12:45:00
➖ 7. SUBTIME()
Subtracts time
SELECT SUBTIME('10:30:00', '01:00:00');
🟢 Output: 09:30:00
⏳ 8. TIMEDIFF()
Difference between two times
SELECT TIMEDIFF('18:00:00', '10:30:00');
🟢 Output: 07:30:00
🕛 9. SEC_TO_TIME()
Converts seconds → time
SELECT SEC_TO_TIME(4500);
🟢 Output: 01:15:00
🔢 10. TIME_TO_SEC()
Converts time → seconds
SELECT TIME_TO_SEC('01:15:00');
🟢 Output: 4500
📅 11. EXTRACT()
Extract part of time
SELECT EXTRACT(HOUR FROM '17:20:45');
🟢 Output: 17
📅 DATE Functions in MySQL
Function Description
CURDATE() Current date
CURRENT_DATE() Current date
DATE() Extract date from datetime
DAY() Day of month
DAYNAME() Day name
DAYOFMONTH() Day of month
DAYOFWEEK() Day of week (1–7)
DAYOFYEAR() Day of year
WEEK() Week number
WEEKDAY() Weekday index (0–6)
MONTH() Month number
MONTHNAME() Month name
YEAR() Year
QUARTER() Quarter of year
LAST_DAY() Last day of month
DATEDIFF() Difference between dates
DATE_ADD() Add date interval
DATE_SUB() Subtract date interval
ADDDATE() Add date
SUBDATE() Subtract date
Function Description
STR_TO_DATE() String → Date
DATE_FORMAT() Format date
⏰ TIME Functions in MySQL
Function Description
CURTIME() Current time
CURRENT_TIME() Current time
TIME() Extract time
HOUR() Hour
MINUTE() Minute
SECOND() Second
ADDTIME() Add time
SUBTIME() Subtract time
TIMEDIFF() Difference between times
TIME_TO_SEC() Time → Seconds
SEC_TO_TIME() Seconds → Time
MAKETIME() Create time
EXTRACT() Extract part
DATETIME / TIMESTAMP Functions
Function Description
NOW() Current date & time
CURRENT_TIMESTAMP() Current timestamp
SYSDATE() System date & time
UTC_DATE() Current UTC date
UTC_TIME() Current UTC time
UTC_TIMESTAMP() Current UTC datetime
Function Description
FROM_UNIXTIME() Unix → Date
UNIX_TIMESTAMP() Date → Unix
TIMESTAMP() Create timestamp
TIMESTAMPADD() Add interval
TIMESTAMPDIFF() Difference
🔤 Formatting & Conversion Functions
Function Description
DATE_FORMAT() Format date
TIME_FORMAT() Format time
STR_TO_DATE() String → Date
CAST() Convert data type
CONVERT() Convert data type
Aggregate functions
An aggregate function is a function that performs a calculation on a set of values, and returns a single
value.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP
BY clause splits the result-set into groups of values and the aggregate function can be used to return
a single value for each group.
The most commonly used SQL aggregate functions are:
MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column
Aggregate functions ignore null values (except for COUNT(*)).