lunar-calendar.sqbpro

<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="D:/Coding/Workspace/python/lunar-calendar/lunar-calendar.sqlite" readonly="0" foreign_keys="" case_sensitive_like="" temp_store="" wal_autocheckpoint="" synchronous=""/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="3506"/><column_width id="4" width="0"/></tab_structure><tab_browse><current_table name="0,0:"/><default_encoding codec=""/><browse_table_settings/></tab_browse><tab_sql><sql name="SQL 1">-- Create lookup view
CREATE VIEW `gregorian_lunar_view` AS SELECT gregorian_lunar.*, lunar_year_of_cycle.name AS lunar_year_of_cycle_name, lunar_day_of_month.name AS lunar_day_of_month_name, lunar_solar_term.name AS lunar_solar_term_name FROM gregorian_lunar
JOIN lunar_year_of_cycle ON gregorian_lunar.lunar_year_of_cycle + 1 = lunar_year_of_cycle.ROWID
JOIN lunar_day_of_month ON gregorian_lunar.lunar_day_of_month + 1 = lunar_day_of_month.ROWID
LEFT JOIN lunar_solar_term ON (gregorian_lunar.lunar_solar_term IS NOT NULL AND gregorian_lunar.lunar_solar_term + 1 = lunar_solar_term.ROWID)
;

-- Lookup Gregorian date by Chinese Lunar date
SELECT gregorian_date FROM gregorian_lunar WHERE lunar_cycle = 1 AND lunar_year_of_cycle = 37 AND lunar_month_of_year = 10 AND lunar_day_of_month = 20;

-- Lookup Chinese Lunar date by Gregorian date
SELECT lunar_cycle, lunar_year_of_cycle, lunar_month_of_year, lunar_day_of_month, lunar_solar_term FROM gregorian_lunar WHERE gregorian_date = '2020-07-06';
SELECT lunar_cycle, lunar_year_of_cycle, lunar_year_of_cycle_name, lunar_month_of_year, lunar_month_of_year_name, lunar_day_of_month, lunar_day_of_month_name, lunar_solar_term, lunar_solar_term_name FROM gregorian_lunar_view WHERE gregorian_date = '2020-07-06';
</sql><current_tab id="0"/></tab_sql></sqlb_project>