2 Replies Latest reply: Feb 20, 2014 12:55 PM by Clever Anjos RSS

    SQL Select & Epoch time questions...

      Hi,

      I have a database that I am using an ODBC connection to connect to.  I can connect to it and pull data.  Now i am doing some data modeling and trying to reduce the amount of data and runtime via applying a where clause on a date field.  The date field is in epoch time.  I would like to set a variable to convert the epoch time to regular time in the script so if I have to use it again, I can simply refer to the variable name.  I have the script below, but it returns 'Not a valid month', ORA-01843 .  I have tried several variations such as

      $(SubmitDate) > '01/01/2013'

      $(SubmitDate) > '01/01/2013 12:00:00'

      $(SubmitDate) > '01/01/2013 12:00:00 AM'

       

      No dice.  Could someone help me out with syntax here?

       

      ODBC

       

      (connection string)

       

      set SubmitDate = (to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (("SUBMIT_DATE"- 4*3600)/(60*60*24)));

      SQL SELECT "ASSIGNED_GROUP",
      ASSIGNEE,
      "FULL_NAME",
      "INCIDENT_NUMBER",
      STATUS,
      "SUBMIT_DATE",
      (to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (("SUBMIT_DATE"- 4*3600)/(60*60*24))) as SubmitDateTime

      FROM ITSMTST."HPD_HELP_DESK_CLASSIC"

      where $(SubmitDate) > '1/1/2013';

       

       

        • Re: SQL Select & Epoch time questions...
          Massimo Grossi

          I don't have your table, but this for me works

          In submit_date column I have a constant 915148798, just to try your script

          Hope it helps you.

           

           

          set SubmitDate = (to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + ((submit_date - 4*3600)/(60*60*24)));

           

          SQL

          select

          $(SubmitDate),

          to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') 

          from dwh_dl.a

          where

          $(SubmitDate) > to_date('01/01/1950', 'MM/DD/YYYY')

          ;

          • Re: SQL Select & Epoch time questions...
            Clever Anjos

            It´s a very commom problem while using ORACLE.

            It´s a conversion problem

            Oracle expects a date into YYYY-MM-DD format OR you tranform your string using TO_DATE function

            I would to

            SQL SELECT "ASSIGNED_GROUP",
            ASSIGNEE,
            "FULL_NAME",
            "INCIDENT_NUMBER",
            STATUS,
            "SUBMIT_DATE",
            (to_date('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + (("SUBMIT_DATE"- 4*3600)/(60*60*24))) as SubmitDateTime

            FROM ITSMTST."HPD_HELP_DESK_CLASSIC"

            where $(SubmitDate) > '2013-01-01';