Oracle Time function

1.sysdate returns the current date.

2.add_months (D1,N1) returns a new date based on the date D1 plus N1 months.
Select Sysdate,add_months (sysdate,3) Hz from dual;

3.last_day (D1) Returns the date D1 the last day of the month on which the date is located.
Select Last_day (sysdate) Hz from dual;

4.months_between (D1,D2) returns the number of months between the date D1 and the D2 of the date. Returns a positive number if d1>d2, or a negative number if d1<d2.
Select Sysdate,months_between (sysdate,to_date (' 2006-01-01 ', ' Yyyy-mm-dd ')) from dual;


5.new_time (DT1,C1,C2) gives the time dt1 the date and time in the C1 time zone corresponding to the C2 time zone. DT1 date type, return date type
Select To_char (sysdate, ' yyyy.mm.dd hh24:mi:ss ') Bj_time,to_char (New_time (sysdate, ' PDT ', ' GMT '), ' yyyy.mm.dd Hh24:mi: SS ') Los_angles from dual;


6.round (D1[,C1]) gives date D1 The first day of the period after the period (parameter C1) is rounded (similar to the value rounding meaning)
Select Sysdate then date,
round (sysdate) date of last 0 points,
round (sysdate, ' Day '), last Sunday,
round (sysdate, ' month ') at the beginning of recent month,
round (sysdate, ' Q ') date of the latest quarter,
round (sysdate, ' year ') recent beginning date from dual;


7.next_day (D1[,C1]) returns the date D1 in the next week, the Day of the week (parameter C1)
D1 Date Type, C1 is the character type (parameter), C1 default is J (i.e. the current date); C1 corresponds to: Monday, Tuesday, Wednesday ... Sunday
Select Sysdate then date,
Next_day (sysdate, ' Monday ') next Monday,
Next_day (sysdate, ' Tuesday ') next Tuesday,
Next_day (sysdate, ' Wednesday ') next Wednesday,
Next_day (sysdate, ' Thursday ') next Thursday,
Next_day (sysdate, ' Friday ') next Friday,
Next_day (sysdate, ' Saturday ') next Saturday,
Next_day (sysdate, ' Sunday ') next Sunday from dual;


8.extract (c1 from D1) Date/time D1, parameter (C1) value
SELECT sysdate from DUAL; --1 May-January-18 (month-date)
SELECT Systimestamp from DUAL; --1 May-January-18 11.20.50.210000000 am +08:00 (month, day and minute)

Select
Extract (hour from timestamp ' 2001-2-16 2:38:40 ') hours,
Extract (minute from timestamp ' 2001-2-16 2:38:40 ') minutes,
Extract (second from timestamp ' 2001-2-16 2:38:40 ') seconds,
Extract (Day from timestamp ' 2001-2-16 2:38:40 '),
Extract (month from timestamp ' 2001-2-16 2:38:40 ') months,
Extract (year from timestamp ' 2001-2-16 2:38:40 ')
from dual;

Select
Extract (Day from date ' 2001-2-16 '),
Extract (month from date ' 2001-2-16 ') months,
Extract (year from date ' 2001-2-16 ')
from dual;


9.localtimestamp returns the date and time in a session
Select Localtimestamp from dual;


10.current_timestamp to return the current date in the current session's time zone with the timestamp with date zone data type
Select Current_timestamp from dual;


11.current_date returns the current date in the current session's time zone
Select current_date from dual;

12.dbtimezone return time zone


1) To_char (value, ' format ') converts the value to the specified string format, commonly used on dates
Select To_char (sysdate, "Yyyy-mm-dd Hh:mm:ss") from dual;
Select To_char (sysdate, ' yyyy ') from dual; Get the year of the time
Select To_char (sysdate, ' mm ') from dual; Get the month of the time
Select To_char (sysdate, ' DD ') from dual; Get the day of the time
Select To_char (sysdate, ' hh24 ') from dual; Get time of 24 o'clock
Select To_char (sysdate, ' mi ') from dual; Get the minutes of the time
Select To_char (sysdate, ' SS ') from dual; Gets the seconds of the time


2) Returns the first day of a period for which the specified date is located
--trunc (D1[,C1])
Select Trunc (sysdate) from dual;--no parameters, return date 0 o ' Day
Select Trunc (sysdate, ' Day ') from dual;--return date of the week Sunday
Select Trunc (sysdate, ' month ') from dual;--return date of the month
Select Trunc (sysdate, ' Q ') from dual;--Returns the date of the season
Select Trunc (sysdate, ' year ') from dual;--return date


3) Days of difference between dates
Select To_date (' 08/06/2018 ', ' mm/dd/yyyy ')-to_date (' 07/01/2017 ', ' mm/dd/yyyy ') from dual;

4) Number of months between dates, function: Months_between
--months_between (DATE1,DATE2)
-If the day of the two dates is the same, or if the two days are the last day of the month, then the returned result is an integer. Otherwise, the returned result will contain a fractal part (calculated in 31 days for January)
Select Months_between (to_date (' 01/31/2015 ', ' mm/dd/yyyy '), to_date (' 12/31/2014 ', ' mm/dd/yyyy ') "months" from DUAL; --1 (Same as "day" in two dates)
Select Months_between (to_date (' 02/28/2015 ', ' mm/dd/yyyy '), to_date (' 12/31/2014 ', ' mm/dd/yyyy ') "months" from DUAL; --2 (These two days are the last day of the month respectively)

Select Months_between (to_date (' 01/01/2015 ', ' mm/dd/yyyy '), to_date (' 12/31/2014 ', ' mm/dd/yyyy ') "months" from DUAL; --0.0322580645161290322580645161290322580645
Select Floor (Months_between (to_date (' 01/01/2015 ', ' mm/dd/yyyy '), to_date (' 12/31/2014 ', ' mm/dd/yyyy ')) "months" from   DUAL; --0

5) Number of years of difference between dates
The number of years is calculated by calculating the number of moon and then dividing by 12;
Select Floor(Months_between (to_date (' 01/01/2015 ', ' mm/dd/yyyy '), to_date (' 12/31/2014 ', ' mm/dd/yyyy '))/12 "months" from DUAL;

Oracle Time function