前のブログでは確か、1
(1)
まず単純に、現在時刻から当月の全日付を
with recursive r (ts) as ( select date_trunc('month', now()) union select ts2 from r, cast(ts + '1day' as date) as ts2 where extract(month from ts) = extract(month from ts2) ) select ts :: date, to_char(ts, 'dy') as dow from r; +------------+-----+ | ts | dow | +------------+-----+ | 2016-06-01 | wed | | 2016-06-02 | thu | | 2016-06-03 | fri | | 2016-06-04 | sat | | 2016-06-05 | sun | | 2016-06-06 | mon | | 2016-06-07 | tue | | 2016-06-08 | wed | | 2016-06-09 | thu | | 2016-06-10 | fri | ... | 2016-06-26 | sun | | 2016-06-27 | mon | | 2016-06-28 | tue | | 2016-06-29 | wed | | 2016-06-30 | thu | +------------+-----+ (30 rows)
(2)
カレンダーに必要なのは「日」だけなので、その準備。to_char
with recursive r (ts, ary) as ( select date_trunc('month', now()), '{01}' :: text[] union select ts2, ary || to_char(ts2, 'dd') from r, cast(ts + '1day' as timestamp) as ts2 where extract(month from ts) = extract(month from ts2) ) select ts :: date, ary from r; +------------+----------------- | ts | ary +------------+----------------- | 2016-06-01 | {01} | 2016-06-02 | {01,02} | 2016-06-03 | {01,02,03} | 2016-06-04 | {01,02,03,04} | 2016-06-05 | {01,02,03,04,05} ...
(3)
日だけの配列を順番に連結して一つながりの文字列に、ただし日曜の後に改行を入れることでカレンダーの原型に。あとは第
with recursive r (ts, ary) as ( select date_trunc('month', now()), '{01}' :: text[] union select ts2, ary || case when extract(dow from ts) = 0 then E' \n' else '' end || to_char(ts2, 'dd') from r, cast(ts + '1day' as timestamp) as ts2 where extract(month from ts) = extract(month from ts2) ) select array_to_string(ary, ' ') from r order by ts desc limit 1; +-------------------------------+ | array_to_string | +-------------------------------+ | 01 02 03 04 05 +| | 06 07 08 09 10 11 12 +| | 13 14 15 16 17 18 19 +| | 20 21 22 23 24 25 26 +| | 27 28 29 30 | +-------------------------------+ (1 row)
(4)完成
冒頭の画像もこれです。(3)+
with recursive r (ts, ary) as ( select date_trunc('month', now()), '{01}' :: text[] union select ts2, ary || case when extract(dow from ts) = 0 then E' \n' else '' end || to_char(ts2, 'dd') from r, cast(ts + '1day' as timestamp) as ts2 where extract(month from ts) = extract(month from ts2) ) select concat('<', to_char(now(), 'YYYY-MM'), E'>\n', replace( concat(' ', repeat(' ', 4 * (extract(dow from date_trunc('month', ts)) :: int - 1)), array_to_string(ary, ' ')), ' 0', ' ')) as calendar from r order by ts desc limit 1; +-------------------------------+ | calendar | +-------------------------------+ | <2016-06> +| | 1 2 3 4 5 +| | 6 7 8 9 10 11 12 +| | 13 14 15 16 17 18 19 +| | 20 21 22 23 24 25 26 +| | 27 28 29 30 | +-------------------------------+ (1 row)
最終行に
実際使うにはストアド化して、引数で月を可変にするといいと思います。