I just store millis or nanos as INTEGER. Never found the correct use for string datetimes, also they're slower and take much more space
But for storing future events that are tied to a time zone, you need the string with time zone. Otherwise when time zone definitions change, your time will become wrong.
If daylight savings time gets cancelled by legislation, then the event happening at noon two summers from now, you will still probably want to happen at noon -- the new noon.
But changes to timezones don't apply retroactively. At least not in this universe!
When describing past events, it’s often most precise to describe the literal universe time that it happened.
Obviously these are just generalities, whether you choose one strategy or another depends on the specific use case.
On the application side, I use Dapper and DateTimeOffset to map these to a domain type with proper DateTime/UTC fields.
I've found that storing time as integers in the database has some interesting upsides. For example, range queries over the field tend to be faster.