Oracle time/date operations

There are many date functions in oracle, such:

1,Add_months ()Used to increase or decrease the number of 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

----------

April 11-04

SQL> select add_months (sysdate, 112) "Last Year" from dual;

Last Year

----------

13-3-13

 

SQL>

2,Current_date ()Returns the current date in the current time zone.

Date_value: = current_date

SQL> column sessiontimezone for a15

SQL> select sessiontimezone, current_date from dual;

SESSIONTIMEZONE CURRENT_DA

-------------------------

+-11-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

---------------------------------------------------

--03 04.59.13.668000 pm-11:

00

SQL>

3,Current_timestamp ()Returns the current date in the current time zone as 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

---------------------------------------------------

+-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

---------------------------------------------------

--03 04.58.00.243000 pm-11:

00

SQL>

4,Dbtimezone ()Return Time Zone

Varchar_value: = dbtimezone

SQL> select dbtimezone from dual;

DBTIME

------

-07:00

SQL>

5,Extract ()Locate 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 that contains the date parameter.

Date_value: = last_day (date_value)

SQL> select last_day (date '2017-02-01 ') "Leap Yr? "From dual;

Leap Yr?

----------

29-2-00

SQL> select last_day (sysdate) "Last day of this month" from dual;

Last day o

----------

30-11-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-11-03 12.09.15.433000

Afternoon

SQL> select localtimestamp, current_timestamp from dual;

LOCALTIMESTAMP CURRENT_TIMESTAMP

----------------------------------------------------------------

13-11-03 12.09.31.006000 13-11-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

----------------------------------------------------

12-11-03 05.11.31.259000 13-11-2003 12:11:31 pm

Afternoon

SQL>

8,Months_between ()Determine the number of months between two dates

Number_value: = months_between (date_value, date_value)

SQL> select months_between (sysdate, date '2017-05-18 ') from dual;

MONTHS_BETWEEN (SYSDATE, DATE '2017-05-18 ')

----------------------------------------

389.855143

SQL> select months_between (sysdate, date '2017-01-01 ') from dual;

MONTHS_BETWEEN (SYSDATE, DATE '2017-01-01 ')

----------------------------------------

34.4035409

SQL>

9,Next_day ()Given a date value, return the date value that appears for the first time on the day indicated by the second parameter (the name string of the corresponding day should be returned)

Note:

Single Row date functions

A single-row date function operates on data types. The vast majority of data type parameters are available, and the vast majority of returned data types are also values.

10,Add_months (,)

Returns the result after date d plus I months. I can make any integer. If I is a decimal point, the database implicitly converts it to an integer, and the part after the decimal point is truncated.

11,Last_day ()

Returns the last day of the month containing the date d.

12,Months_between (,)

Returns the number of months between d1 and d2. If the date of d1 and d2 is the same, or the last day of the month is used, an integer is returned, otherwise, the returned result contains a score.

13,New_time (,,)

D1 is a date data type. When the date and time in Zone tz1 are d, the date and time in Zone tz2. Tz1 and tz2 are strings.

14,Next_day (,)

Returns the first day of the condition given by dow after the date d. dow specifies a day in a week using the language given in the current session, and returns the same time component as the time component of d.

Select next_day (''01-jan-2000 '', ''monday'')" 1st Monday ", next_day (''01-nov-2004'', ''tuesday '') + 7 "2 nd Tuesday") from dual; 1st Monday 2nd tuesday03-jan-2000 09-nov-2004

15,Round ([,])

Round the date d in the format specified by fmt, and fmt is a string.

16,Syadate

The function has no parameters. The current date and time are returned.

17,Trunc ([,])

Returns the date d of the unit specified by fmt.

Single Row Conversion Function

The single-row conversion function is used to operate on multiple data types and convert data types.

18,Chartorwid ()

C makes a string, and the function converts c to the rwid data type.

Select test_id from test_case where rowid = chartorwid (''aaaa0saacaaaaliaaa '')

19,Convert (, [,])

String at the end of c. dset and sset are two character sets. The function converts string c from the sset character set to the dset character set, and the sset is set to the database character set by default.

20,Hextoraw ()

X is a hexadecimal string. The function converts hexadecimal x to the raw data type.

21,Rawtohex ()

X is a raw data string. The function converts the raw data class to a hexadecimal data type.

22,Rowidtochar ()

The function converts the rowid data type to the char data type.

23,To_char ([[,)

X is a data or number data type. The function converts x to the char data type in the specified format of fmt. If x is the date nlsparm = nls_date_language, the language used to control the returned month and day. If x is the number nlsparm = nls_numeric_characters, it is used to specify the Separators for decimal places and kilobytes, as well as currency symbols.

Nls_numeric_characters = "dg", nls_currency = "string"

24,To_date ([,[,)

C Indicates a string, and fmt indicates a string in a special format. Returns the c language in the fmt Format. The nlsparm indicates the language used. The function converts string c to the date data type.

25,To_multi_byte ()

C Represents a string, and the function converts the truncation character of c to multi-byte characters.

26,To_number ([,[,)

C Indicates a string, fmt indicates a special string, and the function return value is displayed in the format specified by fmt. Nlsparm indicates the language, and the function returns numbers represented by c.

27,To_single_byte ()

Converts multiple characters in string c into equivalent single-byte characters. This function is used only when the database character set contains single-byte and multi-byte characters at the same time.

 

　　1. Date 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

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 operations

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 oracle documentation (ORACLE901DOC/SERVER.901/A90125/SQL _ELEMENTS4.HTM #48515)

　　3. Character-to-date operations

Select to_date ('1970-10-17 21:15:37 ', 'yyyy-mm-dd hh24: mi: ss') from dual

The usage is similar to that of to_char.

　　4. Use of the trunk/ROUND Function

Select trunc (sysdate, 'Year') from dual

Select trunc (sysdate) from dual

Select to_char (trunc (sysdate, 'yyyy'), 'yyyy') from dual

　　5. oracle has millisecond-level data types

-- Returns the current time year month hour minute second millisecond

Select to_char (current_timestamp (5), 'DD-MON-YYYY HH24: MI: SSxFF') from dual;

-- Returns the second millisecond of the current time. The precision after the second can be specified (max = 9)

Select to_char (current_timestamp (9), 'mi: SSxFF ') from dual;

　　6. computing program running time (MS)

Declare

Type rc is ref cursor;

Rochelle rc;

Rochelle dummy all_objects.object_name % type;

Rochelle start number default dbms_utility.get_time;

Begin

For I in 1000

Loop

Open l_rc

'Select object_name from all_objects '|

'Where object_id = '| I;

Fetch l_rc into l_dummy;

Close l_rc;

End loop;

Dbms_output.put_line

(Round (dbms_utility.get_time-l_start)/100, 2) |

'Seconds ...');

End;