3 Replies Latest reply on Jul 11, 2016 4:40 PM by Shinichiro Murakami

    Create A Date From the Number of Seconds

    Richard Kissel

      I cannot seem to find a Function that will convert the number of seconds to a Date.

      My Oracle 11gR2 database column stores the values of the Date in Seconds. I need to get the date that this number represents.

      How can I use the Calculated field to convert the seconds to an actual date?

       

       

       

      If I try using the Convert to "Date & Time" menu item I get the following Error:

      Oracle database error 1830: ORA-01830: date format picture ends before converting entire input string

       

      This is the Query:

      SELECT *

      FROM (SELECT "DS_APPLICATION"."ACTIVE" AS "ACTIVE",

        "DS_APPLICATION"."CALENDAR_ID" AS "CALENDAR_ID",

        "DS_APPLICATION"."CREATED_BY_USER" AS "CREATED_BY_USER",

        CAST((TO_TIMESTAMP(TO_CHAR(FLOOR("DS_APPLICATION"."DATE_CREATED" + 2415021)), 'J') + NUMTODSINTERVAL("DS_APPLICATION"."DATE_CREATED" - FLOOR("DS_APPLICATION"."DATE_CREATED"), 'DAY')) AS TIMESTAMP) AS "DATE_CREATED",

        "DS_APPLICATION"."DESCRIPTION" AS "DESCRIPTION",

        "DS_APPLICATION"."ENFORCE_COMPLETE_SNAPSHOTS" AS "ENFORCE_COMPLETE_SNAPSHOTS",

        "DS_APPLICATION"."GHOSTED_DATE" AS "GHOSTED_DATE",

        "DS_APPLICATION"."ID" AS "ID",

        "DS_APPLICATION"."LAST_MODIFIED" AS "LAST_MODIFIED",

        "DS_APPLICATION"."NAME" AS "NAME",

        "DS_APPLICATION"."NOTIFICATION_SCHEME_ID" AS "NOTIFICATION_SCHEME_ID",

        1 AS "Number of Records",

        "DS_APPLICATION"."SEC_RESOURCE_ID" AS "SEC_RESOURCE_ID",

        "DS_APPLICATION"."TEMPLATE_ID" AS "TEMPLATE_ID",

        "DS_APPLICATION"."TEMPLATE_VERSION" AS "TEMPLATE_VERSION",

        "DS_APPLICATION"."VERSION" AS "VERSION"

      FROM "UCDSCHEMA"."DS_APPLICATION" "DS_APPLICATION")

      WHERE ROWNUM <= 1000

       

       

      Any Help is Appreciated...Thank You, And Have A Nice Day

        • 1. Re: Create A Date From the Number of Seconds
          Shinichiro Murakami

          According to the link below, Java counts the time using a number of milliseconds since 01-01-1970. So it's not a surprise that this has three digits more than the Unix timestamp.

           

          java - Convert ORACLE NUMBER field to ORACLE DATE - Stack Overflow

           

          Then, the converting formula should be

          [datetime]

          date("1970,1,1")+int([Original]/1000)/(60*60*24)

          But please carefully verify on your data.

           

          Thanks,

          Shin

           

          9.2 attached.

          • 2. Re: Create A Date From the Number of Seconds
            Richard Kissel

            Thank You this was excellent solution...

             

            I actually had to use the below since my data actually comes from an Oracle DB

            date("1970,1,1")+int(ATTR([Date Created])/1000)/(60*60*24)

             

            I had to put the data into a Spreadsheet so I could put it into the question and I believe that is why your above solution did not work for me.

            FYI...When using your solution I received the following Error:

            Oracle database error 1861: ORA-01861: literal does not match format string

             

            SELECT (TRUNC(CAST(N'1970,1,1' AS DATE)) + (CASE WHEN 86400 = 0 THEN CAST(NULL AS BINARY_DOUBLE) ELSE ROUND(TRUNC((CASE WHEN 1000 = 0 THEN CAST(NULL AS BINARY_DOUBLE) ELSE "DS_APPLICATION"."DATE_CREATED" / 1000 END)),0) / 86400 END)) AS "Calculation_834573341312684032",
              "DS_APPLICATION"."NAME" AS "NAME",
              TO_NUMBER(TO_CHAR((TRUNC(CAST(N'1970,1,1' AS DATE)) + (CASE WHEN 86400 = 0 THEN CAST(NULL AS BINARY_DOUBLE) ELSE ROUND(TRUNC((CASE WHEN 1000 = 0 THEN CAST(NULL AS BINARY_DOUBLE) ELSE "DS_APPLICATION"."DATE_CREATED" / 1000 END)),0) / 86400 END)),'YYYY')) AS "yr:Calculation_834573341312684"
            FROM "UCDSCHEMA"."DS_APPLICATION" "DS_APPLICATION"
            GROUP BY (TRUNC(CAST(N'1970,1,1' AS DATE)) + (CASE WHEN 86400 = 0 THEN CAST(NULL AS BINARY_DOUBLE) ELSE ROUND(TRUNC((CASE WHEN 1000 = 0 THEN CAST(NULL AS BINARY_DOUBLE) ELSE "DS_APPLICATION"."DATE_CREATED" / 1000 END)),0) / 86400 END)),
              "DS_APPLICATION"."NAME",
              TO_NUMBER(TO_CHAR((TRUNC(CAST(N'1970,1,1' AS DATE)) + (CASE WHEN 86400 = 0 THEN CAST(NULL AS BINARY_DOUBLE) ELSE ROUND(TRUNC((CASE WHEN 1000 = 0 THEN CAST(NULL AS BINARY_DOUBLE) ELSE "DS_APPLICATION"."DATE_CREATED" / 1000 END)),0) / 86400 END)),'YYYY'))

            • 3. Re: Create A Date From the Number of Seconds
              Shinichiro Murakami

              Thank you for the additional info. 

              As long as the concept is same, should be O.K.

               

              Thanks,

              Shin