Как найти разницу в mysql

This function takes the difference between two dates and shows it in a date format yyyy-mm-dd. All you need is to execute the code below and then use the function. After executing you can use it like this

SELECT datedifference(date1, date2)
FROM ....
.
.
.
.


DELIMITER $$

CREATE FUNCTION datedifference(date1 DATE, date2 DATE) RETURNS DATE
NO SQL

BEGIN
    DECLARE dif DATE;
    IF DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2)))) < 0    THEN
                SET dif=DATE_FORMAT(
                                        CONCAT(
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 , 
                                            '-',
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 , 
                                            '-',
                                            DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(DATE_SUB(date1, INTERVAL 1 MONTH)), '-', DAY(date2))))),
                                        '%Y-%m-%d');
    ELSEIF DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2)))) < DAY(LAST_DAY(DATE_SUB(date1, INTERVAL 1 MONTH))) THEN
                SET dif=DATE_FORMAT(
                                        CONCAT(
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 , 
                                            '-',
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 , 
                                            '-',
                                            DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2))))),
                                        '%Y-%m-%d');
    ELSE
                SET dif=DATE_FORMAT(
                                        CONCAT(
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 , 
                                            '-',
                                            PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 , 
                                            '-',
                                            DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2))))),
                                        '%Y-%m-%d');
    END IF;

RETURN dif;
END $$
DELIMITER;

datediff(expression1,expression2);

Note: expression1is the first date, and expression2 is the second date.

How To Find DateDiff In MySQL

When working with MySQL database and trying to figure out how to find datediff in MySQL, all you have to do is use the MySQL datediff function.

For example, let’s you have two dates: September 1, 2023, and September 20, 2023. And you want to programmatically find the difference between the two (without using your calculator or fingers to count), you’ll use the datediff() function as follows:

Select datediff('2023-09-20','2023-09-01') as Difference;

Once you run the above query against the database, you’ll get the following result:


+------------+
| Difference |
+------------+
|     19     |
+------------+


Notably, bear in mind that the MySQL datediff() function works with the following MySQL versions: 5.7, 5.6, 5.5, 5.1, 5.0 and 4.1.1.
What Does DateDiff Return MySQL
Equally important, you may be wondering what does the MySQL datediff function return. As you can see from the above example, the function will return the number of days between the two dates you provided.
Also, if you provided datetimes instead of date days, then the function will equally return the difference between the two provided date times in days.

