SQLite 에서 Calendar Query

/*
sqlite에서 query를 이용해서 달력을 조회하기 위한 쿼리.
*/

/* 달력쿼리를 위한 임시테이블 생성작업 */


create table dumy as
select 'x' from sqlite_master
cross join sqlite_master
cross join sqlite_master
limit 366;


/* 1개월 달력 */

select
strftime('%W', '2011-02-01', '+'||(c.rowid)||' day', 'localtime')+1 as 주
, max(case when strftime('%w', '2011-02-01', '+'||(c.rowid-1)||' day') = '0' then strftime('%d', '2011-02-01', '+'||(c.rowid-1)||' day') else '' end) 일
, max(case when strftime('%w', '2011-02-01', '+'||(c.rowid-1)||' day') = '1' then strftime('%d', '2011-02-01', '+'||(c.rowid-1)||' day') else '' end) 월
, max(case when strftime('%w', '2011-02-01', '+'||(c.rowid-1)||' day') = '2' then strftime('%d', '2011-02-01', '+'||(c.rowid-1)||' day') else '' end) 화
, max(case when strftime('%w', '2011-02-01', '+'||(c.rowid-1)||' day') = '3' then strftime('%d', '2011-02-01', '+'||(c.rowid-1)||' day') else '' end) 수
, max(case when strftime('%w', '2011-02-01', '+'||(c.rowid-1)||' day') = '4' then strftime('%d', '2011-02-01', '+'||(c.rowid-1)||' day') else '' end) 목
, max(case when strftime('%w', '2011-02-01', '+'||(c.rowid-1)||' day') = '5' then strftime('%d', '2011-02-01', '+'||(c.rowid-1)||' day') else '' end) 금
, max(case when strftime('%w', '2011-02-01', '+'||(c.rowid-1)||' day') = '6' then strftime('%d', '2011-02-01', '+'||(c.rowid-1)||' day') else '' end) 토
from dumy c
cross join (
select ((t.rowid-1) % 7) weekday
from dumy t
where 1=1
limit 7) x
where 1=1
and c.rowid <= strftime('%d', date('2011-02-01','start of month','+1 month','-1 day'))
group by strftime('%W', '2011-02-01', '+'||(c.rowid)||' day', 'localtime');



/* 1년 달력 */

SELECT
min(c.rowid) start
, max(c.rowid) end
, strftime('%W', '2011-01-01', '+'||(c.rowid)||' day', 'localtime')+1 as Week
, strftime('%m', '2011-01-01', '+'||(c.rowid-1)||' day', 'localtime') as Month
, strftime('%Y%m', '2011-01-01', '+'||(c.rowid-1)||' day', 'localtime') as yyyymm
, max(case when strftime('%w', '2011-01-01', '+'||(c.rowid-1)||' day') = '0' then strftime('%d', '2011-01-01', '+'||(c.rowid-1)||' day') else '' end) 일
, max(case when strftime('%w', '2011-01-01', '+'||(c.rowid-1)||' day') = '1' then strftime('%d', '2011-01-01', '+'||(c.rowid-1)||' day') else '' end) 월
, max(case when strftime('%w', '2011-01-01', '+'||(c.rowid-1)||' day') = '2' then strftime('%d', '2011-01-01', '+'||(c.rowid-1)||' day') else '' end) 화
, max(case when strftime('%w', '2011-01-01', '+'||(c.rowid-1)||' day') = '3' then strftime('%d', '2011-01-01', '+'||(c.rowid-1)||' day') else '' end) 수
, max(case when strftime('%w', '2011-01-01', '+'||(c.rowid-1)||' day') = '4' then strftime('%d', '2011-01-01', '+'||(c.rowid-1)||' day') else '' end) 목
, max(case when strftime('%w', '2011-01-01', '+'||(c.rowid-1)||' day') = '5' then strftime('%d', '2011-01-01', '+'||(c.rowid-1)||' day') else '' end) 금
, max(case when strftime('%w', '2011-01-01', '+'||(c.rowid-1)||' day') = '6' then strftime('%d', '2011-01-01', '+'||(c.rowid-1)||' day') else '' end) 토
from dumy c
cross join (
select ((t.rowid-1) % 7) weekday
from dumy t
where 1=1
limit 7) x
where 1=1
and c.rowid <= strftime('%j', date('2011-12-01','start of month','+1 month','-1 day'))
group by
strftime('%m', '2011-01-01', '+'||(c.rowid-1)||' day', 'localtime')
,strftime('%W', '2011-01-01', '+'||(c.rowid-1)||' day', 'localtime')
+ case when strftime('%w', '2011-01-01', '+'||(c.rowid-1)||' day', 'localtime') = '0' then 1 else 0 end
order by c.rowid;