Postgresで連続する日付を生成

bash : time series

bash であればある日を起点にして、5 日分の日付がほしい場合、以下のようにしてできる。

for (( c=0; c<5; c++ ))
do
    date --date="20121230 +$c day" +%Y/%m/%d
done

PostgreSQL : time series

これと近いことを PostgreSQL でやるには generate_series を使うと良い。

generate_series 関数の引数は generate_series(start, stop, step) (step はオプション)

generate_series で整数を生成

postgres=# select generate_series(0, 4);
 generate_series
-----------------
               0
               1
               2
               3
               4
(5 rows)
postgres=# select '2012-12-30'::date + s.a as dates from generate_series(0, 4) as s(a);
   dates
------------
 2012-12-30
 2012-12-31
 2013-01-01
 2013-01-02
 2013-01-03
(5 rows)

generate_series で日付を直接生成

postgres=# select * from generate_series('2012-12-30'::date, '2013-01-03', '1 day');
    generate_series
------------------------
 2012-12-30 00:00:00+09
 2012-12-31 00:00:00+09
 2013-01-01 00:00:00+09
 2013-01-02 00:00:00+09
 2013-01-03 00:00:00+09
(5 rows)

Example

generate_series を使うと、日付の範囲を指定してテーブルに存在しない日付を簡単に抽出できる。

postgres=# create table t(n serial primary key, d date);
postgres=# insert into t(d) values('2013/01/01');
INSERT 0 1
postgres=# insert into t(d) values('2013/01/02');
INSERT 0 1
postgres=# select * from t;
 n |     d
---+------------
 1 | 2013-01-01
 2 | 2013-01-02
(2 rows)

   select gs.*
     from generate_series('2012-12-30'::date, '2013-01-03', '1 day') as gs(d)
left join t
       on gs.d = t.d
    where t.d is null;

           d
------------------------
 2012-12-30 00:00:00+09
 2012-12-31 00:00:00+09
 2013-01-03 00:00:00+09
(3 rows)

Note

generate_series は PostgreSQL 8 以降でしか使えないので、7 系列の場合は日付だけのテーブルを作成し、このテーブルから JOIN するといった対応が必要。

References

Advertisements
Tagged with: , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
%d bloggers like this: