Processing of Oracle Time objects "Oracle"

1.1 Use of Oracle date objects

(1) Create a date field

For example

CREATE TABLE foo_7 (d1 date);

(2) Use the to_date function to insert a date object. to_date (' string ', ' string format ')

For example

INSERT into foo_7 values (to_date (' November 20, 2015 13:20 10 seconds ', ' yyyy ' year "MM" month "DD" Day "Hh24" when "Mi" minutes "ss" Seconds "));

Or:'yyyy-mm-dd hh24:mi:ss'

(3) Use the to_char function to output a date object. To_char (Date object,' string format ')

For example

INSERT into foo_7 values (to_date (' November 20, 2015 13:20 10 seconds ', ' yyyy ' year "MM" month "DD" Day "Hh24" when "Mi" minutes "ss" Seconds "));

(4) last_day () function in order to calculate the last day of the month in which Otsuki Yue

For example

Select To_char (Last_day (sysdate), ' Yyyy-mm-dd ') from dual;

(5) use months_between to calculate two time intervals for a few months

For example

Select Months_between (sysdate,to_date (' 1989-05-05 ', ' yyyy-mm-dd '))/12 years from dual;

(6) Use the Extract () function to get the month and date of the time object in the format extract (Year[mounth][day] from time object), for example

Select Extract (year from sysdate) from dual;

(7) Use Trunc function bar to change to 00:00:00

For example

Select To_char (trunc (sysdate), ' Yyyy-mm-dd Hh24:mi:ss ') from dual;

(8) use the round function to trade-offs (after a few, then into 1);

For example

Select To_char (Round (sysdate), ' Yyyy-mm-dd Hh24:mi:ss ') from dual;

(9) least (DATE1,DATE2) returns more ancient time objects

1.2 Oracle Timestamp () timestamp object for more precision.

(1) create timestamp field

For example

CREATE TABLE Foo_8 (time timestamp);

(2) inserting a record with a system constant systimestamp object,

For example

INSERT into foo_8 values (Systimestamp);

(3) use to_char object to display the time of the timestramp field

Display to seconds

Select To_char (Time, ' yyyy-dd-mm hh24:mi:ss ') "Time" from Foo_8;

2015-25-01 13:15:31

Shows the seconds to any precision

Sql> Select To_char (Time, ' yyyy-dd-mm hh24:mi:sssssssss ') "Time" from Foo_8;

Display to milliseconds, microseconds, etc.

Select To_char (Time, ' yyyy-dd-mm hh24:mi:ss.ss ') "Time" from Foo_8;

Processing of Oracle Time objects "Oracle"