Method: 1. Use the "to_timestamp()" function to convert the string into a timestamp; 2. Use the "to_date()" function to convert the timestamp into a date type; 3. Use the CAST function to convert the timestamp type to date. Convert.
The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.
1. Convert character type to timestamp
select to_timestamp(loga006,'dd-MON-yyhh:mi:ss.ff AM') from loga_t;
2. Convert timestamp to date type
select cast(to_timestamp(loga006,'dd-MON-yyhh:mi:ss.ff AM')as date) timestamp_to_date loga_t
3. Convert date type into timestamp
select cast(sysdateas timestamp) date_to_timestamp from loga_t
4. Get the system time in timestamp format
select systimestamp from loga_t
First let’s talk about the difference between date and timestamp.
As we all know, the date type can store month, year, day, century, hour, minute and second, and the common format is 'MM/DD/YYYY HH24:MI:SS' or 'MM-DD- YYYY HH24:MM:SS'.
As you can see, date can only be accurate to seconds, and you cannot see the time within seconds. It is sufficient for general time calculations, but for calculations with higher requirements such as time intervals, etc., date is a bit inadequate.
In order to solve the problem of insufficient granularity of the date type, ORACLE has extended the timestamp type, and the timestamp type introduces fractional seconds information.
2. Get the system date time and timestamp time
1. Get the system time and return it as date type, as follows:
SQL>SELECT SYSDATE FROM DUAL;
2. Get the system time and return it into timestamp type, which can be as follows:
SQL> SELECT SYSTIMESTAMP FROM DUAL;
3. Conversion between date type and timestamp type
1. date--》timestamp, the example is as follows:
SQL>select to_date(to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual
2 , timestamp --》date, the example is as follows:
SQL>select to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual
Or you can use the CAST function to convert, the example is as follows:
SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;
Similarly, you can also use the CAST function to convert date to timestamp .
Recommended tutorial: "Oracle Video Tutorial"
The above is the detailed content of How to convert timestamp in oracle. For more information, please follow other related articles on the PHP Chinese website!