本文介绍了如何确定间隔的最大分辨率?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何确定INTERVAL值的最大分辨率?例如:
How can I determine the largest resolution of an INTERVAL value? For example:
-
INTERVAL'100天3秒'
=>天 -
时间'20:05'-时间'12:01:01'
=>小时 -
AGE(NOW(),NOW()-间隔'1 MONTH')
=>月
INTERVAL '100 days and 3 seconds'
=> dayTIME '20:05' - TIME '12:01:01'
=> hourAGE(NOW(), NOW() - INTERVAL '1 MONTH')
=> month
推荐答案
这个问题不是100%清楚的,因此答案可能与您正在寻找的完全不同,但是...
The question isn't 100% clear so the answer may or may not be exactly what you're looking for, but...
有一个函数,您可能想研究一下。
There is a justify_interval()
function, which you might want to look into.
test=# select justify_interval(INTERVAL '100 days 3 seconds');
justify_interval
-------------------------
3 mons 10 days 00:00:03
(1 row)
test=# select justify_interval(TIME '20:05' - TIME '12:01:01');
justify_interval
------------------
08:03:59
(1 row)
test=# select justify_interval(AGE(NOW(), NOW() - INTERVAL '1 MONTH'));
justify_interval
------------------
1 mon
(1 row)
在那里提取年,月,日等,直到得出非零答案:
For there extract the year, then month, then day, etc. until you come up with a non-zero answer:
test=# select extract('mon' from interval '3 mons 10 days 00:00:03');
date_part
-----------
3
在评论中再次提出您的其他问题:
Re your other question in comments:
create function max_res(interval) returns interval as $$
select case
when extract('year' from justify_interval($1)) > 0 or
extract('mon' from justify_interval($1)) > 0 or
extract('day' from justify_interval($1)) > 0
then '1 day'
when extract('hour' from justify_interval($1)) > 0
then '1 hour'
when ...
end;
$$ language sql immutable strict;
这篇关于如何确定间隔的最大分辨率?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!