PostgreSQL-截断年龄函数中的日期 [英] PostgreSQL - Truncating A Date Within Age Function
问题描述
我有以下查询:
SELECT usersid as user,
(age(creationtime)) as account_days
FROM users
输出用户(整数)和使用时间戳输出的account_days年龄(即58天,即2:31:31.387746)。
which outputs users (an integer) and account_days which uses the timestamp to output the age (i.e. 58 days, 2:31:31.387746).
我希望输出结果只说58天,而不是58天,即2:31:31.387746。我将如何处理?我尝试
I want the output to just say 58, instead of 58 days, 2:31:31.387746. How would I go about this? I attempted
SELECT usersid as user,
EXTRACT(day FROM(age(creationtime))) as account_days
FROM users
,但是它将输出更改为不匹配的数字。
but it changes the output to a number that doesn't tie out.
我在x86_64-unknown-linux-gnu上使用PostgreSQL 9.2.4版本,由gcc(Ubuntu / Linaro 4.6.3-1ubuntu5)编译4.6.3,64位
Im using version PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
推荐答案
日期减法
您可以通过强制转换获得天数将两个操作数都计算为 date
然后减去,而不是使用 age
函数返回一个合理间隔:
Date subtraction
You can get the number of days by casting both operands to date
then subtracting, instead of using the age
function that returns a justified interval:
SELECT current_date - creationtime::date
FROM ...
WHERE ...
部分天数将被四舍五入,并返回整数天数。
Partial days will be rounded down and the integer number of days returned.
例如:
select current_date - (current_timestamp - INTERVAL '1 month 28 days, 2:31:31.387746')::date;
to_char
的 DDD
格式说明符
或者您可以使用格式说明符 DDD
表示 to_char
,并带有 FM
格式修饰符以删除前导零:
to_char
's DDD
format specifier
Alternately you can use the format specifier DDD
for to_char
, with the FM
format modifier to remove the leading zeroes:
select to_char(age( current_timestamp - INTERVAL '1 month 28 days, 2:31:31.387746'), 'FMDDD');
这仍然可以使用1年以上:
This will still work for > 1 year:
regress=> select to_char(age( current_timestamp - INTERVAL '8 year 1 month 28 days, 2:31:31.387746'), 'FMDDD');
to_char
---------
2936
(1 row)
这篇关于PostgreSQL-截断年龄函数中的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!