Как найти разницу между датами в sql

Difference between two dates in MySQL

How to calculate the difference between two dates, in the format YYYY-MM-DD hh: mm: ss and to get the result in seconds or milliseconds?

14 Answers 14

So, you can use TIMESTAMPDIFF for your purpose.

If you are working with DATE columns (or can cast them as date columns), try DATEDIFF() and then multiply by 24 hours, 60 min, 60 secs (since DATEDIFF returns diff in days). From MySQL:

Get the date difference in days using DATEDIFF

OR

If you want to add where clause with DATEDIFF then it is also possible to add where clause or condition. Take a look of following example.

SELECT ( DATEDIFF(‘1993-02-20′,’1993-02-19’)*( 24*60*60) )AS ‘seccond’;

Or, you could use TIMEDIFF function

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

OK it’s not quite what the OP asked, but it’s what I wanted to do 🙂

This code calculate difference between two dates in yyyy MM dd format.

If you’ve a date stored in text field as string you can implement this code it will fetch the list of past number of days a week, a month or a year sorting:

%d%m%Y is your date format

This query display the record between the days you set there like: Below from last 7 days and Above from last 14 days so it would be your last week record to be display same concept is for month or year. Whatever value you’re providing in below date like: below from 7-days so the other value would be its double as 14 days. What we are saying here get all records above from last 14 days and below from last 7 days. This is a week record you can change value to 30-60 days for a month and also for a year.

SQL DATEDIFF

Summary: in this tutorial, you will learn how to use the SQL DATEDIFF() function to calculate the difference between two dates.

Syntax

To calculate the difference between two dates, you use the DATEDIFF() function. The following illustrates the syntax of the DATEDIFF() function in SQL Server:

Arguments

datepart

The datepart is a part of the date in which you want the function to return. The following table illustrates the valid parts of date in SQL Server:

Valid Date Part	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

startdate, enddate

The startdate and enddate are date literals or expressions from which you want to find the difference.

Return

The DATEDIFF() function returns an integer value with the unit specified by the datepart argument.

Examples

The following example returns the number of year between two dates:

Here is the result:

To get the number of month or day, you change the first argument to month or day as shown below:

The following shows the result:

Notice that the DATEDIFF() function takes the leap year into account. As shown clearly in the result, because 2016 is the leap year, the difference in days between two dates is 2×365 + 366 = 1096.

The following example illustrates how to use the DATEDIFF() function to calculate the difference in hours between two DATETIME values:

Consider the following example:

It also returns two because the DATEDIFF() function returns an integer only. In this case, it truncated the minute part and only consider the hour part.

The following example shows how to use the DATEDIFF() function to calculate the year of services of employees up to January 1st, 2018:

DATEDIFF in MySQL

Unlike SQL Server, MySQL has a slightly different DATEDIFF() function syntax:

MySQL only returns the difference between two dates in days. It ignores all the time part of the date in the calculation. See the following example:

The result is nine days:

In this tutorial, you have learned how to use the SQL DATEDIFF() function to calculate the difference between two dates.

[SQL Basic] How to work with Date Functions in SQL — SQL DATE_FORMAT, DATEDIFF

Our SQL tutorials so far have covered how to look up data, navigate through it, and perform simple calculations. But in practice we want to to extract specific data from specific tables, not all the data in all the tables. The queries we have learned so far can filter most of that information, but we still need to cover a few more. Here are a few more queries and functions that are essential to your business.

There are certain standards that cannot be omitted from marketing and data analysis. The most important criteria when looking at existing customers, new customers, sales and other data is the date. In this tutorial, we will learn how to handle dates with SQL queries.

#Glossary:

��Lesson 3: SELECT / FROM / WHERE
��Lesson 4: ORDER BY
��Lesson 5: AND / OR / IN
��Lesson 6: LIKE
��Lesson 7: AS /DISTINCT / IS NULL
��Lesson 9: COUNT / SUM
��Lesson 10: AVG / MAX / MIN
��Lesson 11: GROUP BY / HAVING
��Lesson 12: CASE
��Lesson 13: INNER JOIN
��Lesson 14: OUTER LEFT JOIN / OUTER RIGHT JOIN
��Lesson 15: SUBQUERIES

