当前位置： 文江博客话题详情

如何使用 MySQL 查询获取两个日期之间的月差？

发布于 2024-07-09 19:35:33 字数 88 浏览 16 评论 0原文

我正在计算两个日期时间字段之间的月数。

有没有比获取 Unix 时间戳然后除以 2 592 000（秒）并在 MySQL 中向上舍入更好的方法？

原文

I'm looking to calculate the number of months between 2 date time fields.

Is there a better way than getting the Unix timestamp and then dividing by 2 592 000 (seconds) and rounding up within MySQL?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（21）

往昔成烟 2024-07-16 19:35:33

任何给定两个日期之间的月差：

我很惊讶这一点还没有被提及：

看看 TIMESTAMPDIFF() MySQL 中的函数。

这允许您传入两个 TIMESTAMP 或 DATETIME 值（甚至是 DATE，因为 MySQL 会自动转换）以及您想要作为差异基础的时间单位。

您可以在第一个参数中指定MONTH作为单位：

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

它基本上获取从参数列表中的第一个日期开始经过的月数。 该解决方案会自动补偿每个月不同的天数（28、30、31），并考虑闰年 - 您不必担心任何这些问题。

精确的月差：

如果您想在经过的月份数中引入小数精度，那就有点复杂了，但可以这样做：

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
  )

其中 startdate 和 enddate 是您的日期参数，无论是来自表中的两个日期列还是来自脚本的输入参数：

示例：

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

Month-difference between any given two dates:

I'm surprised this hasn't been mentioned yet:

Have a look at the TIMESTAMPDIFF() function in MySQL.

What this allows you to do is pass in two TIMESTAMP or DATETIME values (or even DATE as MySQL will auto-convert) as well as the unit of time you want to base your difference on.

You can specify MONTH as the unit in the first parameter:

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

It basically gets the number of months elapsed from the first date in the parameter list. This solution automatically compensates for the varying amount of days in each month (28,30,31) as well as taking into account leap years — you don't have to worry about any of that stuff.

Month-difference with precision:

It's a little more complicated if you want to introduce decimal precision in the number of months elapsed, but here is how you can do it:

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
  )

Where startdate and enddate are your date parameters, whether it be from two date columns in a table or as input parameters from a script:

Examples:

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
回复 原文
随心而道 2024-07-16 19:35:33

PERIOD_DIFF 计算月份两个日期之间。

例如，要计算 now() 与 your_table 中的时间列之间的差异：

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

PERIOD_DIFF calculates months between two dates.

For example, to calculate the difference between now() and a time column in your_table:

select period_diff(date_format(now(), '%Y%m'), date_format(time, '%Y%m')) as months from your_table;
回复 原文
平生欢 2024-07-16 19:35:33

我还使用 PERIOD_DIFF。 为了获取日期的年份和月份，我使用函数 摘录：

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

I use also PERIOD_DIFF. To get the year and the month of the date, I use the function EXTRACT:

  SELECT PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM time)) AS months FROM your_table;
回复 原文
淤浪 2024-07-16 19:35:33

DATEDIFF 函数可以给出您两个日期之间的天数。 哪个更准确，因为……你如何定义一个月？ （28、29、30 还是 31 天？）

The DATEDIFF function can give you the number of days between two dates. Which is more accurate, since... how do you define a month? (28, 29, 30, or 31 days?)

回复 原文
冷情妓 2024-07-16 19:35:33

正如这里的许多答案所示，“正确”的答案取决于您的需求。 就我而言，我需要四舍五入到最接近的整数。

考虑这些例子：
1 月 1 日 -> 1月31日：整整0个月，差不多1个月了。
1 月 1 日 -> 2月1日？ 整整1个月，正好1个月长。

要获取整个（完整）月数，请使用：

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

要获取以月为单位的四舍五入持续时间，您可以使用：

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

这精确到 +/- 5 天，并且适用于范围超过1000年。 赞恩的回答显然更准确，但对我来说太冗长了。

As many of the answers here show, the 'right' answer depends on exactly what you need. In my case, I need to round to the closest whole number.

Consider these examples:
1st January -> 31st January: It's 0 whole months, and almost 1 month long.
1st January -> 1st February? It's 1 whole month, and exactly 1 month long.

To get the number of whole (complete) months, use:

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

To get a rounded duration in months, you could use:

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

This is accurate to +/- 5 days and for ranges over 1000 years. Zane's answer is obviously more accurate, but it's too verbose for my liking.

回复 原文
以可爱出名 2024-07-16 19:35:33

我更喜欢这种方式，因为大家一看就明白：

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

