Create a date master table in the Oracle database

Source: Internet
Author: User

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'));
  5. -- Set Monday to the first day of each week
  6. UPDATE date_main SET day_of_week = day_of_week-1;
  7. UPDATE date_main SET day_of_week = 7
  8. 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;
  14. For x in (
  15. SELECT to_char (date_time,'Yyyymmdd') Dat
  16. , Month_of_year
  17. , Week_of_year
  18. , Day_of_week
  19. , Week_of_month
  20. FROM date_main s
  21. Order by s. date_time
  22. )
  23. LOOP
  24. Temp_year: = substr (X. dat, 1, 4 );
  25. IF temp_year <> current_year THEN -- Cross-year
  26. Current_year: = temp_year;
  27. Current_month: = X. month_of_year;
  28. Current_week: = X. week_of_year;
  29. DayOfWeek: = X. day_of_week;
  30. WeekOfMonth: = 1;
  31. ELSE -- same year
  32. Temp_month: = X. month_of_year;
  33. IF temp_month <> current_month THEN -- Cross-month
  34. WeekOfMonth: = 1;
  35. Current_month: = temp_month;
  36. ELSE -- same month
  37. IF current_week <> X. week_of_year THEN
  38. Current_week: = X. week_of_year;
  39. WeekOfMonth: = weekOfMonth + 1;
  40. End if;
  41. End if;
  42. End if;
  43. EXECUTE IMMEDIATE
  44. 'Update date_main'
  45. |'Set week_of_month =:'
  46. |'Where date_time = to_date (: B ,''Yyyymmdd'')'USING weekOfMonth, X. DAT;
  47. End loop;
  48. 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;
  4. -- Set the last day of each week
  5. UPDATE Date_main
  6. SET last_day_of_week = first_day_of_week + 6;
  7. UPDATE Date_main
  8. SET (first_day_of_Week_of_Month, LAST_day_of_week_of_Month) = (
  9. SELECT mind, maxd FROM
  10. (
  11. Select min (date_time) MIND, MAX (date_time) MAXD, year_and_month, week_of_month
  12. FROM date_main group by year_and_month, week_of_month
  13. ) B
  14. Where a. Year_And_Month = B. year_and_month and a. week_of_month = B. week_of_month
  15. );

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.