9.0: FEATURE: Add created/last modified dates to Nodes

[bwaidelich]

-c-m--------------- (content stream user1 (n))
-----c'-m'--------- (content stream review (n'))
-----------c''-m''- (content stream live (n''))
 1 2 3  4  5   6    (t)

Legend:

• c created event
• m modified event
• n the node
• t point in time

At t1:

• n
  • created: t1
  • originalCreated: t1
  • lastModified: null
  • originalLastModified: null

At t2:

• n
  • created: t1
  • originalCreated: t1
  • lastModified: t2
  • originalLastModified: t2

At t6:

• n:
  • created: t1
  • originalCreated: t1
  • lastModified: t2
  • originalLastModified: t2
• n':
  • created: t3
  • originalCreated: t1
  • lastModified: t4
  • originalLastModified: t2
• n'':
  • created: t5
  • originalCreated: t1
  • lastModified: t6
  • originalLastModified: t2

[bwaidelich]

With that we could implement sth like:

function lastPublicationDate(): \DateTimeImmutable {
  return $this->lastModifiedAt ?? $this->createdAt;
}

And $node->lastModifiedAt === null === no changes yet

[bwaidelich]

The projection logic could be sth like:

creating event

• createdAt: $eventEnvelope->recordedAt
• originalCreatedAt: $eventEnvelope->event->metadata->get('initiatingTimestamp')
• lastModifiedAt: null
• originalLastModifiedAt: null

modifying event

• lastModifiedAt: $eventEnvelope->recordedAt
• originalLastModifiedAt: $eventEnvelope->event->metadata->get('initiatingTimestamp')

Note: We need to check whether the metadata is correctly kept when rebasing/publishing events

[ahaeslich]

FYI: I created an issue (neos/neos-ui#3434) so we don't forget to customize the output in the UI afterwards. Perhaps there are other places in the UI, but this one immediately came to mind.

[bwaidelich]

This needs a follow-up that fixes timezone handling.

i.e. when server and db timezone is not equal we currently get invalid timestamps.
When the contentGraph projection is not replayed it even might lead to exceptions:

Previous exception: Exception in line 182 of /var/www/html/Packages/Libraries/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php: An exception occurred while executing 'INSERT INTO cr_default_p_graph_node (relationanchorpoint, nodeaggregateid, origindimensionspacepoint, origindimensionspacepointhash, properties, nodetypename, classification, created, originalcreated, lastmodified, originallastmodified) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["8d0e7528-ff71-42ec-a13b-c84f7ceef36b", "ac488801-2080-43d2-bbdc-3eb33c56f547", "{\"language\":\"en_US\"}", "046d33049fe2ab3d1c3c54ca5340186f", "{\"caption\":{\"value\":\"\",\"type\":\"string\"},\"image\":{\"value\":{\"__flow_object_type\":\"Neos\\\\Media\\\\Domain\\\\Model\\\\Image\",\"__identifier\":\"c418b835-19b7-4720-a0ac-dd7ac196e4b1\"},\"type\":\"Neos\\\\Media\\\\Domain\\\\Model\\\\ImageInterface\"},\"alternativeText\":{\"value\":\"testtext2\",\"type\":\"string\"}}", "Neos.Demo:Content.Image", "regular", "-0001-11-30 00:00:00", "-0001-11-30 00:00:00", "2023-04-03 16:10:15", "2023-04-03 16:10:15"]:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '-0001-11-30 00:00:00' for column `db`.`cr_default_p_graph_node`.`created` at row 1

(@dlubitz thanks for raising)

[bwaidelich]

i.e. when server and db timezone is not equal we currently get invalid timestamps.

This is not about timezones, but: https://stackoverflow.com/questions/10450644/how-do-you-explain-the-result-for-a-new-datetime0000-00-00-000000

We could change the default value of the creation columns to 1970-01-01 00:00:00 but that also feels awkward.. @skurfuerst do you have an opinion on this one?

[kitsunet]

Why would we ever have a created timestamp of 0000.... ? Even IF we import from the old repo and can't take over from there for whatever reason using the timestamp of that import is surely a better information than 0000 ?

[bwaidelich]

@kitsunet if we import from the old repo, we already use the current timestamp.
This issue is about the migration that adds the new database columns if you already had an existing CR before , see https://github.com/neos/neos-development-collection/pull/4102/files#diff-566b83337a43be38f60d89822ccf6fd7a7f177b3ca6c1d50c5d2101be34528bbR53-R62

[skurfuerst]

@bwaidelich this can be closed, no?

[mhsdesign]

I have a question how the new time stamps compare to the 8.3's.
Especially for the new flow-query people might need guidance how to migrate: #5015
I created this little table with what i think are equivalents. Is this correct?

8.3 getter and documentation	9.0 getter and documentation
_creationDateTime / getCreationDateTime()	timestamps->created Date and time a node was created in its content stream
_lastModificationDateTime / getLastModificationDateTime()	timestamps->lastModified Date and time a node was last modified in its content stream
_lastPublicationDateTime / getLastPublicationDateTime() Date of last publication or null if the node was not published yet	timestamps->originalLastModified Date and time a node was last modified in its original content stream