I prefer this way, because evryone will understand it clearly at the first glance:

SELECT
    12 * (YEAR(to) - YEAR(from)) + (MONTH(to) - MONTH(from)) AS months
FROM
    tab;
回复 原文
◇流星雨 2024-07-16 19:35:33

来自 MySQL 手册：

PERIOD_DIFF(P1,P2)

返回期间 P1 和 P2 之间的月数。 P1 和 P2 的格式应为 YYMM 或 YYYYMM。 请注意，句点参数 P1 和 P2 不是日期值。

mysql> 选择 PERIOD_DIFF(200802,200703);
-> 11

因此，可以执行如下操作：

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;

其中 d1 和 d2 是日期表达式。

我必须使用 if() 语句来确保月份是两位数，例如 02 而不是 2。

From the MySQL manual:

PERIOD_DIFF(P1,P2)

Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

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

So it may be possible to do something like this:

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;

Where d1 and d2 are the date expressions.

I had to use the if() statements to make sure that the months was a two digit number like 02 rather than 2.

回复 原文
才能让你更想念 2024-07-16 19:35:33

有没有更好的办法？
是的。 不要使用 MySQL 时间戳。 除了占用 36 字节之外，它们使用起来一点也不方便。
我建议对所有日期/时间值使用朱利安日期和午夜开始的秒数。
这些可以组合起来形成 UnixDateTime。 如果将其存储在 DWORD（无符号 4 字节整数）中，则一直到 2106 的日期都可以存储为自 epoc, 01/01/1970 以来的秒数
DWORD max val = 4,294,967,295 - DWORD 可以保存 136 年的秒数

在进行日期计算时，儒略日期非常适合使用
进行日期/时间计算时，UNIXDateTime 值非常适合使用
两者都不好看，所以当我需要一个不会进行太多计算的列，但我想要一个概览指示时，我会使用时间戳。

使用良好的语言可以非常快速地完成与 Julian 的相互转换。 使用指针，我将其减少到大约 900 个时钟（当然，这也是从字符串到整数的转换）

当您进入使用日期/时间信息的严肃应用程序（例如金融市场）时，儒略日期实际上是。

Is there a better way?
yes. Do not use MySQL Timestamps. Apart from the fact that they occupy 36 Bytes, they are not at all convenient to work with.
I would reccomend using Julian Date and Seconds from midnight for all date/time values.
These can be combined to form a UnixDateTime. If this is stored in a DWORD (unsigned 4 Byte Integer) then dates all the way up to 2106 can be stored as seconds since epoc, 01/01/1970
DWORD max val = 4,294,967,295 - A DWORD can hold 136 years of Seconds

Julian Dates are very nice to work with when making date calculations
UNIXDateTime values are good to work with when making Date/Time calculations
Neither are good to look at, so I use the Timestamps when I need a column that I will not be doing much calculation with, but I want an at-a-glance indication.

Converting to Julian and back can be done very quickly in a good language. Using pointers I have it down to about 900 Clks (This is also a conversion from a STRING to an INTEGER of course)

When you get into serious applications that use Date/Time information like for example the financial markets, Julian dates are de-facto.

回复 原文
痴意少年 2024-07-16 19:35:33

查询将类似于：

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

给出自在客户记录上创建日期戳以来的多个日历月，让 MySQL 在内部进行月份选择。

The Query will be like:

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

Gives a number of calendar months since the created datestamp on a customer record, letting MySQL do the month selection internally.

回复 原文
能怎样 2024-07-16 19:35:33
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

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
回复 原文
分分钟 2024-07-16 19:35:33

执行此代码，它将创建一个函数 datedeifference，它将为您提供日期格式 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;

Execute this code and it will create a function datedeifference which will give you the difference in date format 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;
回复 原文
会发光的星星闪亮亮i 2024-07-16 19:35:33

这取决于您希望如何定义月份数。 回答以下问题：“月份有何不同：2008 年 2 月 15 日 - 2009 年 3 月 12 日”。 它是由明确的天数定义的，这取决于闰年 - 是哪一个月，还是上个月的同一天 = 1 个月。

天数计算：

2 月 15 日 -> 29（闰年）= 14
2008 年 3 月 1 日 + 365 = 2009 年 3 月 1 日。
3月1日-> 3 月 12 日 = 12 天。
14 + 365 + 12 = 391 天。
总计 = 391 天 /（每月平均天数 = 30）= 13.03333

月份计算：

2008 年 2 月 15 日 - 2009 年 2 月 15 日 = 12
2月15日-> 3 月 12 日 = 不到 1 个月
总计 = 12 个月，如果 2 月 15 日 - 3 月 12 日被视为“过去一个月”，则为 13 个月

