Home > Database > Oracle > body text

How to convert timestamp in oracle

WBOY
Release: 2022-01-26 15:50:40
Original
17831 people have browsed it

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.

How to convert timestamp in oracle

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;
Copy after login

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
Copy after login

3. Convert date type into timestamp

select cast(sysdateas timestamp) date_to_timestamp from loga_t
Copy after login

4. Get the system time in timestamp format

select systimestamp from loga_t
Copy after login

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;
Copy after login

2. Get the system time and return it into timestamp type, which can be as follows:

SQL> SELECT SYSTIMESTAMP FROM DUAL;
Copy after login

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
Copy after login

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
Copy after login

Or you can use the CAST function to convert, the example is as follows:

 SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template