PL/SQL date and time functions and operations

Internal Storage Format:
Century, year, month, day, hour, minute, second
The default format is DD-MON-RR.
SYSDATE returns the current system time.
Select sysdate from dual;
Mathematical operations on dates
SELECT (SYSDATE-HIRE_DATE)/7 from tablename where rownum;
Number Column
ADD_MONTHS (date, x) function, returns the value of DATE after X months. X can be any integer. If the daily component of the result month is less than the daily component of the Month of DATE, the last day of the result month is returned. If it is not smaller than, the result is the same as the daily component of DATE. The time component is the same.
CURRENT_DATE returns the current DATE of the session time zone as the DATE type. This function is similar to SYSDATE, except that SYSDATE is the time zone of the session.
CURRENT_TIMESTAMP [(precision)] returns the current date of the session time zone in the timestamp with timezone type. If precision is specified, it indicates the precision of the number of seconds returned. The default value is 6.
DBTIMEZONE returns the time zone of the database.
LAST_DAY (date) specifies the date of the last day of the month in which the date is located. This function can be used to determine the number of days in the month.
LOCALTIMESTAMP [(precision)] returns the current date of the session time zone in the TIMESTAMP type. If precision is specified, it indicates the precision of the number of seconds returned. The default value is 6.
MONTHS_BETWEEN (date 1, date earlier than the current date) the number of months between two dates (calculated in the smallest unit of day ). Returns the number of months of the difference. If the daily components of date1 and date2 are the same, or both dates are the last day of the month, the returned result is an integer. Otherwise, the returned result contains a score, which is calculated as 31 days a month.
NEW_TIME (d, zone1, zone2) function. When the date and time in Zone zone1 are D, the date and time in Zone zone2 are returned.
The return type is DATE. Zone1 and zone2 are character strings, and other time zones can be in ORACLE9I
Query V $ TIMEZONE_NAMES.
NEXT_DAY (date, day of the week) specifies the date of a day in the next seven days that will be met after the date.
ROUND (date, 'month/year') is rounded to get a new date. The retention period is Month and year.
SESSIONTIMEZONE returns the time zone of the current session. The return type is a character string of the time zone offset or time zone title. If specified, the format is the same as that in the alter session Statement.
SYS_EXTRACT_UTC (datetime) returns the Time from the provided DATETIME in UTC (Coordinated Universal Time.
DATETIME must contain a time zone.
SYSTIMESTAMP returns the current date and time with timezone. When used in Distributed SQL statements, the date and time of the local database are returned.
TRUNC (date, 'month/year') truncation
TZ_OFFSET (timezone) returns the offset between the provided timezone and UTC with a character string. Timezone can be specified as the time zone name or the offset in the format of '+/-HH: Hi. You can also use the SESSIONTIMEZONE and DBTIMEZONE functions. The return format is '+/-HH: Hi '.
Character string Time Zone
AST Atlantic Standard
ADT Atlantic crash hours
BST white Order Standard
BDT
CST Central Standard
CDT central failover
EST Eastern Standard
EDT East region time
GMT Mean Time
HST Alaska Hawaii Standard
HDT Alaska Hawaii
MST Mountain Standard
MDT Mountain hours
NST new Finland Standard
PST Pacific Standard
PDT Pacific Daylight Saving Time
YST YuKon Standard
YDT YuKon timeout
Date and Time Arithmetic
Return type
Result
D1-d2 NUMBER
Returns the number of days between D1 and D2. This value is a numerical value, and its decimal part represents the fraction of a day.
Dt1-dt2 INTERVAL
Returns the interval between DT1 and DT2.
I1-i2 INTERVAL
Returns the gap between i1 and i2.
D1 + d2 N/
Invalid -- only two dates can be subtracted.
Dt1 + dt2 N/
Invalid -- only two dates can be subtracted.
I1 + i2 INTERVAL
Returns the sum of i1 and i2.
D1 + n DATE
Add N days to D1 as the DATE type. N can be a real number, which contains a fraction of a day.
D1-n DATE
Returns the DATE type from D1 minus N days. N can be a real number, which contains a fraction of a day.
Dt1 + i1 DATETIME
Returns the sum of DT1 and i1.
Dt1-i1 DATETIME
Returns the gap between DT1 and i1.
I1 * n INTERVAL
Returns the Npower of i1.
I1/n INTERVAL
Returns the value of dividing I1 by N.
Note in the table:
D1 and D2 are date values;
DT1 and DT2 are date and time values;
I1 and I2 are the time interval values;
N indicates the numeric value.