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
  • RT @__apf__: How to write a research paper: a guide for software engineers & practitioners. docs.google.com/presentation/d… /cc @inwyrd 4 months ago
  • RT @HayatoChiba: 昔、自然と対話しながら数学に打ち込んだら何かを悟れるのではと思いたち、専門書1つだけ持ってパワースポットで名高い奈良の山奥に1週間籠ったことがある。しかし泊まった民宿にドカベンが全巻揃っていたため、水島新司と対話しただけで1週間過ぎた。 それ… 4 months ago
  • RT @googlecloud: Ever wonder what underwater fiber optic internet cables look like? Look no further than this deep dive w/ @NatAndLo: https… 4 months ago
  • @ijin UTC+01:00 な時間帯で生活しています、、、 10 months ago
  • RT @mattcutts: Google's world-class Site Reliability Engineering team wrote a new book: amazon.com/Site-Reliabili… It's about managing produc… 1 year ago
%d bloggers like this: