semanas - timestampdiff mysql

La diferencia en meses entre fechas en MySQL

Question

Estoy buscando calcular el número de meses entre 2 campos de fecha y hora.

¿Hay una mejor manera que obtener la marca de tiempo de Unix y la división por 2 592 000 (segundos) y redondear en MySQL?

Answer 1

Diferencia de mes entre dos fechas dadas:

Me sorprende que aún no se haya mencionado:

Eche un vistazo a la función TIMESTAMPDIFF() en MySQL.

Lo que esto le permite hacer es pasar dos valores TIMESTAMP o DATETIME (o hasta DATE ya que MySQL se convertirá automáticamente), así como la unidad de tiempo en la que desea basar su diferencia.

Puede especificar MONTH como la unidad en el primer parámetro:

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

Básicamente, obtiene la cantidad de meses transcurridos desde la primera fecha en la lista de parámetros. Esta solución compensa automáticamente la cantidad variable de días en cada mes (28,30,31), además de tener en cuenta los años bisiestos: no tiene que preocuparse por nada de eso.

Mes-diferencia con precisión:

Es un poco más complicado si desea introducir una precisión decimal en la cantidad de meses transcurridos, pero esta es la forma de hacerlo:

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
)

Donde startdate y enddate son sus parámetros de fecha, ya sea desde dos columnas de fecha en una tabla o como parámetros de entrada desde una secuencia de comandos:

Ejemplos:

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

Answer 2

¿Hay una mejor manera? sí. No use las marcas de tiempo de MySQL. Además del hecho de que ocupan 36 Bytes, no es conveniente trabajar con ellos. Recomendaría usar Julian Date y Seconds desde la medianoche para todos los valores de fecha / hora. Estos se pueden combinar para formar un UnixDateTime. Si se almacena en un DWORD (entero de 4 bytes sin signo), las fechas hasta 2106 se pueden almacenar como segundos desde epoc, 01/01/1970 DWORD max val = 4,294,967,295 - DWORD puede contener 136 años de segundos

Es muy útil trabajar con fechas julianas cuando se hacen cálculos de fechas. Los valores UNIXDateTime son buenos para trabajar cuando se hacen cálculos de fecha / hora. Tampoco son buenos de observar, así que uso Timestamps cuando necesito una columna que no haré mucho cálculo. con, pero quiero una indicación de un vistazo.

La conversión a Julian y viceversa se puede hacer muy rápido en un buen idioma. Utilizando punteros lo tengo a unos 900 clics (Esta es también una conversión de un STRING a un INTEGER por supuesto)

Cuando entra en aplicaciones serias que usan información de fecha / hora como, por ejemplo, los mercados financieros, las fechas julianas son de facto.

Answer 3

Como muchas de las respuestas aquí muestran, la respuesta "correcta" depende exactamente de lo que necesita. En mi caso, necesito redondear al número entero más cercano .

Considere estos ejemplos: 1 de enero -> 31 de enero: Son 0 meses completos y casi 1 mes de duración. 1 de enero -> 1 de febrero? Es 1 mes completo y exactamente 1 mes de duración.

Para obtener el número de meses completos , use:

SELECT TIMESTAMPDIFF(MONTH, ''2018-01-01'', ''2018-01-31''); => 0
SELECT TIMESTAMPDIFF(MONTH, ''2018-01-01'', ''2018-02-01''); => 1

Para obtener una duración redondeada en meses, puede usar:

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

Esto tiene una precisión de +/- 5 días y para intervalos de más de 1000 años. La respuesta de Zane es obviamente más precisa, pero es demasiado detallada para mi gusto.

Answer 4

Del manual de MySQL:

PERIOD_DIFF (P1, P2)

Devuelve la cantidad de meses entre los períodos P1 y P2. P1 y P2 deben tener el formato AAIM o AAAAMM. Tenga en cuenta que los argumentos de período P1 y P2 no son valores de fecha.

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

Entonces puede ser posible hacer algo como esto:

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;

Donde d1 y d2 son las expresiones de fecha.

Tuve que usar las declaraciones if () para asegurarme de que los meses fueran un número de dos dígitos como 02 en lugar de 2.

Answer 5

Ejecute este código y creará una función fechade diferencia que le dará la diferencia en el formato de fecha aaaa-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;

Answer 6

Esta consulta funcionó para mí :)

SELECT * FROM tbl_purchase_receipt
WHERE purchase_date BETWEEN ''2008-09-09'' AND ''2009-09-09''

Simplemente toma dos fechas y recupera los valores entre ellas.

Answer 7

Esto depende de cómo quiera que se defina el número de meses. Responda las siguientes preguntas: ''Cuál es la diferencia en meses: 15 de febrero de 2008 - 12 de marzo de 2009''. ¿Está definido por un corte claro # de días que depende de los años bisiestos, qué mes es o el mismo día del mes anterior = 1 mes?

Un cálculo para Días :

15 de febrero -> 29 (año bisiesto) = 14 de marzo de 2008 + 365 = 1 de marzo de 2009. Mar 1 -> 12 de marzo = 12 días. 14 + 365 + 12 = 391 días. Total = 391 días / (promedio de días en el mes = 30) = 13.03333

Un cálculo de meses :

15 feb 2008 - 15 feb 2009 = 12 feb 15 -> 12 mar. = Menos de 1 mes Total = 12 meses, o 13 si feb 15 - mar 12 se considera "el mes pasado"

Answer 8

La consulta será como:

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

Da un número de meses calendario desde la marca de fecha creada en un registro de cliente, permitiendo que MySQL haga la selección de mes internamente.

Answer 9

La función DATEDIFF puede darle el número de días entre dos fechas. Lo cual es más preciso, ya que ... ¿cómo se define un mes? (28, 29, 30 o 31 días?)

Answer 10

Necesitaba una diferencia de mes con precisión. Aunque la solución de Zane Bien va en la dirección correcta, sus ejemplos segundo y tercero dan resultados inexactos. Un día en febrero dividido por el número de días en febrero no es igual a un día en mayo dividido por el número de días en mayo. Entonces, el segundo ejemplo debería dar salida ((31-5 + 1) / 31 + 13/30 =) 1.3043 y el tercer ejemplo ((29-27 + 1) / 29 + 2/30 + 3 =) 3.1701.

Terminé con la siguiente consulta:

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

Answer 11

Prefiero esto, porque evryone lo entenderá claramente a primera vista:

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

Answer 12

Puede obtener años, meses y días de esta manera:

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

Answer 13

También puedes probar esto:

select MONTH(NOW())-MONTH(table_date) as ''Total Month Difference'' from table_name;

O

select MONTH(Newer_date)-MONTH(Older_date) as ''Total Month Difference'' from table_Name;

Answer 14

Yo también uso PERIODDIFF . Para obtener el año y el mes de la fecha, uso la función EXTRACT :

 SELECT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM time)) AS months FROM your_table;

Answer 15

PERIODDIFF calcula los meses entre dos fechas.

Por ejemplo, para calcular la diferencia entre now () y una columna de tiempo en your_table:

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

Answer 16

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

Answer 17

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