Incorrect date and time in the relationship with a person in the target lists

[SinergiaCRM]

Issue

When deleting a Target List that has related records, the value of the date_modified field of the prospect_lists_prospects table is modified, however the DB stores the local date, and not the UTC date and time as it should.

This happens because the field update is being done directly with SQL using the NOW() function.

Possible Fix

Use the value of UTC_TIMESTAMP() from php instead of the NOW() function in:

SuiteCRM/modules/ProspectLists/ProspectList.php

Line 430 in 5f8062c

$query = "UPDATE prospect_lists_prospects SET deleted = 1, date_modified = NOW() WHERE prospect_list_id = '{$id}' AND deleted = 0";

Steps to Reproduce the Issue

1. Create a Target List
2. Add a person to the Target List
3. Check the date_modified field that appears in the prospect_lists_prospects table and note that the date and time is UTC.
4. Delete the Target List and note that the date_modified field has taken the local time and date

Context

No response

Version

7.14.4

What browser are you currently using?

Chrome

Browser Version

No response

Environment Information

PHP 7.4

Operating System and Version

Ubuntu 22.04