[ORACLE] processing of oracle time objects

[ORACLE] processing of oracle time objects

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 ('August 13, November 20, 2015 ', 'yyyy "year" mm "month" dd "day" hh24 "Hour" mi "Minute" ss "second "'));

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

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

For example,

Insert into foo_7 values (to_date ('August 13, November 20, 2015 ', 'yyyy "year" mm "month" dd "day" hh24 "Hour" mi "Minute" ss "second "'));

 

(4) The Last_day () function is used to calculate the last day of the month.

For example,

Select to_char (last_day (sysdate), 'yyyy-mm-dd') from dual;

(5) Use months_between to calculate the two time intervals of several months

For example,

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

(6) use the extract () function to obtain the year, month, and day of the time object. The format is extract (year [mounth] [day] from time object). For example:

Select extract (year from sysdate) from dual;

(7) use the trunc function to change the hour, minute, and second 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 make a trade-off (after 12 hours, enter 1 );

For example,

Select to_char (round (sysdate), 'yyyy-mm-dd hh24: mi: ss') from dual;

(9) least (date1, date2) returns a relatively old time object

 

1.2 oracle timestamp () timestamp object, more accurate.

(1) create a timestamp field

For example,

Create table foo_8 (time timestamp );

(2) Use the system constant systimestamp object to insert a record,

For example,

Insert into foo_8 values (systimestamp );

(3) display the Time of the Timestramp field using the to_char object

Displayed to seconds

Select to_char (time, 'yyyy-dd-mm hh24: mi: ss') "time" from foo_8;

13:15:31, 2015-25-01

Display to any precision in seconds

SQL> select to_char (time, 'yyyy-dd-mm hh24: mi: ssssssss') "time" from foo_8;

Display to milliseconds, microseconds, etc.

Select to_char (time, 'yyyy-dd-mm hh24: mi: ss. ss') "time" from foo_8;