Oracle time and date operation method summary page 2/2

There are many functions about dates in oracle, such as:
1. add_months() is used to increase or decrease some months from a date value.
date_value:=add_months(date_value,number_of_months)
example:
SQL> select add_months(sysdate,12) "Next Year" from dual;
Next Year
----------
13-November-04
SQL> select add_months(sysdate,112) "Last Year" from dual;
Last Year
----------
13-March -13
SQL>
2. Current_date() returns the current date in the time zone
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;
SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00 13-November-03
SQL> alter session set time_zone='-11:00'
2 /
The session has been changed.
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 December-03 04.59.13.668000 pm -11:
00
SQL>
3. Current_timestamp() returns the current date in the time zone using the timestamp with time zone data type
timestamp_with_time_zone_value:=current_timestamp([timestamp_precision])
SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
+08:00 13-November-03 11.56.28.160000 AM +08:
00
SQL> alter session set time_zone='-11:00'
2 /
The session has been changed.
SQL> select sessiontimezone,current_timestamp from dual;
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 December-03 04.58.00.243000 pm -11:
00
SQL>
4. dbtimezone() returns the time zone
varchar_value:=dbtimezone
SQL> select dbtimezone from dual;
DBTIME
------
-07:00
SQL>
5. Extract() finds the field value of the date or interval value
date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) "This Month" from dual;
This Month
----------
11
SQL> select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;
3 Years Out
-----------
2006
SQL>
6. Last_day() returns the date of the last day of the month containing the date parameter
date_value:=last_day(date_value)
SQL> select last_day(date'2000-02-01') "Leap Yr?" from dual;
Leap Yr?
----------
29-February -00
SQL> select last_day(sysdate) "Last day of this month" from dual;
Last day o
----------
30-November-03
SQL>
7. localtimestamp() returns the date and time in the session
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
----------------------------
13-November-03 12.09.15.433000
afternoon
SQL> select localtimestamp,current_timestamp from dual;
LOCALTIMESTAMP CURRENT_TIMESTAMP
---------------------------- ------------------------------------
13-November-03 12.09.31.006000 13-November-03 12.09.31.006000 pm +08:
00 pm
SQL> alter session set time_zone='-11:00';
The session has been changed.
SQL> select localtimestamp,to_char(sysdate,'DD-MM-YYYY HH:MI:SS AM') "SYSDATE" from dual;
LOCALTIMESTAMP SYSDATE
---------------------------- ------------------------
December-03 05.11.31.259000 13-11-2003 12:11:31 pm
afternoon
SQL>
8. months_between() determines the number of months between two dates
number_value:=months_between(date_value,date_value)
SQL> select months_between(sysdate,date'1971-05-18') from dual;
MONTHS_BETWEEN(SYSDATE,DATE'1971-05-18')
----------------------------------------
389.855143
SQL> select months_between(sysdate,date'2001-01-01') from dual;
MONTHS_BETWEEN(SYSDATE,DATE'2001-01-01')
----------------------------------------
34.4035409
SQL>
9. Given a date value, next_day() returns the date value that the date indicated by the second parameter appears for the first time (the name string of the corresponding day should be returned)
illustrate:
Single-line date function
A single-line date function operates on data data types. Most of them have parameters of data data type, and most of them return values ​​of data data type.
add_months(,)
Returns the result after date d plus i month. i can make any integer. If i is a decimal, the database converts it implicitly into an integer and will cut off the part after the decimal point.
last_day()
Function returns the last day of the month containing the date d
months_between(,)
Returns the number of months between d1 and d2. If the dates of the days of d1 and d2 are the same, or both make the last day of the month, an integer will be returned, otherwise the result returned will contain a score.
new_time(,,)
d1 is a date data type. When the date and time in zone tz1 are d, it returns the date and time in time zone tz2. tz1 and tz2 strings.
next_day(,)
Returns the first day of the condition given by dw after the date d, the dow specifies a day of the week using the language given in the current session, and the returned time component is the same as the time component of d.
select next_day(''01-jan-2000'',''monday'') "1st monday",next_day(''01-nov-2004'',''tuesday'')+7 "2nd tuesday") from dual;1st monday 2nd tuesday03-jan-2000 09-nov-2004
round([,])
Round the date d in the format specified by fmt, and fmt is a string.
syadate
The function has no parameters and returns the current date and time.
trunc([,])
Returns the date d of the unit specified by fmt.
Single-line conversion function
A single-line conversion function is used to operate on multiple data types and convert between data types.
chartorwid()
c Make a string, the function converts c to rwid data type.
select test_id from test_case where rowid=chartorwid(''aaaa0saacaaaaliaaa'')
convert(,[,])
C-tailed string, dset and set are two character sets. The function converts the string c from the set character set to the dset character set. The default setting of set is the character set of the database.
hextoraw()
x is a hexadecimal string, and the function converts hexadecimal x to raw data type.
rawtohex()
x is a raw data type string, and the function converts the raw data class to a hexadecimal data type.
rowidtochar()
The function converts the rowid data type to the char data type.
to_char([[,)
x is a data or number data type. The function converts x to the char data type specified in the format fmt. If x is a date nlsparm=nls_date_language controls the language used for the returned month and day. If x is the number nlsparm=nls_numeric_characters is used to specify the decimal and thousandths of decimals, as well as the currency symbol.
nls_numeric_characters ="dg", nls_currency="string"
to_date([,[,)
c represents a string, and fmt represents a string in a special format. Returns the language used for c and nlsparm displayed in fmt format. The function converts the string c to the date data type.
to_multi_byte()
c represents a string, and the function converts the truncated characters of c into multi-byte characters.
to_number([,[,)
c represents a string, fmt represents a string in a special format, and the return value of the function is displayed in the format specified by fmt. nlsparm represents language, and the function will return the number represented by c.
to_single_byte()
Converts many byte characters in the string c into equivalent single-byte characters. This function is only used if the database character set contains both single-byte and multi-byte characters.
Oracle's operations on time/date
1. Date and time interval operation
Current time minus 7 minutes
select sysdate,sysdate - interval '7' MINUTE from dual
Current time minus 7 hours
select sysdate - interval '7' hour from dual
Current time minus 7 days
select sysdate - interval '7' day from dual
Current time minus July time
select sysdate,sysdate - interval '7' month from dual
Current time minus 7 years
select sysdate,sysdate - interval '7' year from dual
Time interval multiplied by a number
select sysdate,sysdate - 8 *interval '2' hour from dual
2. Date to character operation
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual
Refer to the relevant documentation of oracle (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)
3. Character to date operation
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
The specific usage is similar to the to_char above.
4. Use of trunk/ROUND functions
select trunc(sysdate ,'YEAR') from dual
select trunc(sysdate ) from dual
select to_char(trunc(sysdate ,'YYYY'),'YYYY') from dual
There are millisecond data types
--Return to the current time, year, month, day, hour, minute, second, millisecond
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
--Return the milliseconds of the current time, and you can specify the accuracy after the second (maximum = 9)
select to_char(current_timestamp(9),'MI:SSxFF') from dual;
6. Calculate the time of the program running (ms)