This depends on how you want the # of months to be defined. Answer this questions: 'What is difference in months: Feb 15, 2008 - Mar 12, 2009'. Is it defined by clear cut # of days which depends on leap years- what month it is, or same day of previous month = 1 month.

A calculation for Days:

Feb 15 -> 29 (leap year) = 14
Mar 1, 2008 + 365 = Mar 1, 2009.
Mar 1 -> Mar 12 = 12 days.
14 + 365 + 12 = 391 days.
Total = 391 days / (avg days in month = 30) = 13.03333

A calculation of months:

Feb 15 2008 - Feb 15 2009 = 12
Feb 15 -> Mar 12 = less than 1 month
Total = 12 months, or 13 if feb 15 - mar 12 is considered 'the past month'

回复 原文
凉风有信 2024-07-16 19:35:33
SELECT * 
FROM emp_salaryrevise_view 
WHERE curr_year Between '2008' AND '2009' 
    AND MNTH Between '12' AND '1'

SELECT * 
FROM emp_salaryrevise_view 
WHERE curr_year Between '2008' AND '2009' 
    AND MNTH Between '12' AND '1'
回复 原文
裸钻 2024-07-16 19:35:33

我需要精确的月差。 尽管 Zane Bien 的解决方案方向正确，但他的第二个和第三个示例给出的结果不准确。 二月的一天除以二月的天数不等于五月的一天除以五月的天数。 因此，第二个示例应输出 ((31-5+1)/31 + 13/30 = ) 1.3043，第三个示例应输出 ((29-27+1)/29 + 2/30 + 3 = ) 3.1701。

我最终得到以下查询：

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

I needed month-difference with precision. Although Zane Bien's solution is in the right direction, his second and third examples give inaccurate results. A day in February divided by the number of days in February is not equal to a day in May divided by the number of days in May. So the second example should output ((31-5+1)/31 + 13/30 = ) 1.3043 and the third example ((29-27+1)/29 + 2/30 + 3 = ) 3.1701.

I ended up with the following query:

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
回复 原文
顾铮苏瑾 2024-07-16 19:35:33

PERIOD_DIFF() 函数

其中一种方法是 MySQL PERIOD_DIFF() 返回两个周期之间的差异。 期间应采用相同的格式，即 YYYYMM 或 YYMM。 需要注意的是，句点不是日期值。

代码：

SELECT PERIOD_DIFF(200905,200811);

在此处输入图像描述

PERIOD_DIFF() function

One of the way is MySQL PERIOD_DIFF() returns the difference between two periods. Periods should be in the same format i.e. YYYYMM or YYMM. It is to be noted that periods are not date values.

Code:

SELECT PERIOD_DIFF(200905,200811);

enter image description here

回复 原文
酷遇一生 2024-07-16 19:35:33

您可以通过以下方式获取年、月和日：

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

You can get years, months and days this way:

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
回复 原文
窗影残 2024-07-16 19:35:33

您也可以尝试以下操作：

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

或

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

You can also try this:

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

OR

select MONTH(Newer_date)-MONTH(Older_date) as 'Total Month Difference' from table_Name;
回复 原文
泪之魂 2024-07-16 19:35:33

简单的答案是给定开始日期为 ins_frm 和结束日期为 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

Enjoy :)))

Simple answer given start date as ins_frm and end date as 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

Enjoy :)))

回复 原文
不必在意 2024-07-16 19:35:33

尝试这个

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

Try this

SELECT YEAR(end_date)*12 + MONTH(end_date) - (YEAR(start_date)*12 + MONTH(start_date))
回复 原文
梦在深巷 2024-07-16 19:35:33

虽然这是一个老话题，但它显示在谷歌的顶部，我没有看到与 Mysql 相关的新问题来计算几个月的差异。 我需要一个非常精确的计算，包括月份的一部分。

这是为了计算订阅费，例如每月 8 欧元。 那么二月的 1 天与其他月份相比确实有不同的价格。 所以需要计算月份的分数，这里分数的精度是基于秒的。

它的作用是在@from和@to日期之间计算时将计算分成3部分：

  1. 分数@from 和 @from 日历月结束之间的日历月的
  2. 数量 @from 和 @to 之间的整个日历月数
  3. 日历月开始和 @to 之间的日历月的分数

例如，从 '2021-09-29 12 :00:00' 到 '2021-11-07 00:00:00'：

  1. 2021 年 9 月底的 1.5 天。9 月确实有 30 天
    天，因此分数为 0.05 个月 (1.5/30)。
  2. 2021 年 10 月整月，所以 1 个整月
  3. 2021 年 11 月开始的 6 天。11 月确实有 30 天，所以派系是 0.2 个月 (6/30)。

