有没有一种可靠的方法可以从postgres间隔中提取年份? [英] Is there a reliable way to extract years from a postgres interval?
问题描述
在Postgres中,如果我执行以下操作:
从my_table
中选择(now()-created_at)
我得到这样的结果:
854天12:04:50.29658
但是,如果我这样做:
从my_table
来选择age(now(),created_at)
我得到这样的结果:
2年4星期一3天12:04 :50.29658
根据 pg_typeof(...)
它们都是类型 interval
但是如果我尝试提取年份:
从my_table
$ p中选择提取(年龄(now(now,),created_at)年) $ p>
我得到:
2
其中,
select从my_table
中提取(now()的年数)我得到:
0
是否有一致的方法从区间值中提取年数(无论
注意:我没有对该数据库的写权限,因此无法定义存储过程等。需要成为一个select语句。
------ 更新 ------
justify_interval(...)
的建议如下,但不幸的是,它的计算似乎不准确。
例如:
选择年龄('2018-01-03':: timestamp,'2016-01-05':: timestamp );
给出正确的答案:
1年11个月29天
位置:
选择justify_interval('2018-01-03':: timestamp-'2016-01-05':: timestamp);
给予:
2年9天
我相信这是因为(错误地)假定所有个月内有30天
(请参见
justify_days
此处: https://www.postgresql.org/docs/current/static/functions-datetime.html )解决方案函数
justify_interval
可以满足您的要求。将它与EXTRACT
结合使用可获得年份:SELECT EXTRACT (从
年开始justify_interval(INTERVAL'1 year 900 days 700 hours'));
date_part
-----------
3
(1行)
如果30天= 1个月对您来说不够准确,则必须使用
EXTRACT
来获取数字天数,然后除以365.25。
有一个理论上的限制,即精确度是多少,因为间隔中的年限多少取决于两个日期之间的 该间隔是。
由两个元素组成的
age
函数可以得出精确的结果。两个日期之间的年。In Postgres, if I do the following:
select (now() - created_at) from my_table
I get results like this:
854 days 12:04:50.29658
Whereas, if I do:
select age(now(), created_at) fro my_table
I get results like this:
2 years 4 mons 3 days 12:04:50.29658
According to
pg_typeof(...)
they are both of typeinterval
But if I try to extract the years:
select extract(years from age(now(), created_at)) from my_table
I get:
2
Whereas, with:
select extract(years from (now() - created_at)) from my_table
I get:
0
Is there a consistent way to extract the number of years from an interval value (no matter how it was generated)?
Note: I don't have write access to the db, so can't define stored procedures, etc. Needs to be a select statement.
------ UPDATE ------
justify_interval(...)
was suggested below, but unfortunately it seems to be inaccurate in its calculations.E.g:
select age('2018-01-03'::timestamp, '2016-01-05'::timestamp);
gives the correct answer:
1 year 11 mons 29 days
Whereas:
select justify_interval('2018-01-03'::timestamp - '2016-01-05'::timestamp);
gives:
2 years 9 days
I believe this is because it (incorrectly) assumes that all months have 30 days in them
(see
justify_days
here: https://www.postgresql.org/docs/current/static/functions-datetime.html)解决方案The function
justify_interval
does what you want. Use it in combination withEXTRACT
to get the years:SELECT EXTRACT(years FROM justify_interval(INTERVAL '1 year 900 days 700 hours')); date_part ----------- 3 (1 row)
If 30 days = 1 month isn't accurate enough for you, you'll have to use
EXTRACT
to get the number of days and divide by 365.25.There is a theoretical limit how exact you can be, because the number of years in an interval somewhat depends on between which dates that interval is.
The two-element
age
function gives a precise result for the number of years between two dates.这篇关于有没有一种可靠的方法可以从postgres间隔中提取年份?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!