Time Processing in Oracle Database

Processing of time in Oracle Database:

4824 hours for HH24

Select to_char (sysdate, 'yyyy-MM-dd HH24: mi: ss') from dual;

Select to_date ('1970-01-01 13:14:20 ', 'yyyy-MM-dd HH24: mi: ss') from dual;

To_date () function

1. Description of date format parameters

D. day of the week

The name of DAY, which is filled with spaces to 9 characters.

Day of DD month

The day of the year in DDD

Short Name of DY day

Week of the Year of the iw iso Standard

Four-digit year of the iyyy iso Standard

YYYY four-digit year

Last three digits of YYY, YY, and Y years, two digits, one digit

HH hours, at 12 hours

HH24 hours, in 24 hours

MI score

SS seconds

MM Month

Abbreviated month of Mon

Full name of Month

W the week of the month

The day of the week in WW 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;

To_char () function

The following are number examples for the to_char function.

To_char (1210.73, '192. 9') wocould return '192. 7'

To_char (1210.73, '20140901') wocould return '20160301'

To_char (1210.73, '$9,999.00') wocould return '$1,210.73'

To_char (21, '20140901') wocould return '20160901'

The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in parallel combinations.

Parameter Explanation

YEAR Year, spelled out

YYYY 4-digit year

YYY

YY

Y Last 3, 2, or 1 digit (s) of year.

IYY

IY

I Last 3, 2, or 1 digit (s) of ISO year.

IYYY 4-digit year based on the ISO standard

The time processing in the Oracle database is explained here. It may not be very comprehensive. In the future, I will continue to explain this knowledge and hope to help you.