You're completely right, for these niche cases it matters. I'm not saying you should indiscriminately apply UTC to every problem, I'm just saying usually datetimes should be stored as UTC.
And the reason I feel the need to say this is that most systems I've worked on don't do that. They use local time for things that have no business being local time.
UTC should be the default solution, and local datetime usage should be a solution in the few situations where its needed.
And yeah, dateonly is nice. If the db doesn't support it you can just store it as a string and serialize/deserialize it to a DateOnly type in your software.
I'd say, in general any time a time-value somehow originates as a part of an interaction with a human, that time-value carries with it the context of a timezone and the expectations of what that timezone means for that human.
For internal timestamps such as ordering events in a database, them UTC or something similar is nice. Bet the point then is that those values are not really meaningful or important in the analog world.
Pretty much everything most user-facing apps do generates time values. Maybe it's more accurate to say any time a user actually inputs a date and time. Stuff like scheduling and booking software definitely needs to consider these things.
Basically if you want to preserve the original input then obviously you won't change it. If you just want to record an instant in time you use UTC.
And the reason I feel the need to say this is that most systems I've worked on don't do that. They use local time for things that have no business being local time.
UTC should be the default solution, and local datetime usage should be a solution in the few situations where its needed.
And yeah, dateonly is nice. If the db doesn't support it you can just store it as a string and serialize/deserialize it to a DateOnly type in your software.