In-depth understanding of oracle datetime timestamp

1. Convert string type to timestamp
Copy codeThe Code is as follows:
Select to_timestamp ('01-October-08 07.46.41.000000000 AM ', 'dd-MON-yy hh: mi: ss. ff am ')
From dual;

2. Convert timestamp to date type
Copy codeThe Code is as follows:
Select cast (to_timestamp ('01-October-08 07.46.41.000000000 AM ', 'dd-MON-yy hh: mi: ss. ff am') as date) timestamp_to_date
From dual;

3. Convert the date type to timestamp
Copy codeThe Code is as follows:
Select cast (sysdate as timestamp) date_to_timestamp
From dual;

4. Obtain the system time in timestamp format
Copy codeThe Code is as follows:
Select distinct imestamp 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.
Copy codeThe Code is as follows:
Select systimestamp-policimestamp from dual;
Select sysdate-sysdate from dual;

Note: Therefore, timestamp must calculate the number of seconds between the two dates and use a function to convert them.
6. The to_char function supports date and timestamp, but trunc does not support the TIMESTAMP data type.
7. timestamp supports only six decimal places after the second.
Copy codeThe Code is as follows:
Select to_char (systimestamp, 'yyyymmdd hh24: mi: ssxff6') FROM dual;

Note: ssxff6 can also be ssxff7, ssxff8, or ssxff9, but only 6 is valid.
8. Statements for obtaining the system time:
Copy codeThe Code is as follows:
SELECT sysdate, systimestamp, to_char (systimestamp, 'yyyymmdd hh24: mi: ssxff6') FROM dual;