@pfrancois-ACX are you planning to address the code review comments ?

Hello @dregad,

Sorry for the delay in my response, end/start of the year are always very busy.

Thanks for your insight regarding my resolution method that wasn't very elegant. I didn't know for the first two point (which show lack in pratice and timzone management). For the third and last point, it's true it is clumsy.
I will give some try to your suggestion and keep you informed of the result.

Regarding your last thought, I'm not sure what's the best method to resolve this issue but, for information, we also have a SVN server and it doesn't present this timzone offset in bug view page (cf. below).

So it must take into account somewhere the fact that the constructor force UTC (it is probably the same for all the others source code managers plugins). That's why I tried to resolve this issue with change in the Gitea plugin.

Finally, you can find below a sample of Gitea API response for commit->author->date (in ISO8601 with date in UTC+2).

Best regards,

Philippe

Merci Philippe

sample of Gitea API response for commit->author->date (in ISO8601 with date in UTC+2).
"2024-09-19T16:14:33+02:00"

In that case, I believe my earlier suggestion should give the expected result, but I'm also surprised because it's a standard format that the DateTimeImmutable constructor called by SourceChangeSet::__construct() should handle natively without the need for pre-conversion, so maybe the bug is elsewhere...

php > print_r( new DateTimeImmutable( "2024-09-19T16:14:33+02:00", new DateTimeZone('UTC') ) );
DateTimeImmutable Object
(
    [date] => 2024-09-19 16:14:33.000000
    [timezone_type] => 1
    [timezone] => +02:00
)

note that the UTC timezone is ignored since +02:00 was part of the original string...

Can you provide the Gitea output for commit 3ba2ee1814 illustrated in your original screenshot ?
Also, what is the value of mantis_plugin_Source_changeset_table.timestamp column for the same ?

we also have a SVN server and it doesn't present this timzone offset in bug view page

Can you check the output of svn log --xml -r6565 (sorry if the syntax is wrong, I have not used SVN for years) ? Maybe it's already in UTC.

I wait for your feedback.

Hello @dregad,

I think I might have an idea of what's hapenning.

The constructor of SourceChangeSet class receive the commit timestamp and a new DateTimeImmutable object is created with it. Later, in the save function of the class (line 957), this new object is formated without taking into account the timezone specified for compatibility with MySQL < 8.0.19.

# Commit timestamp: can't use DATE_ATOM format to insert datetime,
# as MySQL < 8.0.19 does not support specifying timezone
# @see https://dev.mysql.com/doc/refman/8.0/en/datetime.html
$t_timestamp = $this->timestamp->format( 'Y-m-d H:i:s' );

As the timestamp received from Gitea is in UTC+2, it is this timezone that is saved in database and later, in bug view page, the database timestamp is displayed in the user timezone which lead to the observed offset.

/**
 * Get the changeset's timestamp in the user's timezone.
 *
 * @param string $p_format Date format, defaults to $g_normal_date_format.
 *
 * @return string
 */
public function getLocalTimestamp( $p_format = null )
{
	if( !$p_format ) {
		$p_format = config_get( 'normal_date_format' );
	}

	return date( $p_format, $this->timestamp->getTimestamp() );
}

Below some picture to illustrate this and also the one asked :

1. The Gitea API response for commit 3ba2ee1814.
   

2. The changeset database entry from standard Gitea plugin for commit 3ba2ee1814 (saved in UTC+2 timezone).
   

3. The changeset database entry from patched (with your suggestion) Gitea plugin for commit 3ba2ee1814 (saved in UTC timezone this time).
   

4. The result of the svn log command for the revision 6565 which is indeed in UTC timezone.
   

5. Finally, the changeset database entry for SVN revision 6565 (saved in UTC timezone).
   

Best regards,

Philippe

The constructor of SourceChangeSet class receive the commit timestamp and a new DateTimeImmutable object is created with it. Later, in the save function of the class (line 957), this new object is formated without taking into account the timezone specified for compatibility with MySQL < 8.0.19.

# Commit timestamp: can't use DATE_ATOM format to insert datetime,
# as MySQL < 8.0.19 does not support specifying timezone
# @see https://dev.mysql.com/doc/refman/8.0/en/datetime.html
$t_timestamp = $this->timestamp->format( 'Y-m-d H:i:s' );

As the timestamp received from Gitea is in UTC+2, it is this timezone that is saved in database and later, in bug view page, the database timestamp is displayed in the user timezone which lead to the observed offset.

Right, looks like an oversight, I failed to consider that the timestamp would be stored in the original timezone. We need to always store the date in UTC for consistency.

I'm not sure whether it's best for this conversion to occur in the constructor, or in the save() method, but I guess the latter probably makes more sense since this is where the workaround for older MySQL version is.

Could you try with the following patch

diff --git a/Source/Source.API.php b/Source/Source.API.php
index 9b8ba6e..c50ccda 100644
--- a/Source/Source.API.php
+++ b/Source/Source.API.php
@@ -954,7 +954,10 @@ class SourceChangeset {
                # Commit timestamp: can't use DATE_ATOM format to insert datetime,
                # as MySQL < 8.0.19 does not support specifying timezone
                # @see https://dev.mysql.com/doc/refman/8.0/en/datetime.html
-               $t_timestamp = $this->timestamp->format( 'Y-m-d H:i:s' );
+               # so we convert the timestamp to UTC first.
+               $t_timestamp = $this->timestamp
+                       ->setTimezone( new DateTimeZone( 'UTC' ) )
+                       ->format( 'Y-m-d H:i:s' );

                if ( 0 == $this->id ) { # create
                        $t_query = "INSERT INTO $t_changeset_table ( repo_id, revision, parent, branch, user_id,

Hello @dregad,

I've tested your patch for Source.API.php with standard SourceGitea plugin (of course).

All works well ! The changeset database entry for commit 3ba2ee1814 is correctly saved in UTC timezone and the date information displayed in the user interface has the correct timezone included.

I have also tested the patch with a SVN repositry (it shouldn't have impact as the commit date received is in UTC but just to be safe) and all is okay here as well.

Best regards,

Philippe

@pfrancois-ACX thanks for the tests and feedback.

Since the solution is quite different from your original submission, I will close this PR. I have opened issue #419 to track the bug, and will commit the fix separately.