所以结果是 1.25 个月。

set @from  = '2021-09-29 12:00:00';
set @to    = '2021-11-07 00:00:00';
select 
/* part 1 */ (unix_timestamp(last_day(@from)) + 86400 - unix_timestamp(@from)) / 86400 / day(last_day(@from))
/* part 2 */ + PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM @to), EXTRACT(YEAR_MONTH FROM @from)) - 1 +
/* part 3 */ 1 - (unix_timestamp(last_day(@to)) + 86400 - unix_timestamp(@to)) / 86400 / day(last_day(@to)) 
month_fraction;

完全相同的计算，但现在基于不使用 mysql 变量的人的字段，并且更容易接管自己的字段：

select 
/* part 1 */ (unix_timestamp(last_day(periodStart)) + 86400 - unix_timestamp(periodStart)) / 86400 / day(last_day(periodStart))
/* part 2 */ + PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM periodTill), EXTRACT(YEAR_MONTH FROM periodStart)) - 1 +
/* part 3 */ 1 - (unix_timestamp(last_day(periodTill)) + 86400 - unix_timestamp(periodTill)) / 86400 / day(last_day(periodTill))
month_fraction
from (select '2021-09-29 12:00:00' periodStart, '2021-11-07 00:00:00' periodTill) period

为了速度优化，我使用了 unix_timestamp ，它应该执行得很快，因为它能够使用数学计算。 unix_timestamp 返回一个以秒为单位的数字。 86400 是一天的秒数。

Although it's an old topic it shows on top in google and I don't see newer questions related to Mysql to calculate the difference in months. And I needed a very precise calculation including the fraction of the month.

This for the purpose to calculate a subscription fee e.g. 8 euro per month. Then 1 day in februari does have a different price compared to other months. So the fraction of months needs to be calculated and here the precision of the fraction is based on seconds.

What it does is to split the calculation into 3 parts when calculation between @from and @to dates:

  1. fraction of the calendar month between @from and the end of the @from calendar month
  2. number of whole calendar months between @from and @to
  3. fraction of the calendar month between start of the calendar month and @to

E.g from '2021-09-29 12:00:00' to '2021-11-07 00:00:00':

  1. The 1.5 days at the end of september 2021. September does have 30
    days so the fraction is 0.05 month (1.5/30).
  2. the whole month oktober 2021 so 1 full month
  3. The 6 full days at the begin of november 2021. November does have 30 days so the faction is 0.2 month (6/30).

So the outcome is 1.25 month.

set @from  = '2021-09-29 12:00:00';
set @to    = '2021-11-07 00:00:00';
select 
/* part 1 */ (unix_timestamp(last_day(@from)) + 86400 - unix_timestamp(@from)) / 86400 / day(last_day(@from))
/* part 2 */ + PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM @to), EXTRACT(YEAR_MONTH FROM @from)) - 1 +
/* part 3 */ 1 - (unix_timestamp(last_day(@to)) + 86400 - unix_timestamp(@to)) / 86400 / day(last_day(@to)) 
month_fraction;

Exactly the same calculation but now based on fields for people not using mysql variables and easier to take over your own fields:

select 
/* part 1 */ (unix_timestamp(last_day(periodStart)) + 86400 - unix_timestamp(periodStart)) / 86400 / day(last_day(periodStart))
/* part 2 */ + PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM periodTill), EXTRACT(YEAR_MONTH FROM periodStart)) - 1 +
/* part 3 */ 1 - (unix_timestamp(last_day(periodTill)) + 86400 - unix_timestamp(periodTill)) / 86400 / day(last_day(periodTill))
month_fraction
from (select '2021-09-29 12:00:00' periodStart, '2021-11-07 00:00:00' periodTill) period

For speed optimization I've used unix_timestamp which should perform fast as it is able to use mathematic calculation. The unix_timestamp returns a number in seconds. The 86400 is the number of seconds in a day.

回复 原文
吝吻 2024-07-16 19:35:33

这个查询对我有用:)

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

它只需要两个日期并检索它们之间的值。

This query worked for me:)

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

It simply take two dates and retrieves the values between them.

回复 原文
~没有更多了~

关于作者

夏了南城

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

十二

文章 0 评论 0

飞烟轻若梦

文章 0 评论 0

OPleyuhuo

文章 0 评论 0

wxb0109

文章 0 评论 0

旧城空念

文章 0 评论 0

-小熊_

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文