2008年12月28日星期日

new year calendar in postgresql

mmiw sun mon tue wed thr fri sat
0101       01 02 03 04
0102 05 06 07 08 09 10 11
0103 12 13 14 15 16 17 18
0104 19 20 21 22 23 24 25
0105 26 27 28 29 30 31  
0205             01
0206 02 03 04 05 06 07 08
0207 09 10 11 12 13 14 15
0208 16 17 18 19 20 21 22
0209 23 24 25 26 27 28  
0309             01
0310 02 03 04 05 06 07 08
0311 09 10 11 12 13 14 15
0312 16 17 18 19 20 21 22
0313 23 24 25 26 27 28 29
0314 30 31          
0414     01 02 03 04 05
0415 06 07 08 09 10 11 12
0416 13 14 15 16 17 18 19
0417 20 21 22 23 24 25 26
0418 27 28 29 30      
0518         01 02 03
0519 04 05 06 07 08 09 10
0520 11 12 13 14 15 16 17
0521 18 19 20 21 22 23 24
0522 25 26 27 28 29 30 31
0623 01 02 03 04 05 06 07
0624 08 09 10 11 12 13 14
0625 15 16 17 18 19 20 21
0626 22 23 24 25 26 27 28
0627 29 30          
0727     01 02 03 04 05
0728 06 07 08 09 10 11 12
0729 13 14 15 16 17 18 19
0730 20 21 22 23 24 25 26
0731 27 28 29 30 31    
0831           01 02
0832 03 04 05 06 07 08 09
0833 10 11 12 13 14 15 16
0834 17 18 19 20 21 22 23
0835 24 25 26 27 28 29 30
0836 31            
0936   01 02 03 04 05 06
0937 07 08 09 10 11 12 13
0938 14 15 16 17 18 19 20
0939 21 22 23 24 25 26 27
0940 28 29 30        
1040       01 02 03 04
1041 05 06 07 08 09 10 11
1042 12 13 14 15 16 17 18
1043 19 20 21 22 23 24 25
1044 26 27 28 29 30 31  
1144             01
1145 02 03 04 05 06 07 08
1146 09 10 11 12 13 14 15
1147 16 17 18 19 20 21 22
1148 23 24 25 26 27 28 29
1149 30            
1249   01 02 03 04 05 06
1250 07 08 09 10 11 12 13
1251 14 15 16 17 18 19 20
1252 21 22 23 24 25 26 27
1253 28 29 30 31      

(63 rows)


select to_char(d,'mmiw') as mmiw, 
max(case to_char(d,'d') when 2 then to_char(d,'dd') end) as sun,
max(case to_char(d,'d') when 3 then to_char(d,'dd') end) as mon,
max(case to_char(d,'d') when 4 then to_char(d,'dd') end) as tue,
max(case to_char(d,'d') when 5 then to_char(d,'dd') end) as wed,
max(case to_char(d,'d') when 6 then to_char(d,'dd') end) as thr,
max(case to_char(d,'d') when 7 then to_char(d,'dd') end) as fri,
max(case to_char(d,'d') when 1 then to_char(d,'dd') end) as sat
from (select
generate_series(0,364) +
date(date_trunc('month', now() + '1 months'::interval)) as d
) as range
group by to_char(d,'mmiw') order by 1;

1 条评论:

Metaphysic Qiu 说...

变量输入后
date(date_trunc('month', now() + '1 months'::interval)) as d

这句有语法错。。。。