3 Replies Latest reply on Jan 13, 2014 2:39 PM by Caton Guilbault

    Creation Date Conversion




      I am trying to change the variable creationdDate in Activity table, found at the link below, into an actual date.  Any suggestions?


      Jive Documentation




        • Re: Creation Date Conversion

          I am having the same problem

            • Re: Re: Creation Date Conversion

              If I'm not mistaken you have to use the to_timestamp function in Postgres SQL.  Here is what it looks like in a query:


              SELECT  jivequestion.threadid,


                      jivequestion.userid as "asker",

                      jiveanswer.userid as "answerer",

              to_timestamp(jivequestion.creationdate/1000) as "creationdate",

              to_timestamp(jivequestion.resolutiondate/1000) as "resolutiondate",


              jiveanswer.helpfulanswer as "helpful",

              jiveanswer.correctanswer as "correct",

                      jiveuser.email as "answerer_e-mail",

                    jivequestion.containerid as "space_id",

                      jivecommunity.name as "space"

              FROM    jivequestion

              LEFT JOIN jiveanswer ON jiveanswer.threadid = jivequestion.threadid

              LEFT JOIN jiveuser ON jiveuser.userid = jiveanswer.userid

              LEFT JOIN jivecommunity ON jivecommunity.communityid = jivequestion.containerid

              ORDER BY jivequestion.threadid asc

                • Re: Creation Date Conversion
                  Caton Guilbault

                  You can expand on Matt's query with date parts as well:

                  date_part('year',to_timestamp(msg.creationdate/1000)) as "Year",

                    date_part('month',to_timestamp(msg.creationdate/1000)) as "Month",

                    date_part('day',to_timestamp(msg.creationdate/1000)) as "Day",

                    date_part('week',to_timestamp(msg.creationdate/1000)) as "Week",



                  Alternatively if you are using Excel this formula comes in real handy:


                  Unix time conversion formula for PST   =(((A2/60)/60)/24)+DATE(1970,1,1)+(-8/24)




                  converts a Unix timestamp by converting the seconds to days



                  adds the above result to the date value for January 1, 1970



                  adjusts the formula for the GMT offset (-8 for PST)