An in-depth understanding of Oracle Date-time timestamp _oracle

1, the character type turns into timestamp

Copy Code code as follows:

Select To_timestamp (' January-October-08 07.46.41.000000000 a.m. ', ' Dd-mon-yy hh:mi:ss.ff am ')
from dual;

2, timestamp turn to date type

Copy Code code as follows:

Select CAST (To_timestamp (' January-October-08 07.46.41.000000000 a.m. ', ' Dd-mon-yy hh:mi:ss.ff am ') as date) Timestamp_to_date
from dual;

3, Date type turn into timestamp

Copy Code code as follows:

Select CAST (sysdate as timestamp) date_to_timestamp
from dual;

4, get the timestamp format of the system time

Copy Code code as follows:

Select Systimestamp from dual;

5, two date dates are subtracted from the number of days, and the two timestamp date subtracted from the full month and a minute seconds

Copy Code code as follows:

Select Systimestamp-systimestamp from dual;
Select Sysdate-sysdate from dual;

Note: So, timestamp to calculate the two date interval of how many seconds, to use a function conversion.
6. The To_char function supports date and timestamp, but Trunc does not support timestamp data types.
7, timestamp only support the second decimal point after six digits.

Copy Code code as follows:

Select To_char (Systimestamp, ' yyyymmdd hh24:mi:ssxff6 ') from dual;

Note: Ssxff6 can also be ssxff7, SSXFF8, SSXFF9, but only to 6 effective.
8, get the system time statement:

Copy Code code as follows:

SELECT Sysdate,systimestamp,to_char (Systimestamp, ' yyyymmdd hh24:mi:ssxff6 ') from dual;