Well, I am going to convert the epoch into a normal datetime in oracle sqldeveloper, I wrote the below code, but it says "missing expression" My code:

SELECT to_date(CreationDate, 'yyyymmdd','nls_calendar=persian')  EpochDate/24/60/60
from table1

My table1:

I don not know what is wrong!

CodePudding user response：

If the CreationDate is a Date and EpochDate is a Varchar you can try this:

SELECT to_date(to_char(CreationDate, 'yyyymmdd','nls_calendar=persian'),'yyyymmdd')  
       EpochDate/24/60/60 as newDate
from table1

or:

select to_date(to_char(CreationDate, 'yyyymmdd','nls_calendar=persian'),'yyyymmdd')  
       numtodsinterval(EpochDate,'SECOND') as newDate
 from dual

CodePudding user response：

Let's show you how in an example

Demo data

SQL> create table c1 ( id number generated always as identity, date_test date) ;

Table created.

SQL> insert into c1 ( date_test ) values ( sysdate ) ;

1 row created.

SQL> insert into c1 ( date_test ) values ( sysdate-365 ) ;

1 row created. 

SQL> insert into c1 ( date_test ) values ( sysdate-4000 ) ;

1 row created. 

SQL> insert into c1 ( date_test ) values ( sysdate-7200 ) ;
    
1 row created.

SQL> commit ;

Commit complete.

Now, let's add a column called epoch, and a small function to make easier to update the column.

SQL> alter table c1 add epoch number ;

Table altered.

SQL> create or replace function date_to_unix_ts( PDate in date ) return number is
   l_unix_ts number;
begin
   l_unix_ts := ( PDate - date '1970-01-01' ) * 60 * 60 * 24;
   return l_unix_ts;
end; 
/

Function created

We update the column epoch with the real epoch date based on the timestamp field

SQL> update c1 set epoch=date_to_unix_ts (date_test) ;

4 rows updated.

SQL> select * from c1 ;

        ID DATE_TEST                                            EPOCH
---------- ---------------------------------------- -----------------
         1 2021-09-15 12:25:25                             1631708725
         2 2020-09-15 12:25:25                             1600172725
         3 2010-10-03 12:25:25                             1286108725
         4 2001-12-29 12:25:26                             1009628726


SQL> select to_char(to_date('1970-01-01','YYYY-MM-DD')   numtodsinterval(EPOCH,'SECOND'),'YYYY-MM-DD HH24:MI:SS') from c1 ;

TO_CHAR(TO_DATE('19
-------------------
2021-09-15 12:25:25
2020-09-15 12:25:25
2010-10-03 12:25:25
2001-12-29 12:25:26