2 Replies Latest reply on Mar 19, 2010 9:57 AM by john.tarbutton

    Analytics - Oracle Install - Not Timestamps?

      I have been working off of a PostgreSQL analytics install to date, but just turned on our Jive Analytics Oracle install which is the platform we will ultimately be working within.  I was surprised to see when I went into the Analytics Oracle database that all the _TS fields (ie ACTIVITY_TS) were defined as DATE and not TIMESTAMP within Oracle.  I am hoping there is just some sort of setting I missed or some other way to get them to actually come across as a true timestamp.


      Is there a way to get timestamps within the Oracle Analytics install?  Or is the DATE datatype a forced datatype for TIMESTAMP values on the Oracle platform?


      As this has a large impact on the work we are planning for currently a response is appreciated.




        • Re: Analytics - Oracle Install - Not Timestamps?



          I've just looked into this and confirmed this to be true - which is quite a shock. There was a point in time when the column was correctly being created as a TIMESTAMP. Therefore I think a bug was introduced somewhere in our release cycle which caused DATEs to be used instead.


          I am filing a P1 bug to get this resolved for future releases. There will also be an upgrade task to fix current installations.


          In the meantime a manual remedy is quite simple - you can issue an ALTER STATEMENT to modify the column back to timestamp.


          Please let me know if this works.

            • Re: Analytics - Oracle Install - Not Timestamps?

              Thanks Mark.  That is great to know that it is in fact a bug and not a forced feature to have the dates.  Since I posted I did go ahead and test the approach of forcing the a creation_ts column to timestamp in Oracle and then generated some test data and reran the ETL and it came over correctly (sample below).


                                            GROUP_NAME                           CREATION_DATE                                   MODIFICATION_DATE

              1002       0              TestETLGroup2                            19-MAR-10 PM        19-MAR-10

              1001       0              TestETLTimestampGroup             19-MAR-10 PM         19-MAR-10


              I left modification_date as date and altered creation_date as timestamp and it came over correctly.


              Thanks for the quick response and pushing this one to get a fix in.


              I will modify the columns as a workaround in our dev environment but I would be hesitant to run alter table commands in an environments above that, so I will keep an eye out for the upgrade task.