If you persistently store an instant in time (aka a timestamp), then that instant must be stored in Coordinated Universal Time (UTC)*.
An instant in time is an event that happens at one particular instant everywhere in the world. Examples:
- The instant a user pressed submit on the order of a new Lie-Nielsen hand plane.
- created and last modified timestamps
- log entry timestamps
- start of game 7 of the NBA finals
- launch of a SpaceX rocket
In all these cases, the date must be stored in UTC, even if you think your application will only be used in your local time zone. When the date is displayed, then and only then, is it converted to the local time zone of the user.
I once wrote an application that used MySQL. I stored event times in a DATETIME field. (Actually I thought I was using TIMESTAMP, but Liquibase created a DATETIME.) Anyway, I had all my code working, displaying event times in different time zones, etc. After several months I discovered that our data looked odd when the time changed from Daylight Saving Time to Standard Time. The problem was that MySQL was using the local time zone to store the value and the local time 2014-10-02 01:05 is ambiguous. That time falls during the hour of transition from Daylight Saving Time back to Standard Time. The clock “falls back,” so all the times from 1:00am to 1:59am happen twice. Read the solution here.
During the early stages of development, it is very easy to ignore this issue and store all instants in the local time zone. Don’t do it! Avoid a schema migration later. Store all your instants in UTC now.
* Types like SQL Server's DateTimeOffset also work because they are effectively storing the time in UTC by storing the local time and the time offset to UTC at that instant.