[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL
[BENEFITS]:

FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
Multi-Domain Hosting & 99.9% Uptime Guarantee!
Super Fast Servers with 24/7/365 Technical Support!

Click here to access this [SPECIAL OFFER]

How To Use DateDiff In MySQL
Now, let’s take a look at how to use datediff() in MySQL with some practical examples so you can fully understand the concept.
MySQL DateDiff Seconds
First, how do you get the difference between two times in seconds? The datediff() function is probably not the best function to use in such a situation.
So if you’re trying to do MySQL datediff seconds, you’ll come to realize that the datediff() function only returns day values. Therefore, if you supply the seconds in the date, only the date portion is used in the calculation to return the number of days.
Now, there’s the timestampdiff() function which will provide you with the needed capability to calculate the differences between two datetime expressions. And the two expressions must be of the same type.
The syntax is as follows:
timestampdiff(unit,expression1,expression2);

And for the arguments:
1. unit: The unit can be one of the following options: FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
2. expression1: This is a datetime expression you’ll substract expression2 from.
3. expression2: This is a datetime expression you’ll substract from expression1.
Therefore, if you’re looking for MySQL DateDiff seconds, you can use the TimeStampDiff function to accomplish your objective. For example:
Select timestampdiff(SECONDS, '2023-09-20 12:30:15', '2023-09-01 10:15:00') as Difference;

The above query statement will return the different between the two datetime values provided in seconds 2023-09-20 12:30:15 and 2023-09-01 10:15:00 as shown below:


+------------------+
|    Difference    |
+------------------+
|         15       |
+------------------+


MySQL DateDiff Minutes
Likewise, there will be times you need to get the difference in two datetime expression in minutes. Consequently, your natural inclination might be to there’s a MySQL DateDiff Minutes as a solution.
However, as stated above, you’ll come to realize that this won’t work out very well. Instead, use the TimeStampDiff() function to accomplish this task.
Select timestampdiff(MINUTES, '2023-09-20 12:30:15', '2023-09-01 10:15:00') as Difference;

The above query statement will return the difference between the two datetime values provided in minutes 2023-09-20 12:30:15 and 2023-09-01 10:15:00 as shown below:


+------------------+
|    Difference    |
+------------------+
|         15       |
+------------------+


MySQL DateDiff Hours
Furthermore, as you can see from the above two examples when looking for the difference in two datetime expressions, the MySQL DateDiff hours is not a reliable solution.
Once again, you can use the TimeStampDiff() function to get the job done. Let’s take look:
Select timestampdiff(HOURS, '2023-09-20 12:30:15', '2023-09-01 10:15:00') as Difference;

The above query statement will return the difference between the two datetime values provided in hours 2023-09-20 12:30:15 and 2023-09-01 10:15:00 as shown below:


+------------------+
|    Difference    |
+------------------+
|        2         |
+------------------+


MySQL DateDiff Days
The MySQL DateDiff() function’s default return value is the number of days between two date or datetime expressions.
And as we’ve also indicated earlier, if you run a query statement like this:
Select datediff('2023-09-20','2023-09-15') as Difference;

You’ll get the following result:


+------------+
| Difference |
+------------+
|     5      |
+------------+


The result tells you that there’s a difference of 5 days between the two expressions. So this is how the function works.

[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL
[BENEFITS]:

FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
Multi-Domain Hosting & 99.9% Uptime Guarantee!
Super Fast Servers with 24/7/365 Technical Support!

Click here to access this [SPECIAL OFFER]

MySQL DateDiff Months
Of course, there will be occasions where you need to know the difference between two periods. Particularly, let’say you want to know the difference between the periods in months.
Certainly, trying to use the MySQL DateDiff Months in such a situation won’t cut. Luckily, MySQL provides another handy function called the PERIOD_DIFF() which can do the trick.
Syntax:
The PERIOD_DIFF() syntax is as follows:
period_diff(expression1,expression2);

where “expression1” indicates the first period and “expression2” means the second period to subtract from “expression1”.
So here’s an example:
Select period_diff(202310,202302) As Difference;

The above query statement will output


+------------------+
|    Difference    |
+------------------+
|        8         |
+------------------+


The result shows that there is eight months difference between the two periods.
MySQL DateDiff Years
Moreover, if you’re looking for MySQL DateDiff Years, you can accomplish the same using the PERIOD_DIFF() function above as well as the TIMESTAMPDIFF() described above as well.
For PERIOD_DIFF() example:
Select period_diff(2025,2023) As Difference;

The above query statement will return:


+------------------+
|    Difference    |
+------------------+
|        2         |
+------------------+


For TIMESTAMPDIFF() example:
Select timestampdiff(YEAR, '2025-09-20 12:30:15', '2023-09-01 10:15:00') as Difference;

The above query statement will also return:


+------------------+
|    Difference    |
+------------------+
|        2         |
+------------------+


MySQL DateDiff Current Date
Besides, you can use the function together with the Current Date function. The CURDATE() function returns the value of the current date on the system.
So here’s a practical example:
Select datediff(curdate(),'2023-09-01') as Difference;

Once you run the above query against the database, If the current date is ‘2023-09-30’, you’ll get the following result (in days):


+------------+
| Difference |
+------------+
|     29     |
+------------+


Final Thought
We hope that this article has given you more insight into this function, it’s applications as well as limitations. As with anything, feel free to solidify your understanding of the fundamental concepts by reading other available resources as well as MySQL documentation. Good luck!

[SPECIAL OFFER]: Fastest Web Hosting with FREE MySQL
[BENEFITS]:

FREE 1-Click Install of Open Source Apps, Blog, CMS, and much more!
Support for PHP, MySQL, ASP.NET, SQL Server, WordPress, Joomla and much more!
Multi-Domain Hosting & 99.9% Uptime Guarantee!
Super Fast Servers with 24/7/365 Technical Support!

Click here to access this [SPECIAL OFFER]

• Главная»
• Уроки»

• Разное»

• 6 запросов для MySQL

• Метки урока:
• mysql
• кодинг

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0; 

UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 )

SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(*) = N; 

SELECT COUNT(*)
FROM calendar
WHERE d BETWEEN Start AND Stop
  AND DAYOFWEEK(d) NOT IN(1,7)
  AND holiday=0; 

SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING (constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
  AND t.table_schema='db'
  AND t.table_name='tbl'

SELECT
  table_schema AS 'Db Name',
  Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)',
  Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'
FROM information_schema.tables
GROUP BY table_schema ; 

5 последних уроков рубрики «Разное»

• 

  Как выбрать хороший хостинг для своего сайта?

  Выбрать хороший хостинг для своего сайта достаточно сложная задача. Особенно сейчас, когда на рынке услуг хостинга действует несколько сотен игроков с очень привлекательными предложениями. Хорошим вариантом является лидер рейтинга Хостинг Ниндзя — Макхост.

• 

  Как разместить свой сайт на хостинге? Правильно выбранный хороший хостинг — это будущее Ваших сайтов

  Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.

• 

  Разработка веб-сайтов с помощью онлайн платформы Wrike

  Создание вебсайта — процесс трудоёмкий, требующий слаженного взаимодействия между заказчиком и исполнителем, а также между всеми членами коллектива, вовлечёнными в проект. И в этом очень хорошее подспорье окажет онлайн платформа Wrike.

• 

  20 ресурсов для прототипирования

  Подборка из нескольких десятков ресурсов для создания мокапов и прототипов.

• 

  Топ 10 бесплатных хостингов

  Небольшая подборка провайдеров бесплатного хостинга с подробным описанием.