本文转载自微信公众号「SQL数据库开发」,作者丶平凡世界 。转载本文请联系SQL数据库开发公众号。
之前已经发布过使用存储过程,生存指定年份的日历表《SQL如何制作一张日历维度表》,然后我在文末留了一个思考题:如何将日期对应的农历也加进日历表中?
有不少同学留言该如何解决农历的问题,今天就告诉大家具体操作方法。
农历与世界通用的日历有所区别,是科学家演算出来的,目前为止只有到2049年的,以后的有了还可以加入!
所以我们可以把已经演算出来的具体农历制作成一张表,通过调用当前的日期来返回具体的农历。
创建农历表
- CREATE TABLE SolarData
- (
- yearId int not null primary key,
- data char(7) not null,
- dataInt int not null
- )
插入农历数据
农历数据包含从1900年到2049年的具体数据。
- INSERT INTO
- SolarData SELECT 1900,'0x04bd8',19416 UNION ALL SELECT 1901,'0x04ae0',19168 UNION ALL SELECT 1902,'0x0a570',42352 UNION ALL SELECT 1903,'0x054d5',21717
- UNION ALL SELECT 1904,'0x0d260',53856 UNION ALL SELECT 1905,'0x0d950',55632 UNION ALL SELECT 1906,'0x16554',91476 UNION ALL SELECT 1907,'0x056a0',22176
- UNION ALL SELECT 1908,'0x09ad0',39632 UNION ALL SELECT 1909,'0x055d2',21970 UNION ALL SELECT 1910,'0x04ae0',19168 UNION ALL SELECT 1911,'0x0a5b6',42422
- UNION ALL SELECT 1912,'0x0a4d0',42192 UNION ALL SELECT 1913,'0x0d250',53840 UNION ALL SELECT 1914,'0x1d255',119381 UNION ALL SELECT 1915,'0x0b540',46400
- UNION ALL SELECT 1916,'0x0d6a0',54944 UNION ALL SELECT 1917,'0x0ada2',44450 UNION ALL SELECT 1918,'0x095b0',38320 UNION ALL SELECT 1919,'0x14977',84343
- UNION ALL SELECT 1920,'0x04970',18800 UNION ALL SELECT 1921,'0x0a4b0',42160 UNION ALL SELECT 1922,'0x0b4b5',46261 UNION ALL SELECT 1923,'0x06a50',27216
- UNION ALL SELECT 1924,'0x06d40',27968 UNION ALL SELECT 1925,'0x1ab54',109396 UNION ALL SELECT 1926,'0x02b60',11104 UNION ALL SELECT 1927,'0x09570',38256
- UNION ALL SELECT 1928,'0x052f2',21234 UNION ALL SELECT 1929,'0x04970',18800 UNION ALL SELECT 1930,'0x06566',25958 UNION ALL SELECT 1931,'0x0d4a0',54432
- UNION ALL SELECT 1932,'0x0ea50',59984 UNION ALL SELECT 1933,'0x06e95',28309 UNION ALL SELECT 1934,'0x05ad0',23248 UNION ALL SELECT 1935,'0x02b60',11104
- UNION ALL SELECT 1936,'0x186e3',100067 UNION ALL SELECT 1937,'0x092e0',37600 UNION ALL SELECT 1938,'0x1c8d7',116951 UNION ALL SELECT 1939,'0x0c950',51536
- UNION ALL SELECT 1940,'0x0d4a0',54432 UNION ALL SELECT 1941,'0x1d8a6',120998 UNION ALL SELECT 1942,'0x0b550',46416 UNION ALL SELECT 1943,'0x056a0',22176
- UNION ALL SELECT 1944,'0x1a5b4',107956 UNION ALL SELECT 1945,'0x025d0',9680 UNION ALL SELECT 1946,'0x092d0',37584 UNION ALL SELECT 1947,'0x0d2b2',53938
- UNION ALL SELECT 1948,'0x0a950',43344 UNION ALL SELECT 1949,'0x0b557',46423 UNION ALL SELECT 1950,'0x06ca0',27808 UNION ALL SELECT 1951,'0x0b550',46416
- UNION ALL SELECT 1952,'0x15355',86869 UNION ALL SELECT 1953,'0x04da0',19872 UNION ALL SELECT 1954,'0x0a5d0',42448 UNION ALL SELECT 1955,'0x14573',83315
- UNION ALL SELECT 1956,'0x052d0',21200 UNION ALL SELECT 1957,'0x0a9a8',43432 UNION ALL SELECT 1958,'0x0e950',59728 UNION ALL SELECT 1959,'0x06aa0',27296
- UNION ALL SELECT 1960,'0x0aea6',44710 UNION ALL SELECT 1961,'0x0ab50',43856 UNION ALL SELECT 1962,'0x04b60',19296 UNION ALL SELECT 1963,'0x0aae4',43748
- UNION ALL SELECT 1964,'0x0a570',42352 UNION ALL SELECT 1965,'0x05260',21088 UNION ALL SELECT 1966,'0x0f263',62051 UNION ALL SELECT 1967,'0x0d950',55632
- UNION ALL SELECT 1968,'0x05b57',23383 UNION ALL SELECT 1969,'0x056a0',22176 UNION ALL SELECT 1970,'0x096d0',38608 UNION ALL SELECT 1971,'0x04dd5',19925
- UNION ALL SELECT 1972,'0x04ad0',19152 UNION ALL SELECT 1973,'0x0a4d0',42192 UNION ALL SELECT 1974,'0x0d4d4',54484 UNION ALL SELECT 1975,'0x0d250',53840
- UNION ALL SELECT 1976,'0x0d558',54616 UNION ALL SELECT 1977,'0x0b540',46400 UNION ALL SELECT 1978,'0x0b5a0',46496 UNION ALL SELECT 1979,'0x195a6',103846
- UNION ALL SELECT 1980,'0x095b0',38320 UNION ALL SELECT 1981,'0x049b0',18864 UNION ALL SELECT 1982,'0x0a974',43380 UNION ALL SELECT 1983,'0x0a4b0',42160
- UNION ALL SELECT 1984,'0x0b27a',45690 UNION ALL SELECT 1985,'0x06a50',27216 UNION ALL SELECT 1986,'0x06d40',27968 UNION ALL SELECT 1987,'0x0af46',44870
- UNION ALL SELECT 1988,'0x0ab60',43872 UNION ALL SELECT 1989,'0x09570',38256 UNION ALL SELECT 1990,'0x04af5',19189 UNION ALL SELECT 1991,'0x04970',18800
- UNION ALL SELECT 1992,'0x064b0',25776 UNION ALL SELECT 1993,'0x074a3',29859 UNION ALL SELECT 1994,'0x0ea50',59984 UNION ALL SELECT 1995,'0x06b58',27480
- UNION ALL SELECT 1996,'0x055c0',21952 UNION ALL SELECT 1997,'0x0ab60',43872 UNION ALL SELECT 1998,'0x096d5',38613 UNION ALL SELECT 1999,'0x092e0',37600
- UNION ALL SELECT 2000,'0x0c960',51552 UNION ALL SELECT 2001,'0x0d954',55636 UNION ALL SELECT 2002,'0x0d4a0',54432 UNION ALL SELECT 2003,'0x0da50',55888
- UNION ALL SELECT 2004,'0x07552',30034 UNION ALL SELECT 2005,'0x056a0',22176 UNION ALL SELECT 2006,'0x0abb7',43959 UNION ALL SELECT 2007,'0x025d0',9680
- UNION ALL SELECT 2008,'0x092d0',37584 UNION ALL SELECT 2009,'0x0cab5',51893 UNION ALL SELECT 2010,'0x0a950',43344 UNION ALL SELECT 2011,'0x0b4a0',46240
- UNION ALL SELECT 2012,'0x0baa4',47780 UNION ALL SELECT 2013,'0x0ad50',44368 UNION ALL SELECT 2014,'0x055d9',21977 UNION ALL SELECT 2015,'0x04ba0',19360
- UNION ALL SELECT 2016,'0x0a5b0',42416 UNION ALL SELECT 2017,'0x15176',86390 UNION ALL SELECT 2018,'0x052b0',21168 UNION ALL SELECT 2019,'0x0a930',43312
- UNION ALL SELECT 2020,'0x07954',31060 UNION ALL SELECT 2021,'0x06aa0',27296 UNION ALL SELECT 2022,'0x0ad50',44368 UNION ALL SELECT 2023,'0x05b52',23378
- UNION ALL SELECT 2024,'0x04b60',19296 UNION ALL SELECT 2025,'0x0a6e6',42726 UNION ALL SELECT 2026,'0x0a4e0',42208 UNION ALL SELECT 2027,'0x0d260',53856
- UNION ALL SELECT 2028,'0x0ea65',60005 UNION ALL SELECT 2029,'0x0d530',54576 UNION ALL SELECT 2030,'0x05aa0',23200 UNION ALL SELECT 2031,'0x076a3',30371
- UNION ALL SELECT 2032,'0x096d0',38608 UNION ALL SELECT 2033,'0x04bd7',19415 UNION ALL SELECT 2034,'0x04ad0',19152 UNION ALL SELECT 2035,'0x0a4d0',42192
- UNION ALL SELECT 2036,'0x1d0b6',118966 UNION ALL SELECT 2037,'0x0d250',53840 UNION ALL SELECT 2038,'0x0d520',54560 UNION ALL SELECT 2039,'0x0dd45',56645
- UNION ALL SELECT 2040,'0x0b5a0',46496 UNION ALL SELECT 2041,'0x056d0',22224 UNION ALL SELECT 2042,'0x055b2',21938 UNION ALL SELECT 2043,'0x049b0',18864
- UNION ALL SELECT 2044,'0x0a577',42359 UNION ALL SELECT 2045,'0x0a4b0',42160 UNION ALL SELECT 2046,'0x0aa50',43600 UNION ALL SELECT 2047,'0x1b255',111189
- UNION ALL SELECT 2048,'0x06d20',27936 UNION ALL SELECT 2049,'0x0ada0',44448
(提示:可以左右滑动代码)
计算具体农历
农历的计算有固定的规则,我们通过下面的算法计算出公历日期对应的农历。
注:以下算法来自网络
- create function dbo.fn_GetLunar(@solarday datetime)
- returns nvarchar(30)
- as
- begin
- declare @soldata int
- declare @offset int
- declare @ilunar int
- declare @i int
- declare @j int
- declare @ydays int
- declare @mdays int
- declare @mleap int
- declare @mleap1 int
- declare @mleapnum int
- declare @bleap smallint
- declare @temp int
- declare @year nvarchar(10)
- declare @month nvarchar(10)
- declare @day nvarchar(10)
- declare @chinesenum nvarchar(10)
- declare @outputdate nvarchar(30)
- set @offset=datediff(day,'1900-01-30',@solarday)
- --确定农历年开始
- set @i=1900
- --set @offset=@soldata
- while @i<2050 and @offset>0
- begin
- set @ydays=348
- set @mleapnum=0
- select @ilunar=dataint from solardata where yearid=@i
- --传回农历年的总天数
- set @j=32768
- while @j>8
- begin
- if @ilunar & @j >0
- set @ydays=@ydays+1
- set @j=@j/2
- end
- --传回农历年闰哪个月1-12 , 没闰传回0
- set @mleap = @ilunar & 15
- --传回农历年闰月的天数,加在年的总天数上
- if @mleap > 0
- begin
- if @ilunar & 65536 > 0
- set @mleapnum=30
- else
- set @mleapnum=29
- set @ydays=@ydays+@mleapnum
- end
- set @offset=@offset-@ydays
- set @i=@i+1
- end
-
- if @offset <= 0
- begin
- set @offset=@offset+@ydays
- set @i=@i-1
- end
- --确定农历年结束
- set @year=@i
- --确定农历月开始
- set @i = 1
- select @ilunar=dataint from solardata where yearid=@year
- --判断那个月是润月
- set @mleap = @ilunar & 15
- set @bleap = 0
- while @i < 13 and @offset > 0
- begin
- --判断润月
- set @mdays=0
- if (@mleap > 0 and @i = (@mleap+1) and @bleap=0)
- begin--是润月
- set @i=@i-1
- set @bleap=1
- set @mleap1= @mleap
- --传回农历年闰月的天数
- if @ilunar & 65536 > 0
- set @mdays = 30
- else
- set @mdays = 29
- end
- else
- --不是润月
- begin
- set @j=1
- set @temp = 65536
- while @j<=@i
- begin
- set @temp=@temp/2
- set @j=@j+1
- end
- if @ilunar & @temp > 0
- set @mdays = 30
- else
- set @mdays = 29
- end
- --解除润月
- if @bleap=1 and @i= (@mleap+1)
- set @bleap=0
- set @offset=@offset-@mdays
- set @i=@i+1
- end
- if @offset <= 0
- begin
- set @offset=@offset+@mdays
- set @i=@i-1
- end
- --确定农历月结束
- set @month=@i
- --确定农历日结束
- set @day=ltrim(@offset)
- --输出日期
- set @chinesenum=N'〇一二三四五六七八九十'
- while len(@year)>0
- select @outputdate=isnull(@outputdate,'')
- + substring(@chinesenum,left(@year,1)+1,1)
- , @year=stuff(@year,1,1,'')
- set @outputdate=@outputdate+N'年'
- + case @mleap1 when @month then N'润' else '' end
- if cast(@month as int)<10
- set @outputdate=@outputdate
- + case @month when 1 then N'正'
- else substring(@chinesenum,left(@month,1)+1,1)
- end
- else if cast(@month as int)>=10
- set @outputdate=@outputdate
- + case @month when '10' then N'十' when 11 then N'十一'
- else N'十二' end
- set @outputdate=@outputdate + N'月'
- if cast(@day as int)<10
- set @outputdate=@outputdate + N'初'
- + substring(@chinesenum,left(@day,1)+1,1)
- else if @day between '10' and '19'
- set @outputdate=@outputdate
- + case @day when '10' then N'初十' else N'十'+
- substring(@chinesenum,right(@day,1)+1,1) end
- else if @day between '20' and '29'
- set @outputdate=@outputdate
- + case @day when '20' then N'二十' else N'廿' end
- + case @day when '20' then N'' else
- substring(@chinesenum,right(@day,1)+1,1) end
- else
- set @outputdate=@outputdate+N'三十'
- return @outputdate
- end
- GO
测试农历算法
我们创建的是一个农历的函数,当我们将日期传递给这个函数,其就会返回具体的日历了。
- SELECT dbo.fn_GetLunar('2021-02-19') Lunar
查看一下返回的结果:
我们去查了一下日历,验证结果是正确的
将农历加入到日历表
已经获得了公历转换成农历的转换函数,我们直接调用该函数即可。修改一下日历表结构(在表CALENDAR_INFO 中添加一个字符串格式的字段Lunar)和存储过程。更新后的存储过程如下:
- CREATE PROC PROC_CALENDAR(@YEAR INT )
- AS
- BEGIN
- DECLARE @i INT;
- DECLARE @START_DATE VARCHAR(20);
- DECLARE @END_DATE VARCHAR(20);
- DECLARE @DATE_COUNT INT;
- SET @i=0;
- --定义一年的开始日期,用CONCAT函数将年份和月份日期拼接起来
- SET @START_DATE= CONCAT(@YEAR, '-01-01');
- --定义一年的结束日期
- SET @END_DATE = CONCAT(@YEAR+1,'-01-01');
- --如果表已经存在要新建的日历,则先删除
- DELETE FROM CALENDAR_INFO WHERE YEAR = @YEAR;
- --计算开始到结束日期一共有多少天,DATEDIFF函数计算日期的间隔天数
- SET @DATE_COUNT = DATEDIFF(DAY,@START_DATE,@END_DATE);
-
- --建立循环,条件是@i小于一年的天数
- WHILE @i < @DATE_COUNT
- BEGIN
- INSERT INTO CALENDAR_INFO (COUNTRY,CDR_CODE,DATE_NAME,LUNAR,
- YEAR,MONTH,WEEK,DAY,CDR_DATE,CREATOR,CREATE_DATE,CREATE_INST)
- SELECT
- 'CN',
- 'CN01',
- --CONVERT函数将@START_DATE转换成指定的格式
- CONVERT(CHAR(10),@START_DATE,111) DATE_NAME,
- --将公历日期转换为农历
- dbo.fn_GetLunar(CONVERT(datetime,@start_date,101)) LUNAR,
- --DATENAME获取@START_DATE的年份
- CONVERT(CHAR(4),DATENAME(YEAR, @START_DATE)) YEAR,
- --DATENAME获取@START_DATE的月份
- CONVERT(CHAR(2),DATENAME(MONTH, @START_DATE)) MONTH,
- --DATENAME获取@START_DATE的星期
- CONVERT(CHAR(6),DATENAME(WEEKDAY, @START_DATE)) WEEK,
- --DATENAME获取@START_DATE的日期
- CONVERT(CHAR(2),DATENAME(DAY, @START_DATE)) DAY,
- CONVERT(CHAR(10),@START_DATE,120) CDR_DATE,
- '平凡世界',
- GETDATE() CREATE_DATE,
- 'Lyven'
- --插入完成后,对@i进行自加1处理
- SET @i=@i+1;
- --更新@START_DATE的值,在原来的基础上使用DATEADD函数增加一天
- SET @START_DATE=CONVERT(CHAR(10),DATEADD(DAY, 1, @START_DATE),120);
- END
- END
执行存储过程
- EXEC proc_calendar 2019
结果如下:
我们去查了一下日历,验证结果是正确的
日期维度表作用
可能有同学会问,花这么大力气就写了个这个,到底有什么用啊?
既然叫维度表,那肯定是跟维度有关了,有了这个维度表,我们可以通过多维数据集来查看不同日期维度的具体数据,特别是应用在可视化报表开发方面。
下面就是一个比较简单的Power BI报表,这里我们就使用到了日期维度表中的年月。
Power BI效果图
至此,一个包含农历的完整日期维度表就生成了,有兴趣的小伙伴可以用MySQL或Oracle进行改写一下。
参考资料
https://www.cnblogs.com/accumulater/articles/6807248.html