unable to replicate

what tool are you using to view the value in the "created" column?

Please test with Joomla 4.4.3 or Joomla 5.0.3!

Your table prefix made me think that your Joomla version is 5.1.0 and I downloaded it and tested, and I can confirm that it is fixed in 5.1.0!

I am testing with phpMyAdmin, but a query with a SELECT with PHP or via CLI gives me the same wrong result, but as I discovered today, only in versions prior to J!5.1.0

done with both latest 4.4.x and 5.0.x and it works as expected

done with both latest 4.4.x and 5.0.x and it works as expected

Thanks for the test, we need to find out what is different between my version and yours.
I'm going to install a clean version of Joomle 5.0.x and see if that happens.
Could you please test with phpMyAdmin?

done with 5.2.0

Thanks for the test, I can also confirm that a clean version of Joomla 5.0.3 is also working correctly!
This means that I have something that is changing the dates in my sites that are "affected", I'm going to disable everything and test until I find out what is doing this.
As soon as I have a result I'll post here for others to know what could make this.

I found out what is changing this!!
It is the timezone at the configuration.
If I have UTC it behaves correctly, but if I change it to "Lisbon" it will have this issue!!

Maybe this is the way it should be? account for the timezone change?

datetime is always stored as utc

what is confusing is that you posted an example where the difference did not match

As you can see, there is less one hour compared to the original inputed field, but if we go to older dates (ex: 1532-09-26 00:03:00) the recorded value is this: 1532-09-26 00:39:45

Indeed, I re-tested in Joomla 5.1.0 (the latest beta) and changed the timezone to "Lisbon", and the date "1532-09-26 00:03:00" was re-saved and at the db I can see "1532-09-26 00:39:45" this is a weird change.

Also, why is this happening to dates before 1976-09-26?

The ICU Library respective the Time Zone database (formerly known as Olson database) which is used by many software projects like PHP or databases and also by Linux operating systems uses the local time as determined by the particular city for historic dates which are older than the invention of time zones. I don't remember the date now, but 1532 is surely before that, so you get the local time of Lisboa determined by the longitude difference to Greenwich meridian. That explains the change from "00:03:00" = time entered in Lisboa local time to "00:39:45" in UTC, as Lisboa is west of Greenwich.

You can find more information here https://data.iana.org/time-zones/tz-link.html .

Thanks for that explanation, but what happened on the 1976-09-26 to match the dates?

Well, that's the only remaining question. Everything else should be clarified.

The IANA Time Zone Database rules for Lisboa can be found here: https://github.com/eggert/tz/blob/main/europe#L2157-L2164

So anything before January 1, 1912, goes to local time with an offset of -0:36:45 to UTC.

On 1976-09-26 at 1:00 was a time zone change from WET/WEDT to CET, very likely due to a law change.

The rules which belong to that table you can just find in the lines above the ones I've linked.

So to me all seems to be as expected.

They changed rule on 1966-04-03, then again on 1976-09-26, then again later a few times until the current EU daylight saving time rules applied in 1996.

Stellar research, find and explanation @richard67 !
Thank you very much for clearing this.

I would also like to thank @alikon and @brianteeman for the time they invested in helping solve this issue!