I've said for ages that programmers only need two subjects to learn. Date and time programming and debugging. The first one leads naturally to the second one.
The rules I follow:
1. If you're recording an event in the past, store it in UTC.
2. If you need to record the local time for something either in the past or the future, store it as the local time as it was (for the past), or the local time as it will be (for the future), plus a timezone (not an offset).
3. If you're displaying a timestamp, a) if you can, display UTC, b) if needed, display in local time using current conversion rules
4. If you are getting user input, a) if you can, get it in UTC, b) if not, get it using a timezone (not an offset)
5. If you are storing a date, then that is not a timestamp. A date is a period, so either just store the date without a timezone, or, if it's a date period in a local area, store the start and end timestamps with their timezones.
The rules I follow: 1. If you're recording an event in the past, store it in UTC.
2. If you need to record the local time for something either in the past or the future, store it as the local time as it was (for the past), or the local time as it will be (for the future), plus a timezone (not an offset).
3. If you're displaying a timestamp, a) if you can, display UTC, b) if needed, display in local time using current conversion rules
4. If you are getting user input, a) if you can, get it in UTC, b) if not, get it using a timezone (not an offset)
5. If you are storing a date, then that is not a timestamp. A date is a period, so either just store the date without a timezone, or, if it's a date period in a local area, store the start and end timestamps with their timezones.