#Table of Contents

Query 1. Find the number of rentals that occur on a daily basis
SELECT DATE_FORMAT(rental_date, ‘%Y-%m-%d’) AS date, count(rental_id) AS count_rental FROM rental GROUP BY date;

Query 2. Find which day of the week has the most rentals
SELECT DATE_FORMAT(rental_date, ‘%W’) AS day_of_the_week, count(rental_id) AS count_rental FROM rental GROUP BY day_of_the_week ORDER BY count_rental DESC;

Query 3. Find the rental duration by calculating the difference between the rental date and return date
SELECT rental_id, customer_id, rental_date, return_date, DATEDIFF(return_date, rental_date) AS rental_duration FROM rental;

Before we dive into the information of the rental table, we need to become familiar with the date function and format of the SQL query. Let’s first run a query that prints the current date using DATE_FORMAT .

#DATE_FORMAT

Think of DATE_FORMAT as a column name and plug it in after your SELECT query, then specify a factor that has to do with the date. In our case, we want today’s date ( now() ). Next, we’ll specify the format. Using commas, spaces, and dashes, we can rearrange the date in any format we want! For our example today, we will demonstrate two: First, year-month-day followed by the name of the day( ‘%Y-%m-%d, %a’ ). Second, day-month-year preceded by the name of the day( ‘%W, %d-%m-%y’ ).

SELECT DATE_FORMAT(now(), ‘%Y-%m-%d, %a’);
SELECT DATE_FORMAT(now(), ‘%W, %d-%m-%y’);

⭐️SQLGate provides a multi-query function that enables you to run two or more queries simultaneously and view query results at the same time!

Here is a table of helpful shortcuts you can use:

Q 1. Find the number of rentals that occur on a daily basis and organize by date (using COUNT and DATE_FORMAT)

First, let’s look at the rental history data in the rental table.

SELECT * FROM rental;

The table contains the following information:

— Rental ID: (rental_id)
— Date of the rental: (rental_date)
— Inventory ID: (inventory_id)
— Date of return: (return_date)
— Customer ID:(customer_id)
— Staff ID: (staff_id)

Let’s say you want to look at the number of movie rentals that occur at the rental store on a daily basis. It’s simple. We can easily do this using the DATE_FORMAT query along with previously learned COUNT (tutorial 9) and GROUP BY (tutorial 11).

This time, instead of using now() we will designate rental_date as the column we want date information to be pulled from, and specify the format as year-month-day. Let’s also rename this new column ‘date’.

SELECT DATE_FORMAT(rental_date, ‘%Y-%m-%d’ ) AS date,

�� Remember to capitalize the Y in %Y so the full 4 digit year shows!

To find the number of all rentals, use COUNT and (rental_id) , then rename that new column as count_rental. We’re using ID here because every movie has a unique ID assigned to them. Specify the table we want all this information from (rental):

COUNT(rental_id) AS count_rental FROM rental

And finally organize the information date with GROUP BY . Your final query should look like this:

SELECT DATE_FORMAT(rental_date, ‘%Y-%m-%d’) AS date, count(rental_id) AS count_rental FROM rental GROUP BY date;

��Tip: You can use F9 in SQLGate to instantly clean up your query!

This query returns the number of rentals on every day available on the record. Although it doesn’t offer exact time (00:00:00), it does give the year-day-month of the rental starting from 8 rentals on May 24, 2005.

Q 2. Find which day of the week has the most rentals (using GROUP BY and ORDER BY)

Now that we’ve checked the number of movie rentals on a daily basis, let’s check the number of movie rentals on a week day basis. We will use the same data in the rental table and the query statements are very similar. The only thing we’ll change is the date output format.

Before we used ‘ %Y-%m-%d ’ to find the full date of the rental day. But in this next query, we only want the name of the week-day. If you check the handy table we provided in this tutorial, you will see to get this information we need to use a capital W ( %W ).

