I want to change the start day of week in Oracle, and I need to calculate some reports for each week from daily/hourly data.
Through lot of Google search I got only NLS_TERROTORY option, from which I don't know how to set to TUESDAY. Even if there is a territory, then how do change to different day in future. Thanks in advance!
可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
回答1:
You can create a package to contain custom functions functions relating to the new start of the week:
Note: numbering of weeks of the year is based on the first week of the year containing the first starting day of a week (i.e. Tuesday - so if Jan 1st is a Wednesday then the first week of the year would start on the Jan 7th). If you want a different logic then you will need to modify the WEEK_OF_YEAR function.
SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE PACKAGE change_week_start IS
week_start CONSTANT VARCHAR2(9) := 'TUESDAY';
FUNCTION TRUNC_TO_WEEK_START(
in_date IN DATE
) RETURN DATE;
FUNCTION DAY_OF_WEEK(
in_date IN DATE
) RETURN NUMBER;
FUNCTION WEEK_OF_YEAR(
in_date IN DATE
) RETURN NUMBER;
FUNCTION TO_CHAR_WEEK(
in_date IN DATE
) RETURN VARCHAR2;
END;
/
CREATE PACKAGE BODY change_week_start IS
FUNCTION TRUNC_TO_WEEK_START(
in_date IN DATE
) RETURN DATE
IS
BEGIN
RETURN NEXT_DAY( TRUNC( in_date ) - 7, week_start );
END;
FUNCTION DAY_OF_WEEK(
in_date IN DATE
) RETURN NUMBER
IS
BEGIN
RETURN ( TRUNC( in_date ) - TRUNC_TO_WEEK_START( in_date ) ) + 1;
END;
FUNCTION WEEK_OF_YEAR(
in_date IN DATE
) RETURN NUMBER
IS
BEGIN
RETURN TRUNC(
(
in_date
-
TRUNC_TO_WEEK_START(
TRUNC( TRUNC_TO_WEEK_START( in_date ), 'YYYY' ) + 6
)
) / 7
) + 1;
END;
FUNCTION TO_CHAR_WEEK(
in_date IN DATE
) RETURN VARCHAR2
IS
BEGIN
RETURN TO_CHAR( TRUNC_TO_WEEK_START( in_date ), 'FMYYYY' )
|| '-W' || TO_CHAR( WEEK_OF_YEAR( in_date ), 'FM00' )
|| '-' || DAY_OF_WEEK( in_date );
END;
END;
/
Query 1:
SELECT value,
CHANGE_WEEK_START.TO_CHAR_WEEK( value ) AS week,
TO_CHAR( value, 'DAY' ) AS day
FROM (
SELECT TRUNC( SYSDATE, 'YYYY' ) + LEVEL - 1 AS value
FROM DUAL
CONNECT BY LEVEL <= 14
)
Results:
| VALUE | WEEK | DAY |
|----------------------|------------|-----------|
| 2018-01-01T00:00:00Z | 2017-W52-7 | MONDAY |
| 2018-01-02T00:00:00Z | 2018-W01-1 | TUESDAY |
| 2018-01-03T00:00:00Z | 2018-W01-2 | WEDNESDAY |
| 2018-01-04T00:00:00Z | 2018-W01-3 | THURSDAY |
| 2018-01-05T00:00:00Z | 2018-W01-4 | FRIDAY |
| 2018-01-06T00:00:00Z | 2018-W01-5 | SATURDAY |
| 2018-01-07T00:00:00Z | 2018-W01-6 | SUNDAY |
| 2018-01-08T00:00:00Z | 2018-W01-7 | MONDAY |
| 2018-01-09T00:00:00Z | 2018-W02-1 | TUESDAY |
| 2018-01-10T00:00:00Z | 2018-W02-2 | WEDNESDAY |
| 2018-01-11T00:00:00Z | 2018-W02-3 | THURSDAY |
| 2018-01-12T00:00:00Z | 2018-W02-4 | FRIDAY |
| 2018-01-13T00:00:00Z | 2018-W02-5 | SATURDAY |
| 2018-01-14T00:00:00Z | 2018-W02-6 | SUNDAY |
回答2:
Us, Europeans, have Monday as the first day of the week. Therefore, if you set NLS_TERRITORY to a country in Europe (such as United Kingdom, or - why not - Croatia), you'll get "1" as the result. Here's a short demo:
SQL> ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM';
Session altered.
SQL> select to_char(sysdate, 'D') european from dual;
E
-
1
SQL>
SQL> ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
Session altered.
SQL> select to_char(sysdate, 'D') american from dual;
A
-
2
SQL> ALTER SESSION SET NLS_TERRITORY = 'CROATIA';
Session altered.
SQL> select to_char(sysdate, 'D') croatian from dual;
C
-
1
SQL>
[EDIT] Custom function that returns day of the week:
SQL> create or replace function f_day_1 (par_date in date default sysdate)
2 return varchar2
3 is
4 begin
5 return case to_char(par_date, 'FmDay', 'nls_date_language = english')
6 when 'Monday' then 7
7 when 'Tuesday' then 1
8 when 'Wednesday' then 2
9 when 'Thursday' then 3
10 when 'Friday' then 4
11 when 'Saturday' then 5
12 when 'Sunday' then 6
13 end;
14 end;
15 /
Function created.
SQL> -- today
SQL> select f_day_1 from dual;
F_DAY_1
------------------------------------------------------------------------------
7
SQL> -- tomorrow is Tuesday
SQL> select f_day_1 (date '2018-01-16') from dual;
F_DAY_1(DATE'2018-01-16')
------------------------------------------------------------------------------
1
SQL>
回答3:
With this PL/SQL block you get all possible days of week start:
BEGIN
FOR aLang IN (SELECT * FROM V$NLS_VALID_VALUES WHERE parameter = 'TERRITORY' ORDER BY VALUE) LOOP
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = '''||aLang.VALUE||'''';
DBMS_OUTPUT.PUT_LINE(aLang.VALUE || ' -> ' || TO_CHAR(TRUNC(SYSDATE, 'D'), 'Day'));
end loop;
end;
ALBANIA -> Monday
ALGERIA -> Saturday
AMERICA -> Sunday
ARGENTINA -> Monday
AUSTRALIA -> Monday
AUSTRIA -> Monday
AZERBAIJAN -> Monday
BAHRAIN -> Saturday
BANGLADESH -> Friday
BELARUS -> Monday
BELGIUM -> Monday
BRAZIL -> Sunday
BULGARIA -> Monday
CANADA -> Sunday
CATALONIA -> Monday
CHILE -> Monday
CHINA -> Sunday
CIS -> Monday
COLOMBIA -> Sunday
COSTA RICA -> Sunday
CROATIA -> Monday
CYPRUS -> Monday
CZECH REPUBLIC -> Monday
CZECHOSLOVAKIA -> Monday
DENMARK -> Monday
DJIBOUTI -> Saturday
ECUADOR -> Monday
EGYPT -> Saturday
EL SALVADOR -> Sunday
ESTONIA -> Monday
FINLAND -> Monday
FRANCE -> Monday
FYR MACEDONIA -> Monday
GERMANY -> Monday
GREECE -> Monday
GUATEMALA -> Sunday
HONG KONG -> Sunday
HUNGARY -> Monday
ICELAND -> Monday
INDIA -> Sunday
INDONESIA -> Monday
IRAQ -> Saturday
IRELAND -> Monday
ISRAEL -> Sunday
ITALY -> Monday
JAPAN -> Sunday
JORDAN -> Saturday
KAZAKHSTAN -> Monday
KOREA -> Sunday
KUWAIT -> Saturday
LATVIA -> Monday
LEBANON -> Saturday
LIBYA -> Saturday
LITHUANIA -> Monday
LUXEMBOURG -> Monday
MACEDONIA -> Monday
MALAYSIA -> Sunday
MAURITANIA -> Saturday
MEXICO -> Monday
MOROCCO -> Saturday
NEW ZEALAND -> Monday
NICARAGUA -> Monday
NORWAY -> Monday
OMAN -> Saturday
PANAMA -> Sunday
PERU -> Sunday
PHILIPPINES -> Sunday
POLAND -> Monday
PORTUGAL -> Sunday
PUERTO RICO -> Sunday
QATAR -> Saturday
ROMANIA -> Monday
RUSSIA -> Monday
SAUDI ARABIA -> Saturday
SERBIA AND MONTENEGRO -> Monday
SINGAPORE -> Sunday
SLOVAKIA -> Monday
SLOVENIA -> Monday
SOMALIA -> Saturday
SOUTH AFRICA -> Sunday
SPAIN -> Monday
SUDAN -> Saturday
SWEDEN -> Monday
SWITZERLAND -> Monday
SYRIA -> Saturday
TAIWAN -> Sunday
THAILAND -> Sunday
THE NETHERLANDS -> Monday
TUNISIA -> Saturday
TURKEY -> Monday
UKRAINE -> Monday
UNITED ARAB EMIRATES -> Saturday
UNITED KINGDOM -> Monday
UZBEKISTAN -> Monday
VENEZUELA -> Sunday
VIETNAM -> Sunday
YEMEN -> Saturday
YUGOSLAVIA -> Monday
You see, there is no territory where first day of week is Tuesday, so you have to build your own function. Solution from Littlefoot should work well.
回答4:
Here is a solution I derived on my own with 3 Views for 2015th year and can be configured to run dynamically for any year.
--To calculate all days of one complete year.
CREATE OR REPLACE VIEW CUSTOM_DAYS_AND_WEEKS
AS
SELECT DAYS.*,
CASE
WHEN ((EXTRACT(YEAR FROM DAYS.WEEK_START) <>EXTRACT(YEAR FROM DAYS.WEEK_END))
AND (((TRUNC(WEEK_END,'YYYY')-1)-WEEK_START)>=3))
THEN TO_CHAR(WEEK_START,'YYYY')
WHEN ((EXTRACT(YEAR FROM DAYS.WEEK_START) <>EXTRACT(YEAR FROM DAYS.WEEK_END))
AND (WEEK_START-(TRUNC(WEEK_START,'YYYY'))>=3))
THEN TO_CHAR(WEEK_END,'YYYY')
ELSE TO_CHAR(WEEK_START,'YYYY')
END AS YEAR_NO
FROM
(SELECT "A1"."DD" "DD",
TO_CHAR("A1"."DD",'DY') "DAY",
DECODE(TO_CHAR("A1"."DD",'DY'),'TUE', "A1"."DD",NEXT_DAY("A1"."DD" -7, 'TUESDAY')) WEEK_START,
DECODE(TO_CHAR("A1"."DD",'DY'),'MON',"A1"."DD",NEXT_DAY("A1"."DD" , 'MONDAY')) WEEK_END
FROM
(SELECT DD+(level-1) "DD"
FROM
(SELECT TO_DATE('01012015','DDMMYYYY') "DD" FROM "SYS"."DUAL" "A2"
)
CONNECT BY (DD+(level-1))< TO_DATE('01012016','DDMMYYYY')
) "A1"
) DAYS ;
--To group all weeks current year and next(if any in end of year)
CREATE OR REPLACE VIEW CUSTOM_WEEKS AS
SELECT T.YEAR_NO,
T.WEEK_START,
T.WEEK_END,
SUM(1) S
FROM CUSTOM_DAYS_AND_WEEKS T WHERE YEAR_NO>=2015
group by T.YEAR_NO, T.WEEK_START, T.WEEK_END
ORDER BY 1, 2;
--week start date, end date and week number
CREATE OR REPLACE VIEW CUSTOM_WEEKS_NO AS
SELECT T.YEAR_NO,
T.WEEK_START,
T.WEEK_END,
DECODE (MOD(ROWNUM,(SELECT COUNT(*)+1 FROM CUSTOM_WEEKS T WHERE T.YEAR_NO=2015)),0,1,MOD(ROWNUM,(SELECT COUNT(*)+1 FROM CUSTOM_WEEKS T WHERE T.YEAR_NO=2015))) WEEK_NO
FROM CUSTOM_WEEKS T;
-- TO check the created Views
SELECT * FROM CUSTOM_DAYS_AND_WEEKS;
SELECT * FROM CUSTOM_WEEKS;
--week all dates of year with week start date, end date and week number
SELECT D_W.*, W.WEEK_NO
FROM CUSTOM_DAYS_AND_WEEKS D_W,
CUSTOM_WEEKS_NO W
WHERE D_W.WEEK_START=W.WEEK_START
AND D_W.WEEK_END=W.WEEK_END ORDER BY DD DESC;
