"Go" Time types in Oracle conversion between date and timestamp

Source: http://www.cnblogs.com/java-class/p/4742740.html

the difference between date and timestamp

The date type is an Oracle-commonly used datetime variable, and his time interval is seconds. Two date type subtraction is a two-time interval, noting that the unit is "days". For example: Check the current distance from the opening of the London Olympics:

Select To_date ('2012-7-28 03:12:00','yyyy-mm-dd hh24:mi:ss') fromdual   

The result: 92.2472685185185 days, then you can calculate the interval you want according to the corresponding time! This result may be useful to programmers, and for those who want to see the results directly, this number is not very intuitive, so it leads to the timestamp type

Timestamp is a date-type extension that can be accurate to fractional seconds (fractional_seconds_precision), 0 to9, and 6 by default. Two timestamp subtract words, can not directly get the days of the book, but get,
How many days, how many hours, how many seconds, etc., for example: Also check the current distance from the opening of the London Olympic Games how long.

Select To_timestamp ('2012-7-28 03:12:00','yyyy-mm-dd hh24:mi:ss') fromdual  

The result: +000000092 05:51:24.032000000, a little intercept, you can get 92 days 5 hours, 51 minutes, 24 seconds, so users look more intuitive! But this number is not very intuitive for programmers, if you want a specific length of time, and the accuracy is not required to milliseconds, you can convert the timestamp type to date type, and then directly subtract.

queries the current system time and returns to the date data type. You can use the Sysdate function.

Sql> SELECT Systimestamp from DUAL;

Systimestamp
---------------------------------------------------------------------------
11-aug-18 01.55.07.311630 PM +08:00


queries the current system time and returns to the timestamp data type. You can use the Systimpstamp function.

Sql> SELECT sysdate from DUAL;

Sysdate
-------------------
2018-08-11 13:55:14



The conversion between date and timestamp can be done by

To_char to convert timestamp-->date:


Sql> Select To_date (To_char (Systimestamp, ' yyyy-mm-dd hh24:mi:ss '), ' Yyyy-mm-dd Hh24:mi:ss ') from dual
2;

To_date (To_char (SYS
-------------------
2018-08-11 13:56:27


Date-->timestamp:

Sql> Select To_timestamp (To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '), ' Yyyy-mm-dd Hh24:mi:ss ') from dual
2;

To_timestamp (To_char (sysdate, ' yyyy-mm-ddhh24:mi:ss '), ' Yyyy-mm-ddhh24:mi:ss '
---------------------------------------------------------------------------
11-aug-18 01.56.35.000000000 PM

Sql>

"Go" Time types in Oracle conversion between date and timestamp