Just like Query 1, use the rental_date column as a basis for the date information( DATE_FORMAT(rental_date, ). For the format, clarify that you only want the name of the day( %W ). And rename the new column with AS .

SELECT DATE_FORMAT(rental_date, ‘%W’) AS day_of_the_week

Next use COUNT to find the sum of the rentals and rename that column as well:

count(rental_id) AS count_rental

Clarify the name of the table:

Organize the information with GROUP BY and ORDER BY using the new column names we just made:

GROUP BY day_of_the_week ORDER BY count_rental DESC

Put it all together!!

SELECT DATE_FORMAT(rental_date, ‘%W’) AS day_of_the_week, count(rental_id) AS count_rental FROM rental GROUP BY day_of_the_week ORDER BY count_rental DESC;

�� Once again, don’t forget you can use F9 in SQLGate to tidy the query.

As you can see in the results, the largest number of loans occurred on Tuesday.

Q 3. Find the rental duration by calculating the difference between the rental date and return date

Still using the information in the rental table, let’s calculate the rental period, rental date and the return date for each movie. We can do this using the query DATEDIFF .

#DATEDIFF

DATEDIFF calculates the number of days between two date values, date1 and date2. In our example, we want to know how long the movie was rented. So we will calculate the difference between the day it was rented out (rental_date) and the day it was returned (return_date). Go ahead and rename the new column to Rental Duration:

DATEDIFF(return_date, rental_date) AS rental_duration FROM rental

Earlier we mentioned we also want to see the rental ID, customer ID, rental date and return date, so specify those in the SELECT line.

SELECT rental_id, customer_id, rental_date, return_date

Of course, don’t forget to clarify which table you want all this info from:

Put it all together and it should look like this:

SELECT rental_id, customer_id, rental_date, return_date, DATEDIFF(return_date, rental_date) AS rental_duration FROM rental;

This method can also be useful for inquiring the average lease period/max loan period/minimum loan period.

SQL функции даты и времени

Приветствую Вас, уважаемые читатели блога webcodius.ru. В базе данных часто требуется хранить различные данные связанные с датой и временем. Это может быть дата добавления информации, дата регистрации пользователя, время последней автоизации и другие данные. В языке SQL есть множество функций связанных с датой и временем, сегодня их и рассмотрим.

Все ниже рассмотренные функции работают с календарными типами данных.

Получение текущей даты и времени.

Чтобы получить текущую дату и время используется функция NOW ().

SELECT NOW ()
Результат: 2015-09-25 14:42:53

Для получения только текущей даты есть функция CURDATE ().

SELECT CURDATE ()
Результат: 2015-09-25

И функция CURTIME (), которая возвращает только текущее время:

SELECT CURTIME ()
Результат: 14:42:53

Функции CURDATE () и NOW () удобно использовать для добавления в базу данных записей, для которых требуется хранить дату добавления. Например, при добавлении статьи на сайт хорошо бы хранить ее дату публикации. Тогда запрос на добавление статьи в базу будет примерно таким:

INSERT INTO posts (id_post, text_post, date_publication) VALUES (1, 'текст статьи', NOW ());

Прибавление и вычитание дат и времени

Функция ADDDATE (date, INTERVAL value) прибавляет к дате date значение value и возвращает полученное значение. В качестве value могут выступать следующие значения:

• SECOND — секунды
• MINUTE — минуты
• HOUR — часы
• DAY — дни
• WEEK — недели
• MONTH — месяцы
• QUARTER — кварталы
• YEAR — годы

а также их комбинации:

• MINUTE_SECOND — минуты и секунды
• HOUR_SECONDчасы — минуты и секунды
• HOUR_MINUTE — часы и минуты
• DAY_SECOND — дни, часы, минуты и секунды
• DAY_MINUTE — дни, часы и минуты
• DAY_HOUR — дни и часы
• YEAR_MONTH — года и месяцы.

SELECT ADDDATE ('2015-09-28 10:30:20', INTERVAL 1 DAY)
Результат: 2015-09-29 10:30:20

SELECT ADDDATE ('2015-09-28 10:30:20', INTERVAL '3 1:20' DAY_MINUTE)
Результат: 2015-10-01 11:50:20

Функция SUBDATE (date, INTERVAL value) производит вычитание значения value из даты date . Пример:

SELECT SUBDATE ('2015-09-28 10:30:20', INTERVAL 20 HOUR)
Результат: 2015-09-27 14:30:20

Функция PERIOD_ADD (period, n) прибавляет к значению period n месяцев. Значение период должно быть представлено в формате YYYYMM (например сентябрь 2015 года будет 201509). Пример:

SELECT PERIOD_ADD (201509, 4)
Результат: 201601

Функция TIMESTAMPADD (interval, n, date) прибавляет к дате date временной интервал n , значения которого задаются параметром interval . Возможные значения параметра interval:

• FRAC_SECOND — микросекунды
• SECOND — секунды
• MINUTE — минуты
• HOUR — часы
• DAY — дни
• WEEK — недели
• MONTH — месяцы
• QUARTER — кварталы
• YEAR — годы

SELECT TIMESTAMPADD (QUARTER, 1, '2015-09-28')
Результат: 2015-12-28

Функция SUBTIME (date, time) вычитает из даты date время time. Пример:

SELECT SUBTIME ('2015-09-28 10:30:20', '50:20:19')
Результат: 2015-09-26 08:10:01

Вычисление интервала между датами

Функция TIMEDIFF (date1, date2) вычисляет разницу в часах, минутах и секундах между двумя датами date1 и date2 . Пример:

SELECT TIMEDIFF ('2015-09-28 10:30:20', '2015-09-29 10:30:20')
Результат: -24:10:00

Функция DATEDIFF (date1, date2) вычисляет разницу в днях между двумя датами, при этом часы, минуты и секунды при указании дат игнорируются. Пример:

SELECT DATEDIFF ('2015-09-28 00:00:20', '2015-09-27 23:40:20')
Результат: 1

С помощью этой функции легко определить сколько дней прошло с даты публикации статьи:

SELECT DATEDIFF (CURDATE (), date_publication) FROM posts WHERE id_post = 1

Функция PERIOD_DIFF (period1, period2) вычисляет разницу в месяцах между двумя датами. Даты должны быть представлены в формате YYYYMM . Например, узнаем сколько месяцев прошло с января 2015 по сентябрь 2015:

SELECT PERIOD_DIFF (201509, 201501)
Результат: 9

Функция TIMESTAMPDIFF (interval, date1, date2) вычисляет разницу между датами date2 и date1 в единицах указанных в параметре interval . При этом interval может принимать следующие значения:

• FRAC_SECOND — микросекунды
• SECOND — секунды
• MINUTE — минуты
• HOUR — часы
• DAY — дни
• WEEK — недели
• MONTH — месяцы
• QUARTER — кварталы
• YEAR — годы

SELECT TIMESTAMPDIFF (HOUR, '2015-09-28 10:30:20', '2015-09-28 19:50:20')
Результат: 9

Получение различных форматов даты и времени и другой информации

Функция DATE (datetime) возвращает дату, отсекая время. Пример:

SELECT DATE ('2015-09-28 10:30:20')
Результат: 2015-09-28

Функция TIME (datetime) возвращает время, отсекая дату. Пример:

SELECT TIME ('2015-09-28 10:30:20')
Результат: 10:30:20

Функция TIMESTAMP (date) возвращает полный формат со временем даты date . Пример:

TIMESTAMP ('2015-09-28')
Результат: 2015-09-28 00:00:00

DAY (date) и DAYOFMONTH (date). Функции-синонимы, которые возвращают порядковый номер дня месяца. Пример:

SELECT DAY ('2015-09-28'), DAYOFMONTH ('2015-09-28')
Результат: 28 | 28

Функции DAYNAME (date), DAYOFWEEK (date) и WEEKDAY (date). Первая функция возвращает название дня недели, вторая — номер дня недели (отсчет от 1 — воскресенье до 7 — суббота), третья также номер дня недели только другой отсчет(отсчет от 0 — понедельник, до 6 — воскресенье). Пример:

SELECT DAYNAME ('2015-09-28'), DAYOFWEEK ('2015-09-28'), WEEKDAY ('2015-09-28')
Результат: Monday 2 | 0

Функции WEEK (date) и WEEKOFYEAR (datetime). Обе функции возвращают номер недели в году, только у первой неделя начинается с воскресенья, а у второй с понедельника. Пример:

SELECT WEEK ('2015-09-28 10:30:20'), WEEKOFYEAR ('2015-09-28 10:30:20')
Результат: 39 | 40

Функция MONTH (date) возвращает числовое значение месяца (от 1 до 12), а MONTHNAME (date) название месяца. Пример:

SELECT MONTH ('2015-09-28 10:30:20'), MONTHNAME ('2015-09-28 10:30:20')
Результат: 9 | September

Функция QUARTER (date) возвращает номер квартала года (от 1 до 4). Пример:

SELECT QUARTER ('2015-09-28 10:30:20')
Результат: 3

Функция YEAR (date) возвращает значение года (от 1000 до 9999). Пример:

SELECT YEAR ('2015-09-28 10:30:20')
Результат: 2015

Функция DAYOFYEAR (date) возвращает порядковый номер дня в году (от 1 до 366). Прмиер:

SELECT DAYOFYEAR ('2015-09-28 10:30:20')
Результат: 271

Функция HOUR (datetime) возвращает значение часа (от 0 до 23). Пример:

SELECT HOUR ('2015-09-28 10:30:20')
Результат: 10

Функция MINUTE (datetime) возвращает значение минут (от 0 до 59). Пример:

SELECT MINUTE ('2015-09-28 10:30:20')
Результат: 30

Функция SECOND (datetime) возвращает значение секунд (от 0 до 59). Пример:

SELECT SECOND ('2015-09-28 10:30:20')
Результат: 20

Функция EXTRACT (type FROM date) возвращает часть даты date определяемую параметром type . Пример:

SELECT EXTRACT (YEAR FROM '2015-09-28 10:30:20'), EXTRACT (MONTH FROM '2015-09-28 10:30:20'), EXTRACT (DAY FROM '2015-09-28 10:30:20'), EXTRACT (HOUR FROM '2015-09-28 10:30:20'), EXTRACT (MINUTE FROM '2015-09-28 10:30:20'), EXTRACT (SECOND FROM '2015-09-28 10:30:20')
Результат: 2015 | 9 | 28 | 10 | 30 | 20

Взаимообратные функции TO_DAYS (date) и FROM_DAYS (n). Первая преобразует дату в количество дней, прошедших с нулевого года. Вторая, наоборот, принимает число дней, прошедших с нулевого года и преобразует их в дату. Пример:

SELECT TO_DAYS ('2015-09-28 10:30:20'), FROM_DAYS (736234)
Результат: 736234 | 2015-09-28

Взаимообратные функции UNIX_TIMESTAMP (date) и FROM_UNIXTIME (n). Первая преобразует дату в количество секунд, прошедших с 1 января 1970 года. Вторая, наоборот, принимает число секунд, с 1 января 1970 года и преобразует их в дату. Пример:

SELECT UNIX_TIMESTAMP ('2015-09-28 10:30:20'), FROM_UNIXTIME (1443425420)
Результат: 1443425420 | 2015-09-28 10:30:20

Взаимообратные функции TIME_TO_SEC (time) и SEC_TO_TIME (n). Первая преобразует время в количество секунд, прошедших от начала суток. Вторая, наоборот, принимает число секунд с начала суток и преобразует их во время. Пример:

SELECT TIME_TO_SEC ('10:30:20'), SEC_TO_TIME (37820)
Результат: 37820 | 10:30:20

Функция MAKEDATE (year, n) принимает год year и номер дня в году n и преобразует их в дату. Пример: