本文介绍了如何确定间隔的最大分辨率?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何确定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' => day
- TIME '20:05' - TIME '12:01:01' => hour
- AGE(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...
有一个 justify_interval() 函数,您可能想研究一下。
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;更多推荐
如何确定间隔的最大分辨率?
发布评论