'convert oracle NUMBER to Timestamp

I have a table with a column in oracle where the column has a datatype of NUMBER(10,0).

In this column some one has stored a timestamp data.(Eg.3613470611,3613470666 etc..). I need to make a query which to show the time difference between this timestamp and the current_timestamp.

select (cast(current_TIMESTAMP as date)- date '1900-01-01')*24*60*60 - columen1 from mytable;

(column1 is type of NUMBER(10,0)) This query works fine.

The problem is, I am not able to convert the result to a date so that I can see the month, the date, the hour,

  • The result I got is like this : 74873174, 74873713 (I guess this not a timestamp, but a number)
  • I like to convert like this format: December 12, 2016 11:05:10

So, please help !!

sqloracle


Solution 1:[1]

I guess you need Epoch Converter, something like that

SELECT to_date('01-JAN-1970','dd-mon-yyyy')+(1526357743/60/60/24) from dual

Replace 1526357743 with epoch.

More details see the link https://www.epochconverter.com/

Solution 2:[2]

your number might be linux format numbers. simple answer for conversion is :

Select  to_date('1900-01-01','YYYY-MM-DD') + numtodsinterval(yourNumber,'SECOND') from dual

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Andrey Khmelev
Solution 2

Related Questions