node.getRevisionCreationTime equivalent in views This question is somewhat related to Comparing node creation and modification dates in twig I asked earlier.

For the website in question, we agreed to the following semantics of the various dates related to a node (in our case, blog posts):


Creation date: The date the note was created.
Changed date: The date when any of the node's modifiable settings were changed.
First publication: The date the node was first published (using Drupal module Publication Date).
Publication date: The date a new revision of the node has been created - in other words, the current revision's date.


In rendering the content, all is well when using node.getRevisionCreationTime for the publication date in the relevant twig templates and display modes.

However, I what certain blocks and views to order the blog posts by publication date as defined above.

I fail to find the right settings when defining the view - I find the Views user interface incredibly complicated and not intuitive at all. In my (perhaps naive) assumption, using the Content Revision field Changed or Authored on as sufficient, but I suspect that that doesn't always relates to the current revision.

See the attached screenshot on how I have defined the view:

UPDATE: An example:

The above view (with some filtering on example name) gives the following result:


Taking a screenshot when editing the node I get this:


So far, so good: Node creation, publication and changed times all match. But look at the "Revision created" and "Revision changed" dates - they match the corresponding node times!

Checking the node's revisions, I see that the current revision's date (which date exactly? Created? Changed? Something else?) is 12 June 2018 - but nowhere to be found in my view - see screenshot:


The revision date 12 June 2018 is the date I want to fetch as the "Publication date" explained above.

How do I do that in a view?

Please help!

TIA,
Michel
I found the answer eventually:

The crucial thing to do was to built the query starting with content revisions rather content. Rather unintuitively - creating a relationship the other way around does NOT get you to where you need to get in this situation!

To create a view listing the node's title, creation date, changed date, first publication, and publication date (the creation date of the current revision, follow these steps, and then add/remove as required:


Create a view showing content revisions of type all as follows, creating a page for easier review:

Remove the field "Content revision: Changed (Changed)" as this is not what we are interested in.
Add a relationship to Content as seen in the screenshot below:

This is one of the #DrupalWTF moments - where's the crucial documentation that allows me to select the correct relationship!?
There are apparently two RDBMS foreign key relationships you can use to join revisions and content: 

a. Via content revision foreign key to the id of the node this revision belongs to - this is the first content relationship, and

b. Via revision id referenced as foreign key from the content table as current node - this is the second content relationship shown above in the screenshot.


For whatever reason you cannot reach the creation date of a revision when joining via the first content relationship, #Drupal, #WTF?

Require the relationship for all results, and change the administrative title if you wish:



Add a filter criterion to show only blog posts
The relationship shows revisions for all content types, so we need to filter for content type blog post (Yours may be different). This is pretty straightforward, see the following screenshots:

a. Select the filter criterion:


b. Configure the filter:

Add the various date related fields
This is repetitive; to add the fields select them all in the "Add field" form, and configure them all the same way:


(Content) Authored on,
(Content) Changed,
(Content) Published on,
(Content revision) Revision create time



Here are the screenshots:

and


Et voila, all fields present as required.

Now you (I) can go to town for formatting and sorting.