How to convert timestamp in oracle

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.

How to convert timestamp in Oracle

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!