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

    Creation Date Conversion

    emily915005

      Hi,

       

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

       

      Jive Documentation

       

      Thanks,

      Emily

        • Re: Creation Date Conversion
          erkjones

          I am having the same problem

            • Re: Re: Creation Date Conversion
              mnevill

              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,

                      jiveanswer.messageid,

                      jivequestion.userid as "asker",

                      jiveanswer.userid as "answerer",

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

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

              jivequestion.resolutionstate,

              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)

                   

                  Description

                  =(((A1/60)/60)/24)

                  converts a Unix timestamp by converting the seconds to days

                   

                  +DATE(1970,1,1)

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

                   

                  +(-8/24)

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