功能: 时间维度在数据仓库中应用非常广泛.面对用户关于不同时间维度的分析。需要对时间处理很了解。
用法:1. 当前时间
select sysdate from dual; 2012-2-17 17:02:02
2. 某天是星期几
SELECT to_char(SYSDATE,'day') FROM dual ; 星期五 --显示的是中文(注意跟参数NLS_DATE_LANGUAGE有不关 我的是SIMPLIFIED CHINESE)
SELECT to_char(SYSDATE,'day', 'nls_date_language=american') FROM dual ; friday --显示的是英文
3. 两个日期相差几天
SELECT abs(floor(SYSDATE-to_date('20120331','yyyymmdd'))) FROM dual; 43
3. 两个日期相差月数
SELECT abs(months_between(to_date('20111214','yyyymmdd'),SYSDATE)) FROM dual; 2.1
4. 某天下个星期几的日期
SELECT next_day(SYSDATE,'星期五') FROM dual; 2012-2-24 17:19:30
5. trunc处理时间
a. SELECT trunc(SYSDATE,'yy') FROM dual ; 2012-1-1
b. SELECT trunc(SYSDATE,'MM') FROM dual; 2012-2-1
c. SELECT trunc(SYSDATE,'DAY') FROM dual; 2012-2-12
d. SELECT trunc(SYSDATE,'DD') FROM dual; 2012-2-17
6. 某天是该月第几天
SELECT to_char(SYSDATE,'DD') FROM dual; 17
7 . 某天是该年第几天
SELECT to_char(SYSDATE,'DDD') FROM dual; 048
继续补充: sysdate =20120221
8. 某天是该月的第几周(月内周)
SELECT CEIL((SYSDATE-NEXT_DAY(TRUNC(SYSDATE,'MM'),'星期一')+1)/7+1) FROM dual; 结果: 4
9 . 某天是该年的第几周(年内周)
SELECT CEIL((SYSDATE-NEXT_DAY(TRUNC(SYSDATE,'yy'),'星期一')+1)/7+1) FROM dual; 结果: 9
10. 某天是该年的第几季度
SELECT to_char(SYSDATE,'Q') FROM DUAL ; 结果: 1
11. 计算农历日期。需要建立一个函数, 一张临时表及临时表数据
临时表:
create table P_MID_LUNAR_CALENDAR
(
NL_YEAR NUMBER(38),
NL_YEAR_H CHAR(7),
NL_YEAR_D NUMBER(38)
);
comment on table P_MID_LUNAR_CALENDAR
is '时间维度表中节假日使用';
-- Add comments to the columns
comment on column P_MID_LUNAR_CALENDAR.NL_YEAR
is '农历年';
comment on column P_MID_LUNAR_CALENDAR.NL_YEAR_H
is '农历年对应的16进制数';
comment on column P_MID_LUNAR_CALENDAR.NL_YEAR_D
is '农历年对应的10进制数';
临时表数据:
NL_YEAR | NL_YEAR_H | NL_YEAR_D |
1900 | 0x04bd8 | 19416 |
1901 | 0x04ae0 | 19168 |
1902 | 0x0a570 | 42352 |
1903 | 0x054d5 | 21717 |
1904 | 0x0d260 | 53856 |
1905 | 0x0d950 | 55632 |
1906 | 0x16554 | 91476 |
1907 | 0x056a0 | 22176 |
1908 | 0x09ad0 | 39632 |
1909 | 0x055d2 | 21970 |
1910 | 0x04ae0 | 19168 |
1911 | 0x0a5b6 | 42422 |
1912 | 0x0a4d0 | 42192 |
1913 | 0x0d250 | 53840 |
1914 | 0x1d255 | 119381 |
1915 | 0x0b540 | 46400 |
1916 | 0x0d6a0 | 54944 |
1917 | 0x0ada2 | 44450 |
1918 | 0x095b0 | 38320 |
1919 | 0x14977 | 84343 |
1920 | 0x04970 | 18800 |
1921 | 0x0a4b0 | 42160 |
1922 | 0x0b4b5 | 46261 |
1923 | 0x06a50 | 27216 |
1924 | 0x06d40 | 27968 |
1925 | 0x1ab54 | 109396 |
1926 | 0x02b60 | 11104 |
1927 | 0x09570 | 38256 |
1928 | 0x052f2 | 21234 |
1929 | 0x04970 | 18800 |
1930 | 0x06566 | 25958 |
1931 | 0x0d4a0 | 54432 |
1932 | 0x0ea50 | 59984 |
1933 | 0x06e95 | 28309 |
1934 | 0x05ad0 | 23248 |
1935 | 0x02b60 | 11104 |
1936 | 0x186e3 | 100067 |
1937 | 0x092e0 | 37600 |
1938 | 0x1c8d7 | 116951 |
1939 | 0x0c950 | 51536 |
1940 | 0x0d4a0 | 54432 |
1941 | 0x1d8a6 | 120998 |
1942 | 0x0b550 | 46416 |
1943 | 0x056a0 | 22176 |
1944 | 0x1a5b4 | 107956 |
1945 | 0x025d0 | 9680 |
1946 | 0x092d0 | 37584 |
1947 | 0x0d2b2 | 53938 |
1948 | 0x0a950 | 43344 |
1949 | 0x0b557 | 46423 |
1950 | 0x06ca0 | 27808 |
1951 | 0x0b550 | 46416 |
1952 | 0x15355 | 86869 |
1953 | 0x04da0 | 19872 |
1954 | 0x0a5d0 | 42448 |
1955 | 0x14573 | 83315 |
1956 | 0x052d0 | 21200 |
1957 | 0x0a9a8 | 43432 |
1958 | 0x0e950 | 59728 |
1959 | 0x06aa0 | 27296 |
1960 | 0x0aea6 | 44710 |
1961 | 0x0ab50 | 43856 |
1962 | 0x04b60 | 19296 |
1963 | 0x0aae4 | 43748 |
1964 | 0x0a570 | 42352 |
1965 | 0x05260 | 21088 |
1966 | 0x0f263 | 62051 |
1967 | 0x0d950 | 55632 |
1968 | 0x05b57 | 23383 |
1969 | 0x056a0 | 22176 |
1970 | 0x096d0 | 38608 |
1971 | 0x04dd5 | 19925 |
1972 | 0x04ad0 | 19152 |
1973 | 0x0a4d0 | 42192 |
1974 | 0x0d4d4 | 54484 |
1975 | 0x0d250 | 53840 |
1976 | 0x0d558 | 54616 |
1977 | 0x0b540 | 46400 |
1978 | 0x0b5a0 | 46496 |
1979 | 0x195a6 | 103846 |
1980 | 0x095b0 | 38320 |
1981 | 0x049b0 | 18864 |
1982 | 0x0a974 | 43380 |
1983 | 0x0a4b0 | 42160 |
1984 | 0x0b27a | 45690 |
1985 | 0x06a50 | 27216 |
1986 | 0x06d40 | 27968 |
1987 | 0x0af46 | 44870 |
1988 | 0x0ab60 | 43872 |
1989 | 0x09570 | 38256 |
1990 | 0x04af5 | 19189 |
1991 | 0x04970 | 18800 |
1992 | 0x064b0 | 25776 |
1993 | 0x074a3 | 29859 |
1994 | 0x0ea50 | 59984 |
1995 | 0x06b58 | 27480 |
1996 | 0x055c0 | 21952 |
1997 | 0x0ab60 | 43872 |
1998 | 0x096d5 | 38613 |
1999 | 0x092e0 | 37600 |
2000 | 0x0c960 | 51552 |
2001 | 0x0d954 | 55636 |
2002 | 0x0d4a0 | 54432 |
2003 | 0x0da50 | 55888 |
2004 | 0x07552 | 30034 |
2005 | 0x056a0 | 22176 |
2006 | 0x0abb7 | 43959 |
2007 | 0x025d0 | 9680 |
2008 | 0x092d0 | 37584 |
2009 | 0x0cab5 | 51893 |
2010 | 0x0a950 | 43344 |
2011 | 0x0b4a0 | 46240 |
2012 | 0x0baa4 | 47780 |
2013 | 0x0ad50 | 44368 |
2014 | 0x055d9 | 21977 |
2015 | 0x04ba0 | 19360 |
2016 | 0x0a5b0 | 42416 |
2017 | 0x15176 | 86390 |
2018 | 0x052b0 | 21168 |
2019 | 0x0a930 | 43312 |
2020 | 0x07954 | 31060 |
2021 | 0x06aa0 | 27296 |
2022 | 0x0ad50 | 44368 |
2023 | 0x05b52 | 23378 |
2024 | 0x04b60 | 19296 |
2025 | 0x0a6e6 | 42726 |
2026 | 0x0a4e0 | 42208 |
2027 | 0x0d260 | 53856 |
2028 | 0x0ea65 | 60005 |
2029 | 0x0d530 | 54576 |
2030 | 0x05aa0 | 23200 |
2031 | 0x076a3 | 30371 |
2032 | 0x096d0 | 38608 |
2033 | 0x04bd7 | 19415 |
2034 | 0x04ad0 | 19152 |
2035 | 0x0a4d0 | 42192 |
2036 | 0x1d0b6 | 118966 |
2037 | 0x0d250 | 53840 |
2038 | 0x0d520 | 54560 |
2039 | 0x0dd45 | 56645 |
2040 | 0x0b5a0 | 46496 |
2041 | 0x056d0 | 22224 |
2042 | 0x055b2 | 21938 |
2043 | 0x049b0 | 18864 |
2044 | 0x0a577 | 42359 |
2045 | 0x0a4b0 | 42160 |
2046 | 0x0aa50 | 43600 |
2047 | 0x1b255 | 111189 |
2048 | 0x06d20 | 27936 |
2049 | 0x0ada0 | 44448 |
函数代码:
CREATE OR REPLACE FUNCTION Fun_GetLunar(i_SolarDay DATE) RETURN VARCHAR2
/**
*@功能: 算阳历1900/01/31 - 2050/01/22间某一天对应的阴历是多少
*@原理 在一张表中用10进制格式保存某个农历年每月大小,有无闰月,闰月大小信息
1.用12个2进制位来表示某个农历年每月的大小,大月记为1,否则为0
2.用低4位来表示闰月的月份,没有闰月记为0
3.用一个高位表示闰月的大小,闰月大记为0,闰月小或无闰月记为0
4.再将该2进制数转化为10进制,存入表中
农历2000年: 0 110010010110 0000 -> 0x0c960 -> 51552
农历2001年: 0 110110010101 0100 -> 0x0d954 -> 55636
采用查表的方式计算出农历日期
*@性能参考: 估计运行时间:约10s
**/
AS
v_OffSet INT;
v_Lunar INT; -- 农历年是否含闰月,几月是闰月,闰月天数,其它月天数
v_YearDays INT; -- 农历年所含天数
v_MonthDays INT; -- 农历月所含天数
v_LeapMonthDays INT; -- 农历闰月所含天数
v_LeapMonth INT; -- 农历年闰哪个月 1-12 , 没闰传回 0
v_LeapFlag INT; -- 某农历月是否为闰月 1:是 0:不是
v_MonthNo INT; -- 某农历月所对应的2进制数 如农历3月: 001000000000
i INT;
j INT;
k INT;
v_Year INT; -- i_SolarDay 对应的农历年
v_Month INT; -- i_SolarDay 对应的农历月
v_Day INT; -- i_SolarDay 对应的农历日
o_OutputDate VARCHAR2(25); -- 返回值 格式:农历 ****年 **(闰)月 **日
e_ErrMsg VARCHAR2(200);
e_ErrDate EXCEPTION;
BEGIN
--输入参数判断
IF i_SolarDay < TO_DATE('1900-01-31', 'YYYY-MM-DD') OR
i_SolarDay >= TO_DATE('2050-01-23', 'YYYY-MM-DD') THEN
RAISE e_ErrDate;
END IF;
-- i_SolarDay 到 1900-01-30(即农历1900-01-01的前一天) 的天数
v_OffSet := TRUNC(i_SolarDay, 'DD') - TO_DATE('1900-01-30', 'YYYY-MM-DD');
-- 确定农历年开始
i := 1900;
WHILE i < 2050 AND v_OffSet > 0 LOOP
v_YearDays := 348; -- 29*12 以每年12个农历月,每个农历月含29个农历日为基数
v_LeapMonthDays := 0;
-- 取出农历年是否含闰月,几月是闰月,闰月天数,其它月天数
-- 如农历2001年: 0x0d954(16进制) -> 55636(10进制) -> 0 110110010101 0100(2进制)
-- 1,2,4,5,8,10,12月大, 3,6,7,9,11月小, 4月为闰月,闰月小
SELECT nl_year_d
INTO v_Lunar
FROM P_MID_LUNAR_CALENDAR
WHERE nl_year = i;
-- 传回农历年的总天数
j := 32768; -- 100000000000 0000 -> 32768
-- 0 110110010101 0100 -> 55636(农历2001年)
-- 依次判断v_Lunar年个月是否为大月,是则加一天
WHILE j > 8 LOOP
-- 闰月另行判断 8 -> 0 000000000000 1000
IF BITAND(v_Lunar, j) + 0 > 0 THEN
v_YearDays := v_YearDays + 1;
END IF;
j := j / 2; -- 判断下一个月是否为大
END LOOP;
-- 传回农历年闰哪个月 1-12 , 没闰传回 0 15 -> 1 0000
v_LeapMonth := BITAND(v_Lunar, 15) + 0;
-- 传回农历年闰月的天数 ,加在年的总天数上
IF v_LeapMonth > 0 THEN
-- 判断闰月大小 65536 -> 1 000000000000 0000
IF BITAND(v_Lunar, 65536) + 0 > 0 THEN
v_LeapMonthDays := 30;
ELSE
v_LeapMonthDays := 29;
END IF;
v_YearDays := v_YearDays + v_LeapMonthDays;
END IF;
v_OffSet := v_OffSet - v_YearDays;
i := i + 1;
END LOOP;
IF v_OffSet <= 0 THEN
-- i_SolarDay 在所属农历年(即i年)中的第 v_OffSet 天
v_OffSet := v_OffSet + v_YearDays;
i := i - 1;
END IF;
-- 确定农历年结束
v_Year := i;
-- 确定农历月开始
i := 1;
SELECT nl_year_d
INTO v_Lunar
FROM P_MID_LUNAR_CALENDAR
WHERE nl_year = v_Year;
-- 判断那个月是润月
-- 如农历2001年 (55636,15 -> 0 1101100101010100, 1111 -> 4) 即润4月,且闰月小
v_LeapMonth := BITAND(v_Lunar, 15) + 0;
v_LeapFlag := 0;
WHILE i < 13 AND v_OffSet > 0 LOOP
-- 判断是否为闰月
v_MonthDays := 0;
IF (v_LeapMonth > 0 AND i = (v_LeapMonth + 1) AND v_LeapFlag = 0) THEN
-- 是闰月
i := i - 1;
k := i; -- 保存是闰月的时i的值
v_LeapFlag := 1;
-- 传回农历年闰月的天数
IF BITAND(v_Lunar, 65536) + 0 > 0 THEN
v_MonthDays := 30;
ELSE
v_MonthDays := 29;
END IF;
ELSE
-- 不是闰月
j := 1;
v_MonthNo := 65536;
-- 计算 i 月对应的2进制数 如农历3月: 001000000000
WHILE j <= i LOOP
v_MonthNo := v_MonthNo / 2;
j := j + 1;
END LOOP;
-- 计算农历 v_Year 年 i 月的天数
IF BITAND(v_Lunar, v_MonthNo) + 0 > 0 THEN
v_MonthDays := 30;
ELSE
v_MonthDays := 29;
END IF;
END IF;
-- 解除闰月
IF v_LeapFlag = 1 AND i = v_LeapMonth + 1 THEN
v_LeapFlag := 0;
END IF;
v_OffSet := v_OffSet - v_MonthDays;
i := i + 1;
END LOOP;
IF v_OffSet <= 0 THEN
-- i_SolarDay 在所属农历月(即i月)中的第 v_OffSet 天
v_OffSet := v_OffSet + v_MonthDays;
i := i - 1;
END IF;
-- 确定农历月结束
v_Month := i;
-- 确定农历日结束
v_Day := v_OffSet;
-- 格式化返回值
o_OutputDate := '农历 ' || TO_CHAR(v_Year) || '年 ';
IF k = i THEN
o_OutputDate := o_OutputDate || LPAD(TO_CHAR(v_Month), 2, '0') ||
'(润)月 ';
ELSE
o_OutputDate := o_OutputDate || LPAD(TO_CHAR(v_Month), 2, '0') || '月 ';
END IF;
o_OutputDate := o_OutputDate || LPAD(TO_CHAR(v_Day), 2, '0') || '日';
RETURN o_OutputDate;
EXCEPTION
WHEN e_Errdate THEN
RETURN '日期错误! 有效范围(阳历): 1900/01/31 - 2050/01/22';
WHEN OTHERS THEN
e_ErrMsg := SUBSTR(SQLERRM, 1, 200);
RETURN e_ErrMsg;
END Fun_GetLunar;