Oracle date-time timestamp Learning

1. Obtain the system time Statement (ssxff6 gets the six digits after the decimal point) 

 
Select sysdate, systimestamp, to_char (systimestamp, 'yyyymmdd hh24: MI: ssxff6'), to_char (systimestamp, 'yyyymmdd hh24: MI: Ss. ff6') from dual;
 

2. Convert string type to Timestamp

 
Select to_timestamp ('2017-09-14 12:52:42. 100', 'syyyy-mm-dd hh24: MI: Ss. ff') from dual;
 

3. Convert timestamp to date type 

 
Select cast (to_timestamp ('2017-09-14 12:52:42. 100', 'syyyy-mm-dd hh24: MI: Ss. ff') as date) timestamp_to_date from dual;
 

4. Convert the date type to Timestamp 

Select cast (sysdate as timestamp) date_to_timestamp from dual;
 

5. The date of two dates is the number of days, while the date of two timestamp is the complete year, month, day, hour, minute, second, decimal second. 

 select sysdate-sysdate, policimestamp-policimestamp from dual; select extract (day from Inter) * 24*60*60 + extract (hour from Inter) * 60*60 + extract (minute from Inter) * 60 + extract (second from Inter) "seconds" from (select to_timestamp ('2017-09-14 12:34:23. 281000000 ', 'yyyy-mm-dd hh24: MI: Ss. FF ')-to_timestamp ('2017-09-14 12:34:22. 984000000 ', 'yyyy-mm-dd hh24: MI: Ss. FF ') Inter from dual); select extract (second from to_timestamp ('2017-09-14 12:34:23. 281000000 ', 'yyyy-mm-dd hh24: MI: Ss. FF ')-extract (second from to_timestamp ('2017-09-14 12:34:22. 984000000 ', 'yyyy-mm-dd hh24: MI: Ss. FF ') from dual; 

 
Note: Therefore, timestamp must calculate the number of seconds between the two dates and use a function to convert them.
The to_char function supports date and timestamp, but trunc does not support the timestamp data type.

Note: This article Reprinted from: http://hulu26.iteye.com/blog/1171749