Forum Discussion

allensmith81's avatar
allensmith81
Boss
5 years ago

Understanding conversation.last_posting_activity_time (Community APIv2)

Hi all,

 

Looking for some insight here and unfortunately the documentation merely describes this constraint not what it actually does.

First some history:

We extract allot of metrics from the community and push it into our own PowerBI dashboard, this makes it easier to compare category performance vs other categories, boards vs boards and share this content out with the stakeholders.

The SQL database behind this is getting a little unwieldy and so we are looking to make some optimizations.

Specifically we are looking for all depth 0 posts that have been posted or edited since we last exported the body of those posts.

Does anyone have any insight into what exactly results in updating of the value conversation.last_posting_activity_time?

When a post is first published, does conversation.last_posting_activity_time equal post_time?

Sorry if this is documented somewhere, I am not finding it.

  • luk's avatar
    luk
    Boss
    5 years ago

    allensmith81 I believe that sentence from the API docs describes pretty much what it does =):

    Filters messages by the larger of these two date fields: the last post time (equivalent to the conversation.last_post_time) or the last time the topic message was published (the last time a non-draft edit was made).

    Link to Docs

    but yeah, there is not much more than that, although not sure if there is more needed... Your problem might be, that you are not really interested in the "last_post_time" part for your purposes (wich is basically if there was a reply to a topic), so you would probably be interested ONLY in "post_time" and "last_edit_time" of the topic, right?

    You can get those with a query like this:

     

    SELECT post_time, current_revision.last_edit_time FROM messages WHERE depth = 0

     

    hope it helps!

    EDIT: Here's what I can tell from my own research into this

    - post_time is equal conversation.last_posting_activity_time when there were no edits made to the initial post
    - conversation.last_posting_activity_time is updated when the INITIAL post is edited/updated
    - conversation.last_posting_activity_time is updated when a new comment to the initial post is made
    - conversation.last_posting_activity_time is updated when a new reply to a comment is posted
    - conversation.last_posting_activity_time is NOT updated when a comment (e.g. not the initial post) is EDITED
    - conversation.last_posting_activity_time is NOT updated when a kudo is given to the initial post
    - conversation.last_posting_activity_time is NOT updated when a kudo is given to a comment
    - conversation.last_posting_activity_time is NOT updated when a tag is added to the initial post (after it has been posted)
    - conversation.last_posting_activity_time is NOT updated when a tag is added to a comment (after it has been posted)
    - conversation.last_posting_activity_time is NOT updated when a comment is deleted