Chcę obliczyć liczbę miesięcy między 2 polami daty i czasu.

Czy jest lepszy sposób niż pobranie uniksowego znacznika czasu i podzielenie go przez 2 592 000 (sekund) i zaokrąglenie w górę w MySQL?

Funkcja DATEDIFF może podać liczbę dni między dwiema datami. Co jest dokładniejsze, skoro ... jak definiujesz miesiąc? (28, 29, 30 czy 31 dni?)

Miesięczna różnica między dowolnymi dwiema datami:

Dziwię się, że jeszcze o tym nie wspomniano:

Przyjrzyj się funkcji TIMESTAMPDIFF () w MySQL.

Pozwala to na przekazanie dwóch TIMESTAMPlub DATETIMEwartości (lub nawet DATEw przypadku automatycznej konwersji MySQL), a także jednostki czasu, na której chcesz oprzeć swoją różnicę.

MONTHJako jednostkę możesz określić w pierwszym parametrze:

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

Zasadniczo 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ć.

Miesięczna różnica z dokładnością:

To trochę bardziej skomplikowane, jeśli chcesz wprowadzić dokładność dziesiętną w liczbie miesięcy, które upłynęły, 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 startdatei enddatesą parametry daty, czy to z dwóch kolumn dat 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

PERIOD_DIFF oblicza miesiące między dwiema datami.

Na przykład, aby obliczyć różnicę między now () a kolumną czasu w twoja_tabela:

select period_diff(date_format(now(), '%Y%m'), date_format(time, '%Y%m')) as months from your_table;

Używam też 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;

Jak pokazuje wiele odpowiedzi tutaj, „właściwa” odpowiedź zależy od dokładnie tego, czego potrzebujesz. W moim przypadku muszę zaokrąglić do najbliższej liczby całkowitej .

Rozważ następujące przykłady: 1 stycznia -> 31 stycznia: To 0 pełnych miesięcy i prawie 1 miesiąc. 1 stycznia -> 1 lutego? Trwa 1 cały miesiąc i dokładnie 1 miesiąc.

Aby uzyskać liczbę pełnych (pełnych) 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żesz użyć:

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

Jest to dokładne do +/- 5 dni i dla zakresów powyżej 1000 lat. Odpowiedź Zane'a jest oczywiście dokładniejsza, ale jak na mój gust jest zbyt szczegółowa.

Z podręcznika MySQL:

PERIOD_DIFF (P1; P2)

Zwraca liczbę miesięcy między okresami P1 i P2. P1 i P2 powinny mieć format RRMM lub RRRRMM. Należy zauważyć, że argumenty okresu P1 i P2 nie są wartościami dat.

mysql> SELECT PERIOD_DIFF (200802,200703); -> 11

Więc może być możliwe zrobienie czegoś 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 to wyrażenia dat.

Musiałem użyć instrukcji if (), aby upewnić się, że miesiące są dwucyfrową liczbą, taką jak 02, a nie 2.

Wolę ten sposób, bo każdy na pierwszy rzut oka to zrozumie:

SELECT
    12 * (YEAR(to) - YEAR(from)) + (MONTH(to) - MONTH(from)) AS months
FROM
    tab;

Czy jest lepszy sposób? tak. Nie używaj sygnatur czasowych MySQL. Poza tym, że zajmują 36 bajtów, praca z nimi wcale nie jest wygodna. Zalecałbym używanie Julian Date and Seconds od północy dla wszystkich wartości daty / godziny. Można je połączyć, aby utworzyć UnixDateTime. Jeśli jest to zapisane w DWORD (liczba całkowita bez znaku 4 bajty), wówczas daty aż do 2106 mogą być przechowywane jako sekundy od epoc, 01/01/1970 DWORD max val = 4 294 967 295 - DWORD może przechowywać 136 lat sekund

Daty juliańskie są bardzo przyjemne w pracy podczas wykonywania obliczeń dat Wartości UNIXDateTime są dobre do pracy podczas wykonywania obliczeń daty / godziny Nie są one dobre do oglądania, więc używam znaczników czasu, gdy potrzebuję kolumny, której nie będę wykonywać zbyt wielu obliczeń z, ale chcę mieć szybkie wskazanie.

Konwersję na Juliana iz powrotem można wykonać bardzo szybko w dobrym języku. Używając wskaźników, mam to do około 900 Clks (jest to oczywiście konwersja ze STRINGU na INTEGER)

Kiedy wchodzisz w poważne aplikacje, które używają informacji o dacie / godzinie, na przykład na rynkach finansowych, daty juliańskie są de facto.

Zapytanie będzie wyglądać następująco:

select period_diff(date_format(now(),"%Y%m"),date_format(created,"%Y%m")) from customers where..

Podaje liczbę miesięcy kalendarzowych od utworzenia znacznika daty w rekordzie klienta, pozwalając MySQL na wewnętrzne wybranie miesiąca.

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

Wykonaj ten kod, a utworzy on funkcję dateeifference, która poda różnicę w formacie daty rrrr-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;

Zależy to od tego, jak chcesz zdefiniować liczbę miesięcy. Odpowiedz na pytanie: „Jaka jest różnica w miesiącach: 15 lutego 2008 r. - 12 marca 2009 r.”. Czy jest to określone przez wyraźną liczbę dni, która zależy od lat przestępnych - jaki to jest miesiąc, czy ten sam dzień poprzedniego miesiąca = 1 miesiąc.

Obliczenie dla dni :

15 lutego -> 29 (rok przestępny) = 14 1 marca 2008 + 365 = 1 marca 2009. 1 marca -> 12 marca = 12 dni. 14 + 365 + 12 = 391 dni. Razem = 391 dni / (śr. Dni w miesiącu = 30) = 13,03333

Obliczenie miesięcy :

15 lutego 2008 - 15 lutego 2009 = 12 15 lutego -> 12 marca = mniej niż 1 miesiąc Suma = 12 miesięcy lub 13, jeśli 15 lutego - 12 marca jest uważany za „ostatni miesiąc”

SELECT * 
FROM emp_salaryrevise_view 
WHERE curr_year Between '2008' AND '2009' 
    AND MNTH Between '12' AND '1'

Potrzebowałem precyzyjnej różnicy miesięcznej. Chociaż rozwiązanie Zane Bien jest w dobrym kierunku, jego drugi i trzeci przykład dają niedokładne wyniki. Dzień w lutym podzielony przez liczbę dni w lutym nie jest równy dniu w maju podzielonym przez liczbę dni w maju. Zatem drugi przykład powinien wyprowadzić ((31-5 + 1) / 31 + 13/30 =) 1,3043, a 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

Funkcja PERIOD_DIFF ()

Jednym ze sposobów jest to, że MySQL PERIOD_DIFF () zwraca różnicę między dwoma okresami. Okresy powinny mieć ten sam format, tj. RRRRMM lub RRMM. Należy zauważyć, że okresy nie są wartościami dat.

Kod:

SELECT PERIOD_DIFF(200905,200811);

W ten sposób możesz otrzymać lata, miesiące i dni:

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

Możesz też 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;

Prosta odpowiedź z datą rozpoczęcia jako ins_frm i datą zakończenia jako ins_to

SELECT convert(TIMESTAMPDIFF(year, ins_frm, ins_to),UNSIGNED) as yrs,
       mod(TIMESTAMPDIFF(MONTH, ins_frm, ins_to),12) mnths
FROM table_name

Cieszyć się :)))

Spróbuj tego

SELECT YEAR(end_date)*12 + MONTH(end_date) - (YEAR(start_date)*12 + MONTH(start_date))

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 pobiera dwie daty i pobiera wartości między nimi.