Różnica miesięcy między datami w MySQL
Chcę obliczyć liczbę miesięcy pomiędzy 2 polami daty.
Czy jest lepszy sposób niż uzyskanie znacznika czasu Uniksa i podzielenie przez 2 592 000 (sekund) i zaokrąglenie w górę MySQL?
18 answers
Funkcja DATEDIFF może podać liczbę dni pomiędzy dwiema datami. Co jest bardziej dokładne, ponieważ... jak definiujesz miesiąc? (28, 29, 30, czy 31 dni?)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-11-14 01:41:52
Miesiąc-różnica pomiędzy dowolnymi dwoma datami:
Dziwię się, że jeszcze o tym nie wspomniano:
Spójrz na TIMESTAMPDIFF() funkcja w MySQL.
To, co pozwala Ci zrobić, to przekazać dwie wartości TIMESTAMP
lub DATETIME
(lub nawet DATE
, ponieważ MySQL automatycznie konwertuje), a także jednostkę czasu, na której chcesz oprzeć swoją różnicę.
Możesz określić MONTH
jako jednostkę w pierwszym parametr:
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-04')
-- Outputs: 0
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-05')
-- Outputs: 1
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-06-15')
-- Outputs: 1
SELECT TIMESTAMPDIFF(MONTH, '2012-05-05', '2012-12-16')
-- Outputs: 7
W zasadzie pobiera liczbę miesięcy, które upłynęły od pierwszej daty na liście parametrów. To rozwiązanie automatycznie kompensuje zmienną liczbę dni w każdym miesiącu (28,30,31), a także uwzględnia lata przestępne - nie musisz się o to martwić.
Różnica miesiąca z dokładnością:
Jest to trochę bardziej skomplikowane, jeśli chcesz wprowadzić precyzję dziesiętną w liczba miesięcy upłynęła, ale oto jak możesz to zrobić:
SELECT
TIMESTAMPDIFF(MONTH, startdate, enddate) +
DATEDIFF(
enddate,
startdate + INTERVAL
TIMESTAMPDIFF(MONTH, startdate, enddate)
MONTH
) /
DATEDIFF(
startdate + INTERVAL
TIMESTAMPDIFF(MONTH, startdate, enddate) + 1
MONTH,
startdate + INTERVAL
TIMESTAMPDIFF(MONTH, startdate, enddate)
MONTH
)
Gdzie startdate
i enddate
są parametrami daty, Czy to z dwóch kolumn daty w tabeli, czy jako parametry wejściowe ze skryptu:
Przykłady:
With startdate = '2012-05-05' AND enddate = '2012-05-27':
-- Outputs: 0.7097
With startdate = '2012-05-05' AND enddate = '2012-06-13':
-- Outputs: 1.2667
With startdate = '2012-02-27' AND enddate = '2012-06-02':
-- Outputs: 3.1935
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-07-30 09:18:08
PERIOD_DIFF oblicza miesiące pomiędzy dwoma datami.
Na przykład, aby obliczyć różnicę między now () a kolumną czasu w your_table:
select period_diff(date_format(now(), '%Y%m'), date_format(time, '%Y%m')) as months from your_table;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-02-19 16:26:38
Używam również PERIODDIFF . Aby uzyskać rok i miesiąc daty, używam funkcji EXTRACT :
SELECT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM time)) AS months FROM your_table;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-03-01 20:02:05
Z podręcznika MySQL:
PERIOD_DIFF (P1,P2)
Zwraca liczbę miesięcy pomiędzy okresami P1 i P2. P1 i P2 powinny być w formacie YYMM lub YYYYMM. Zauważ, że argumenty period P1 i P2 nie są wartościami daty.
Mysql> SELECT PERIOD_DIFF(200802,200703); -> 11
Więc może uda się zrobić coś takiego:
Select period_diff(concat(year(d1),if(month(d1)<10,'0',''),month(d1)), concat(year(d2),if(month(d2)<10,'0',''),month(d2))) as months from your_table;
Gdzie d1 i d2 są wyrażeniami daty.
Musiałem użyć poleceń if (), aby upewnij się, że miesiące były dwucyfrową liczbą, taką jak 02, a nie 2.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-11-14 02:03:26
Wolę ten sposób, ponieważ evryone zrozumie to wyraźnie na pierwszy rzut oka:
SELECT
12 * (YEAR(to) - YEAR(from)) + (MONTH(to) - MONTH(from)) AS months
FROM
tab;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-08-20 09:27:21
Czy jest lepszy sposób? tak. Nie używaj znaczników czasu MySQL. Poza tym, że zajmują one 36 bajtów, nie są one wcale wygodne do pracy. Polecam użycie daty Juliańskiej i sekund od północy dla wszystkich wartości daty / czasu. Można je łączyć, tworząc UnixDateTime. Jeśli jest przechowywana w DWORD (unsigned 4 Byte Integer), To daty do 2106 mogą być przechowywane w sekundach od epoc, 01/01/1970 DWORD max val = 4,294,967,295 - DWORD może pomieścić 136 lat sekund
Daty Juliańskie są bardzo miłe w pracy przy obliczaniu dat Wartości UNIXDateTime są dobre do pracy przy obliczaniu daty/czasu Nie są też dobre do oglądania, więc używam znaczników czasu, gdy potrzebuję kolumny, z którą Nie będę robił wielu obliczeń, ale chcę natychmiastowego wskazania.
Konwersja na Julian i z powrotem może być wykonana bardzo szybko w dobrym języku. Używając wskaźników mam go do około 900 Clks (jest to również konwersja z ciągu do liczba całkowita oczywiście)
Kiedy dostajesz się do poważnych aplikacji, które używają informacji o dacie/godzinie, takich jak na przykład rynki finansowe, daty Juliańskie są de facto.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-01-27 08:53:00
Zapytanie będzie takie jak:
select period_diff(date_format(now(),"%Y%m"),date_format(created,"%Y%m")) from customers where..
Podaje liczbę miesięcy kalendarzowych od utworzenia datestamp w rekordzie klienta, pozwalając MySQL dokonać wyboru miesiąca wewnętrznie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-12-14 07:07:53
Jak pokazuje wiele odpowiedzi tutaj, "właściwa" odpowiedź zależy dokładnie od tego, czego potrzebujesz. W moim przypadku, muszę zaokrąglić do najbliższej liczby całkowitej .
Rozważmy te przykłady: 1 stycznia - > 31 stycznia: jest 0 całe miesiące i prawie 1 miesiąc. 1 stycznia - > 1 lutego? To cały miesiąc i dokładnie miesiąc.Aby uzyskać liczbę całych miesięcy, użyj:
SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-01-31'); => 0
SELECT TIMESTAMPDIFF(MONTH, '2018-01-01', '2018-02-01'); => 1
Aby uzyskać zaokrąglony Czas trwania w miesiącach, można użycie:
SELECT ROUND(TIMESTAMPDIFF(DAY, '2018-01-01', '2018-01-31')*12/365.24); => 1
SELECT ROUND(TIMESTAMPDIFF(DAY, '2018-01-01', '2018-01-31')*12/365.24); => 1
To jest dokładne do + / - 5 dni i dla przedziałów powyżej 1000 lat. Odpowiedź Zane ' a jest oczywiście dokładniejsza, ale jest zbyt słowna jak na moje upodobania.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-02-22 10:04:22
DROP FUNCTION IF EXISTS `calcula_edad` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `calcula_edad`(pFecha1 date, pFecha2 date, pTipo char(1)) RETURNS int(11)
Begin
Declare vMeses int;
Declare vEdad int;
Set vMeses = period_diff( date_format( pFecha1, '%Y%m' ), date_format( pFecha2, '%Y%m' ) ) ;
/* Si el dia de la fecha1 es menor al dia de fecha2, restar 1 mes */
if day(pFecha1) < day(pFecha2) then
Set vMeses = VMeses - 1;
end if;
if pTipo='A' then
Set vEdad = vMeses div 12 ;
else
Set vEdad = vMeses ;
end if ;
Return vEdad;
End
select calcula_edad(curdate(),born_date,'M') -- for number of months between 2 dates
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-11-04 02:43:48
Wykonaj ten kod, a stworzy on funkcję datedeifference, która da Ci różnicę w formacie daty yyyy-mm-dd.
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-07-25 18:24:08
To zależy od tego, jak chcesz zdefiniować # miesięcy. Odpowiedz na to pytanie: "Jaka jest różnica w miesiącach: 15.02.2008-12.03.2009". Czy jest on zdefiniowany przez jasne cięcie # dni, które zależy od lat przestępnych - jaki to miesiąc, lub ten sam dzień poprzedniego miesiąca = 1 miesiąc.
Obliczenie dla dni :
Luty 15 - > 29 (rok przestępny) = 14 Mar 1, 2008 + 365 = Mar 1, 2009. Mar 1 - > Mar 12 = 12 dni. 14 + 365 + 12 = 391 dni. Razem = 391 dni / (avg dni w miesiącu = 30) = 13.03333
Obliczanie miesięcy :
Feb 15 2008-Feb 15 2009 = 12 Lut 15 - > Mar 12 = mniej niż 1 miesiąc Ogółem = 12 miesięcy, lub 13, jeśli Luty 15 - Marzec 12 jest uważany za "ostatni miesiąc"
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-11-14 01:53:05
SELECT *
FROM emp_salaryrevise_view
WHERE curr_year Between '2008' AND '2009'
AND MNTH Between '12' AND '1'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-07-25 18:25:46
Potrzebowałem miesiecznej różnicy z precyzją. Chociaż rozwiązanie Zane ' a Biena jest we właściwym kierunku, jego drugi i trzeci przykład daje niedokładne wyniki. Dzień w lutym podzielony przez liczbę dni w lutym nie jest równy dziennikowi w maju podzielonemu przez liczbę dni w maju. Więc drugi przykład powinien wyjść ((31-5+1)/31 + 13/30 = ) 1.3043 i trzeci przykład ((29-27+1)/29 + 2/30 + 3 = ) 3.1701.
Skończyło się na następującym zapytaniu:
SELECT
'2012-02-27' AS startdate,
'2012-06-02' AS enddate,
TIMESTAMPDIFF(DAY, (SELECT startdate), (SELECT enddate)) AS days,
IF(MONTH((SELECT startdate)) = MONTH((SELECT enddate)), 0, (TIMESTAMPDIFF(DAY, (SELECT startdate), LAST_DAY((SELECT startdate)) + INTERVAL 1 DAY)) / DAY(LAST_DAY((SELECT startdate)))) AS period1,
TIMESTAMPDIFF(MONTH, LAST_DAY((SELECT startdate)) + INTERVAL 1 DAY, LAST_DAY((SELECT enddate))) AS period2,
IF(MONTH((SELECT startdate)) = MONTH((SELECT enddate)), (SELECT days), DAY((SELECT enddate))) / DAY(LAST_DAY((SELECT enddate))) AS period3,
(SELECT period1) + (SELECT period2) + (SELECT period3) AS months
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-25 01:41:54
Możesz otrzymać lata, miesiące i dni w ten sposób:
SELECT
username
,date_of_birth
,DATE_FORMAT(CURDATE(), '%Y') - DATE_FORMAT(date_of_birth, '%Y') - (DATE_FORMAT(CURDATE(), '00-%m-%d') < DATE_FORMAT(date_of_birth, '00-%m-%d')) AS years
,PERIOD_DIFF( DATE_FORMAT(CURDATE(), '%Y%m') , DATE_FORMAT(date_of_birth, '%Y%m') ) AS months
,DATEDIFF(CURDATE(),date_of_birth) AS days
FROM users
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-06-06 12:44:32
Możesz również spróbować tego:
select MONTH(NOW())-MONTH(table_date) as 'Total Month Difference' from table_name;
Lub
select MONTH(Newer_date)-MONTH(Older_date) as 'Total Month Difference' from table_Name;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-12-14 16:59:15
Funkcja PERIOD_DIFF ()
Jednym ze sposobów jest MySQL PERIOD_DIFF () zwraca różnicę między dwoma okresami. Okresy powinny być w tym samym formacie, tj. YYYYMM lub YYMM. Należy zauważyć, że okresy nie są wartościami daty.
Kod:
SELECT PERIOD_DIFF(200905,200811);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-08-31 09:43:32
To zapytanie zadziałało dla mnie:)
SELECT * FROM tbl_purchase_receipt
WHERE purchase_date BETWEEN '2008-09-09' AND '2009-09-09'
Po prostu zajmuje dwie daty i pobiera wartości między nimi.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-05-09 17:25:36