Automatic conversion to UTC corrupts data in date type column for timezones with positive offset

[hyncica]

How to reproduce

1. Set your default timezone to any timezone with positive offset, for example to CET (UTC+1)/CEST (UTC+2)
2. In your entity define column with type date

/** @Column(type="date") */
private DateTimeImmutable $birth;

3. Set the property to any date, for example 2000-05-05.
4. Persist the entity in DB.
5. Read entity from DB and check the date in property.

Expected result
2000-05-05
Actual result
2000-05-04

This happens because 2000-05-05 is represented as 2000-05-05 00:00:00.000000 in DateTimeImmutable.
When persisting in DB it's converted from UTC+2 to UTC which is 2000-05-04 22:00:00.000000. Then it is stored in DATE type column as 2000-05-04.
When loading from DB the date is loaded as 2000-05-04 00:00:00.000000 and converted into UTC+2 as 2000-05-04 02:00:00.000000.

Conclusion
The columns with type date should not be subject to automatic timezone conversion because timezone conversion only makes sense when the date includes time.

[roxblnfk]

You can configure database timezone in the timezone option

https://cycle-orm.dev/docs/basic-connect#additional-connection-options
https://cycle-orm.dev/docs/advanced-datetime

Will this solve your issue?

[hyncica]

Thanks, this solved the issue in my case .

The documentation at second url is a bit confusing because it says:

all dates selected from and stored into the database will be converted into UTC timezone.

But it solved the issue only because the application will be used by users in same time zone.
Now, lets assume that the application is intended to be used by users in different time zones. In that case if you choose one timezone for DB connection the problem will manifest for any user that uses timezone with higher offset.

[roxblnfk]

Now, lets assume that the application is intended to be used by users in different time zones. In that case if you choose one timezone for DB connection the problem will manifest for any user that uses timezone with higher offset.

In this case you should:

• use datetime type instead of date in the Database
• don't use casting to datetime and use strings instead of DatetimeInterface because DBAL will always convert it to configured timezone