postgresql 动态（基于列）间隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5266758/
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
Dynamic (Column Based) Interval
提问by explodes
How do I add a dynamic (column based) number of days to NOW?
如何向 NOW 添加动态（基于列的）天数？
SELECT NOW() + INTERVAL a.number_of_days "DAYS" AS "The Future Date"
FROM a;
Where a.number_of_daysis an integer?
a.number_of_days整数在哪里？
回答by araqnid
I usually multiply the number by interval '1 day'or similar, e.g.:
我通常将数字乘以interval '1 day'或类似，例如：
select now() + interval '1 day' * a.number_of_days from a;
回答by Paul S
I know this is a year old, but if you need to use a column to specify the actual interval (e.g. 'days', 'months', then it is worth knowing that you can also CAST your string to an Interval, giving:
我知道这是一岁，但是如果您需要使用列来指定实际间隔（例如“天”、“月”，那么值得知道您还可以将字符串转换为间隔，给出：
SELECT now()+ CAST(the_duration||' '||the_interval AS Interval)
So the the original question would become:
所以原来的问题会变成：
SELECT now() + CAST(a.number_of_days||" DAYS" AS Interval) as "The Future Date" FROM a;
回答by Süniúr
I prefer this way. I think its pretty easy and clean.
In postgre you need intervalto use +operator with timestamp
我更喜欢这种方式。我认为它很容易和干净。在 postgre 中，您需要interval使用+运算符timestamp
select (3||' seconds')::interval;
select now()+ (10||' seconds')::interval,now();
where you can use seconds,minutes...days,months... and you can replace the numbers to your column.
您可以在其中使用秒、分钟...天、月...并且您可以将数字替换到您的列中。
select now()+ (column_name||' seconds')::interval,now()
from your_table;
回答by Igor Manturov Jr.
To creating intervals those based on column values, I recommend to add two columns in your table. For example, column "period_value"::INT4 and column "period_name"::VARCHAR. Column "period_name" can store the following values:
要创建基于列值的间隔，我建议在表中添加两列。例如，列“period_value”::INT4 和列“period_name”::VARCHAR。列“period_name”可以存储以下值：
- microsecond
- milliseconds
- second
- minute
- hour
- day
- week
- month
- quarter
- year
- decade
- century
- millennium
- 微秒
- 毫秒
- 第二
- 分钟
- 小时
- 日
- 星期
- 月
- 四分之一
- 年
- 十年
- 世纪
- 千年
+--------------+-------------+ | period_value | period_name | +--------------+-------------+ | 2 | minute | +--------------+-------------+
Now you can write:
现在你可以写：
SELECT NOW() - (period_value::TEXT || ' ' || period_name::TEXT)::INTERVAL FROM table;
回答by Mohammad Yusuf
If we have field with interval string value such as '41 years 11 mons 4 days' and want to convert it to date of birth use this query :
如果我们有间隔字符串值的字段，例如 '41 years 11 mons 4 days' 并希望将其转换为出生日期，请使用以下查询：
UPDATE "february14" set dob = date '2014/02/01' - (patient_age::INTERVAL)
dobis date field to convert '41 years 11 mons 4 days'to '1972/10/14'for example
patient_ageis varchar field that have string like '41 years 11 mons 4 days'
dob是将“ 41 年 11 月 4 天”转换 为 “1972/10/14”的日期字段，例如，
patient_age是 varchar 字段，其字符串类似于“41 年 11 月 4 天”
And this is query to convert age back to date of birth
这是将年龄转换回出生日期的查询
SELECT now() - INTERVAL '41 years 10 mons 10 days';
