Summary of Oracle practical date functions

I. Example of tianhui
1. Yesterday's Data Summary
Startday> = to_char (trunc ($ startday-1), 'yyyymmdd ')
And startday <to_char (trunc ($ startday), 'yyyymmdd ')
($ Startday is the time scalar function trunc ($ startday-10) used to find the first day of the date)
The function to_char (trunc ($ startday-10), 'yyyymmdd') is used to convert the obtained date to the char type in the form of year, month, and day.
2. Data Summary for the last ten days
Startday> = to_char (trunc ($ startday-10), 'yyyymmdd ')
And startday <to_char (trunc ($ startday), 'yyyymmdd ')
($ Startday is the time scalar function trunc ($ startday-10) used to find the first day of the date)
The function to_char (trunc ($ startday-10), 'yyyymmdd') is used to convert the obtained date to the char type in the form of year, month, and day.
Example of monthly summary
1. One-month Data Summary
Startday> = to_char (trunc ($ startday, 'mm'), 'yyyymmdd ')
And startday <to_char (trunc (add_months ($ startday, 1), 'mm'), 'yyyymmdd ')
Note: $ startday is the time scalar function trunc ($ startday, 'mm') used to calculate the first day of the month where the date is located.
Function to_char (trunc ($ startday, 'mm'), 'yyyymmdd') is used to convert the first day of the month in which the date is obtained to the char type.
2. Data Summary for any period of time in a month
Calculate the summary of the four days from the fifth day of the month to the last day of the month.
Startday> = to_char (trunc ($ startday, 'mm') + 4, 'yyyymmdd ')
And startday <to_char (trunc (add_months ($ startday, 1), 'mm')-4, 'yyyymmdd ')
3. weekly summary examples
1. Weekly Data Summary
Startday> = to_char (trunc ($ startday, 'D') + 1, 'yyyymmdd ')
And startday <to_char (trunc ($ startday, 'D') + 8, 'yyyymmdd ')
Note: $ startday is the time scalar function trunc ($ startday, 'D') used to calculate the first day of the week where the date is located. It may be Sunday or Monday depending on the region.
Function to_char (trunc ($ startday, 'D') + 1, 'yyyymmdd') is used to convert the obtained date to the char type in the form of year, month, and day.
It indicates that the first day of a week is Sunday or Monday, depending on the nls settings of your database.
Select * from nls_session_parameters;
Alter session set NLS_TERRITORY = 'America ';
4. Hour summary example
1. Calculate the Data Summary of the previous N hours
Startday = to_char ($ startday, 'yyyymmdd') and
Starttime> = to_char (trunc ($ startday-N/24, 'hh24'), 'hh24') | '123' and
Starttime <to_char (trunc ($ startday, 'hh24'), 'hh24') | '123'
Note: $ startday is the time scalar function to_char ($ startday, 'yyyymmdd') used to convert a date to the char type in the form of year, month, and day.
The to_char (trunc (sysdate-N/24, 'hh24'), 'hh24') function is used to convert the obtained date to the char type in the 24-hour format.

 

From: http://deng947.javaeye.com/blog/205682