Create a date master table in the Oracle database

The date functions provided by Oracle can meet all the daily operations on the date. Generally, we often encounter date fields when designing table structures. If the business needs to summarize the data of the week where the given date is located, and summarize the data of each week in the month where the given date is located, it will be complicated to write SQL statements directly.

Therefore, I directly create a date master table in the data, and calculate the statistical fields that may be used first, you only need to connect to the table to obtain the desired result.

The following describes how to create a table and how to generate data. You can add new fields to meet different requirements. You can create related indexes as needed!

We also welcome you to discuss better methods! Do not hesitate to advise!

1. Create a date primary table

1. Create table DATE_MAIN
2. (
3. DATE_TIME DATE,
4. DAY_OF_WEEK INTEGER,
5. WEEK_OF_YEAR INTEGER,
6. MONTH_OF_YEAR INTEGER,
7. WEEK_OF_MONTH INTEGER,
8. YEAR_AND_MONTH CHAR (7 ),
9. FIRST_DAY_OF_WEEK DATE,
10. LAST_DAY_OF_WEEK DATE,
11. FIRST_DAY_OF_WEEK_OF_MONTH DATE,
12. LAST_DAY_OF_WEEK_OF_MONTH DATE
13. )

2. Insert Date data

1. Declare
2. I integer;
3. Begin_dateChar(10 );
4. Begin
5. Begin_date: ='2017-01-01';
6. FOR I IN 0 .. 3650
7. LOOP
8. Execute IMMEDIATE
9. 'Insert INTO date_main (Date_Time)
10. VALUES (: X)'Using to_date (begin_date ,'YYYY-MM-DD ') + I;
11. End loop;
12. End;

3. Set the values of related fields

1. Update DATE_MAIN
2. SET month_of_year = to_number (to_char (date_time,'Mm')),
3. Week_of_year = to_number (to_char (date_time,'Aw')),
4. Day_of_week = to_number (to_char (date_time,'D'));
6. -- Set Monday to the first day of each week
7. UPDATE date_main SET day_of_week = day_of_week-1;
8. UPDATE date_main SET day_of_week = 7
9. WHERE day_of_week = 0;

4. Set the monthly week

1. Declare
2. Current_year CHAR (4 );
3. Current_month INT;
4. Current_week INT;
5. Temp_year CHAR (4 );
6. Temp_month INT;
7. WeekOfMonth INT;
8. DayOfWeek INT;
9. Begin
10. -- Test statements here
11. Current_year: ='123';
12. Current_month: =-1;
13. Current_week: =-1;
15. For x in (
16. SELECT to_char (date_time,'Yyyymmdd') Dat
17. , Month_of_year
18. , Week_of_year
19. , Day_of_week
20. , Week_of_month
21. FROM date_main s
22. Order by s. date_time
23. )
24. LOOP
25. Temp_year: = substr (X. dat, 1, 4 );
27. IF temp_year <> current_year THEN -- Cross-year
28. Current_year: = temp_year;
29. Current_month: = X. month_of_year;
30. Current_week: = X. week_of_year;
31. DayOfWeek: = X. day_of_week;
32. WeekOfMonth: = 1;
33. ELSE -- same year
34. Temp_month: = X. month_of_year;
35. IF temp_month <> current_month THEN -- Cross-month
36. WeekOfMonth: = 1;
37. Current_month: = temp_month;
38. ELSE -- same month
39. IF current_week <> X. week_of_year THEN
40. Current_week: = X. week_of_year;
41. WeekOfMonth: = weekOfMonth + 1;
42. End if;
43. End if;
44. End if;
46. EXECUTE IMMEDIATE
47. 'Update date_main'
48. |'Set week_of_month =:'
49. |'Where date_time = to_date (: B ,''Yyyymmdd'')'USING weekOfMonth, X. DAT;
50. End loop;
51. End;

5. Set the first and last days of the week.

1. -- Set the first day of each week
2. UPDATE Date_main
3. SET first_day_of_week = trunc (date_time,'Dd')-To_char (date_time-1,'D') + 1;
5. -- Set the last day of each week
6. UPDATE Date_main
7. SET last_day_of_week = first_day_of_week + 6;
9. UPDATE Date_main
10. SET (first_day_of_Week_of_Month, LAST_day_of_week_of_Month) = (
11. SELECT mind, maxd FROM
12. (
13. Select min (date_time) MIND, MAX (date_time) MAXD, year_and_month, week_of_month
14. FROM date_main group by year_and_month, week_of_month
15. ) B
16. Where a. Year_And_Month = B. year_and_month and a. week_of_month = B. week_of_month
17. );