PostgreSQL增量日期? [英] PostgreSQL incremental dates?
本文介绍了PostgreSQL增量日期?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含以下数据的数据库表:
I have a database table that contains the following data:
ID | Date | Bla
1 | 2013-05-01 | 1
2 | 2013-05-02 | 2
3 | 2013-05-03 | 3
4 | 2013-05-05 | 4
请注意缺少日期: 2014-05-04
。我应该如何更改以下查询:
Note that there is a date missing: 2014-05-04
. How should I alter the following query:
SELECT *
FROM table
where DATE >= '2013-05-01' AND DATE <= '2013-05-05'
最终将显示以下输出:
ID | Date | Bla
1 | 2013-05-01 | 1
2 | 2013-05-02 | 2
3 | 2013-05-03 | 3
null | 2013-05-04 | null
4 | 2013-05-05 | 4
可能吗?
推荐答案
您可以加入 generate_series
输出:
select
'2013-05-01'::date + g.o AS "date with offset"
from
generate_series(0, 30) AS g(o)
输出:
"2013-05-01"
"2013-05-02"
"2013-05-03"
...
"2013-05-29"
"2013-05-30"
"2013-05-31"
或者...在定义新的存储过程之后一种更简单的方法:)
Or... an easier method after defining a new stored procedure :)
CREATE OR REPLACE FUNCTION generate_series(date, date) RETURNS
SETOF date AS $$
SELECT $1 + g.s
FROM generate_series(0, ($2 - $1)) AS g(s);
$$ LANGUAGE SQL IMMUTABLE;
就像这样称呼它:
SELECT * FROM generate_series(start_date, end_date);
这篇关于PostgreSQL增量日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文