postgresql 两个日期之间的月份函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14251456/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Months between two dates function
提问by jobi88
In oracle i can find out no:of months between using MONTHS_BETWEEN function.
在 oracle 中，我可以找出使用 MONTHS_BETWEEN 函数之间的月数。
In postgres i am using extract function for this. eg.like
在 postgres 中，我为此使用了提取功能。例如.like
select
extract(year from age(current_date, '2012-12-09')) * 12
+
extract(month from age(current_date, '2012-12-09'))
Is there any other ways(built in functions) in postgres??
postgres 中还有其他方法（内置函数）吗？
回答by araqnid
This is easy to re-implement in PostgreSQL just using SQL functions to tidy up what you've already got:
这很容易在 PostgreSQL 中重新实现，只需使用 SQL 函数来整理您已经拥有的内容：
create function months_of(interval)
returns int strict immutable language sql as $$
select extract(years from )::int * 12 + extract(month from )::int
$$;
create function months_between(date, date)
returns int strict immutable language sql as $$
select abs(months_of(age(, )))
$$;
And now select months_between('1978-06-20', '2011-12-09')produces 401.
现在select months_between('1978-06-20', '2011-12-09')生产 401。
回答by Bohemian
Unfortunately it seems not, because extract(month ...)returns the number of months modulo 12.
不幸的是，它似乎不是，因为extract(month ...)返回模 12的月数。
There is one small simplification you can make; remove the first parameter of age()- the default is age from current_date, so these two are equivalent:
您可以进行一个小的简化；删除第一个参数age()- 默认是 age from current_date，所以这两个是等价的：
age(current_date, '2012-12-09')
age('2012-12-09')
回答by Tomas Greif
You can use UDF, e.g. I've found the following here:
您可以使用 UDF，例如我在这里找到了以下内容：
CREATE OR REPLACE FUNCTION DateDiff (units VARCHAR(30), start_t TIMESTAMP, end_t TIMESTAMP)
RETURNS INT AS $$
DECLARE
diff_interval INTERVAL;
diff INT = 0;
years_diff INT = 0;
BEGIN
IF units IN ('yy', 'yyyy', 'year', 'mm', 'm', 'month') THEN
years_diff = DATE_PART('year', end_t) - DATE_PART('year', start_t);
IF units IN ('yy', 'yyyy', 'year') THEN
-- SQL Server does not count full years passed (only difference between year parts)
RETURN years_diff;
ELSE
-- If end month is less than start month it will subtracted
RETURN years_diff * 12 + (DATE_PART('month', end_t) - DATE_PART('month', start_t));
END IF;
END IF;
-- Minus operator returns interval 'DDD days HH:MI:SS'
diff_interval = end_t - start_t;
diff = diff + DATE_PART('day', diff_interval);
IF units IN ('wk', 'ww', 'week') THEN
diff = diff/7;
RETURN diff;
END IF;
IF units IN ('dd', 'd', 'day') THEN
RETURN diff;
END IF;
diff = diff * 24 + DATE_PART('hour', diff_interval);
IF units IN ('hh', 'hour') THEN
RETURN diff;
END IF;
diff = diff * 60 + DATE_PART('minute', diff_interval);
IF units IN ('mi', 'n', 'minute') THEN
RETURN diff;
END IF;
diff = diff * 60 + DATE_PART('second', diff_interval);
RETURN diff;
END;
$$ LANGUAGE plpgsql;
回答by atiruz
SELECT date_part ('year', f) * 12
+ date_part ('month', f)
FROM age (CURRENT_DATE, '2014-12-01') f
