Conver unix epoch time fields in datetime stamp

  1. alexcol

    alexcol Premium Member

    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Goood afternoon,i need your help please

    Ive got this data from Autosys tables:

    Code (SQL):
    SELECT job_name, last_start, last_end, STATUS, next_start FROM MDBADMIN.ujo_jobst;
    The problem is that many of the date time fields in the Autosys database tables are stored in UNIX epoch time so this is the output

    Code (SQL):
    oscl_fa_23_generacufe_2                                        |1580061672|1580061764|         4|         0
    coscl_fa_23_generacufe_3                                        |1580061672|1580061860|         4|         0
    coscl_fa_23_generacufe_4                                        |1580061671|1580061849|         4|         0
    coscl_fa_26_cufe                                                |1580343037|1580343500|         4|         0
    coscl_fa_26_generacufe                                          |1580343041|1580343100|         4|         0
    coscl_fa_26_generacufe_2                                        |1580343041|1580343333|         4|         0
    coscl_fa_26_generacufe_3                                        |1580343041|1580343394|         4|         0
    coscl_fa_26_generacufe_4                                        |1580343041|1580343500|         4|         0
    coscl_fa_05_cufe                                                |1581260188|1581260434|         4|         0
    coscl_fa_05_generacufe                                          |1581260192|1581260314|         4|         0
    coscl_fa_05_generacufe_2                                        |1581260192|1581260324|         4|         0
    coscl_fa_05_generacufe_3                                        |1581260192|1581260421|         4|         0
    coscl_fa_05_generacufe_4                                        |1581260193|1581260433|         4|         0
    coscl_pv_regulariza_ime_hurto                                   |1533270905|1533270906|         7|1533271200
    coscl_pv_envio_mail_robot_greta                                 |1581627603|1581627604|         4|1581629400
    coscl_ve_baja_imei_FS                                           |1581627604|1581627605|         4|1581629400
    coscl_Soporte_Roaming_USSD_Certicamaras                         |1581582601|1581582927|         4|1581669000
    coscl_pv_notifica_recargas_dep                                  |1551710910|1551710927|         7|         0
    pscl_pv_envio_mail_regularizacion_greta                         |1581598807|1581598809|         4|1581685200
    coscl_fa_generacufenociclo                                      |1581595212|1581608628|         4|1581681600
    pscl_pv_genera_archivo_portout_scl_greta                        |1581595211|1581595216|         4|1581681600
    coscl_fa_tfsfs_calculo_compensacion                             |1581498001|1581498011|         4|1581843600
    coscl_fa_tfsfs_validacion_archivos                              |1581480001|1581480006|         4|1581656400
    coscl_fa_tfsfs_ajustes_fullstack                                |1581566402|1581566607|         4|1581912000
    coscl_fa_tfsfs_historico                                        |1581624001|1581624003|         4|1581710400
    In the above output as you can see date fields are not readable, and i dont know how to convert to datetime stamps

    There is an utility in time0 that converts the epoch time passed to it with the -a option to a human friendly format


    Code (SQL):
    autosys@proauto01 :COL >time0 -a 1513122480

    CAUAJM_I_50097 External TIME: 12/12/2017 18:48:00
    1 So I need your help to convert these datetime data into a readable date format directly from the query?
    2 Secondly the status filed is a number, is there ant way to find out which table has the Description in order to join the description filed into the query ?

    ie 4 = Success 3= Failed

    I appreciate your help in advanced
     
  2. alexcol

    alexcol Premium Member

    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    I tried to convert last_start field but it failed:

    Code (SQL):
    SELECT job_name, to_char(last_start,'dd/mm/yyyy hh24:mi:ss') AS UI, last_end, STATUS, next_start FROM MDBADMIN.ujo_jobst
    Code (SQL):
    ERROR at line 1:
    ORA-01481: invalid NUMBER format model
    I appreciate your help in advanced
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,750
    Likes Received:
    382
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You should create a table and load it with this data:

    Code (SQL):
    SCOTT @ orcl > CREATE TABLE epoch(
      2  stuff    varchar2(40),
      3  t1    NUMBER,
      4  t2    NUMBER,
      5  STATUS    NUMBER,
      6  t3    NUMBER);

    TABLE created.

    SCOTT @ orcl >
    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_23_generacufe_2'                          ,1580061672,1580061764,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_23_generacufe_3'                          ,1580061672,1580061860,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_23_generacufe_4'                          ,1580061671,1580061849,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_26_cufe'                              ,1580343037,1580343500,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_26_generacufe'                          ,1580343041,1580343100,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_26_generacufe_2'                          ,1580343041,1580343333,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_26_generacufe_3'                          ,1580343041,1580343394,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_26_generacufe_4'                          ,1580343041,1580343500,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_05_cufe'                              ,1581260188,1581260434,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_05_generacufe'                          ,1581260192,1581260314,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_05_generacufe_2'                          ,1581260192,1581260324,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_05_generacufe_3'                          ,1581260192,1581260421,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_05_generacufe_4'                          ,1581260193,1581260433,          4,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_pv_regulariza_ime_hurto'                      ,1533270905,1533270906,          7,1533271200 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_pv_envio_mail_robot_greta'                      ,1581627603,1581627604,          4,1581629400 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_ve_baja_imei_FS'                          ,1581627604,1581627605,          4,1581629400 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_Soporte_Roaming_USSD_Certicamaras'                  ,1581582601,1581582927,          4,1581669000 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_pv_notifica_recargas_dep'                      ,1551710910,1551710927,          7,     0 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'pscl_pv_envio_mail_regularizacion_greta'                  ,1581598807,1581598809,          4,1581685200 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_generacufenociclo'                      ,1581595212,1581608628,          4,1581681600 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'pscl_pv_genera_archivo_portout_scl_greta'                  ,1581595211,1581595216,          4,1581681600 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_tfsfs_calculo_compensacion'                  ,1581498001,1581498011,          4,1581843600 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_tfsfs_validacion_archivos'                  ,1581480001,1581480006,          4,1581656400 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_tfsfs_ajustes_fullstack'                      ,1581566402,1581566607,          4,1581912000 FROM dual;

    1 ROW created.

    SCOTT @ orcl > INSERT
      2  INTO epoch
      3  SELECT 'coscl_fa_tfsfs_historico '                       ,1581624001,1581624003,          4,1581710400 FROM dual;

    1 ROW created.

    SCOTT @ orcl > commit;

    Commit complete.

    SCOTT @ orcl >
    Then you can query the table like this:

    Code (SQL):
    SCOTT @ orcl > ALTER SESSION SET nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

    SESSION altered.

    SCOTT @ orcl >
    SCOTT @ orcl > SELECT to_date('19700101','RRRRMMDD') + (t1/86400),
      2          to_date('19700101','RRRRMMDD') + (t2/86400),
      3          to_date('19700101','RRRRMMDD') + (t3/86400)
      4  FROM epoch;

    TO_DATE('19700101',' TO_DATE('19700101',' TO_DATE('19700101','
    -------------------- -------------------- --------------------
    26-JAN-2020 18:01:12 26-JAN-2020 18:02:44 01-JAN-1970 00:00:00
    26-JAN-2020 18:01:12 26-JAN-2020 18:04:20 01-JAN-1970 00:00:00
    26-JAN-2020 18:01:11 26-JAN-2020 18:04:09 01-JAN-1970 00:00:00
    30-JAN-2020 00:10:37 30-JAN-2020 00:18:20 01-JAN-1970 00:00:00
    30-JAN-2020 00:10:41 30-JAN-2020 00:11:40 01-JAN-1970 00:00:00
    30-JAN-2020 00:10:41 30-JAN-2020 00:15:33 01-JAN-1970 00:00:00
    30-JAN-2020 00:10:41 30-JAN-2020 00:16:34 01-JAN-1970 00:00:00
    30-JAN-2020 00:10:41 30-JAN-2020 00:18:20 01-JAN-1970 00:00:00
    09-FEB-2020 14:56:28 09-FEB-2020 15:00:34 01-JAN-1970 00:00:00
    09-FEB-2020 14:56:32 09-FEB-2020 14:58:34 01-JAN-1970 00:00:00
    09-FEB-2020 14:56:32 09-FEB-2020 14:58:44 01-JAN-1970 00:00:00
    09-FEB-2020 14:56:32 09-FEB-2020 15:00:21 01-JAN-1970 00:00:00
    09-FEB-2020 14:56:33 09-FEB-2020 15:00:33 01-JAN-1970 00:00:00
    03-AUG-2018 04:35:05 03-AUG-2018 04:35:06 03-AUG-2018 04:40:00
    13-FEB-2020 21:00:03 13-FEB-2020 21:00:04 13-FEB-2020 21:30:00
    13-FEB-2020 21:00:04 13-FEB-2020 21:00:05 13-FEB-2020 21:30:00
    13-FEB-2020 08:30:01 13-FEB-2020 08:35:27 14-FEB-2020 08:30:00
    04-MAR-2019 14:48:30 04-MAR-2019 14:48:47 01-JAN-1970 00:00:00
    13-FEB-2020 13:00:07 13-FEB-2020 13:00:09 14-FEB-2020 13:00:00
    13-FEB-2020 12:00:12 13-FEB-2020 15:43:48 14-FEB-2020 12:00:00
    13-FEB-2020 12:00:11 13-FEB-2020 12:00:16 14-FEB-2020 12:00:00
    12-FEB-2020 09:00:01 12-FEB-2020 09:00:11 16-FEB-2020 09:00:00
    12-FEB-2020 04:00:01 12-FEB-2020 04:00:06 14-FEB-2020 05:00:00
    13-FEB-2020 04:00:02 13-FEB-2020 04:03:27 17-FEB-2020 04:00:00
    13-FEB-2020 20:00:01 13-FEB-2020 20:00:03 14-FEB-2020 20:00:00

    25 rows selected.

    SCOTT @ orcl >
     
  4. alexcol

    alexcol Premium Member

    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Thank you very much for your support, ive go a couple of questions:

    ive got to insert abot 10.000 records to epoch table, because it´s a production database, how can i make sure there is enough space ?

    secondly, why you use from dual in the select setence?

    I appreaciateyour help i advanced
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,750
    Likes Received:
    382
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Download the data as a comma-separated file and use SQL*Loader to load a table. I used DUAL because I was selecting literal text from a copy-and-paste from your question. I can select any literal text from DUAL.
     
  6. alexcol

    alexcol Premium Member

    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Thanks you very much again for your help
     
  7. christhanah274

    christhanah274 Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    California
    I have the same issue, thanks