Postgres can store your “timestamp with timezone” or “timestamp without timezone”.
If you don’t specify either, it will default to “timestamp without timezone”
Which one should you use? TLDR: “timestamps with time zones”
Whats the problem living life without timezones?
Say you have two users in two different timezones – one in California and one in France. Imagine you may need to know which one acted “first”, maybe in an auction or draft scenario where whoever acts first wins.
Without specifying timezone, your database will save the plain timestamp of each user, say “midnight” and “midnight”. How will you know which one acted first?
You could save the original timezone from the user and perform the necessary algebra when needed but this means you need to keep track of timezone changes and daylight savings time!
Solution – Use “timestamp with time zone” otherwise known as “timestamptz”
For
timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s TimeZone parameter, and is converted to UTC using the offset for thetimezonezone.Postgres docs
This bears repeating:
“For timestamp with time zone, the internally stored value is always in UTC.”
A proper name for this might be timestamp saved as UTC by default. By saving everything in the “same” timezone, we know the absolute time of the timestamps.
This brings up the main UX concern: What do users see?
Your users want to read data from the database using their preferred timezone.
(If they added a blog post at 3:00pm PST, they want to see the timestamp as 3:00pm not 6:00PM EST )
Dates are saved as UTC so you will eventually need to convert the time
Here are three options:
Converting on the server:
Best practice is to save the users’ preferred timezone as a name (e.g. America/Los_Angeles). This way your query or ORM can convert the date according to user preference.
Remember, timezones offsets are affected by political actions. A timezones actual hour offset can change depending on whether the timezone recognizes daylight savings time (Phoenix does not) or just simply when the political “owners” of the timezone decide to CHANGE the offset. Read The Long, Painful History of Time for more info.
Converting on the browser:
Many browsers have default timezones that can be used by passing in your date to new Date. You can also, simply use moment.js to parse the dates on the client to show the correctly formatted date string for your purpose.
Do not convert at all! Use “Time from now”:
Ever notice how websites have time indicators like “just now”, “30 minutes ago” and “22 hours ago”? These avoid dealing with timezones altogether. Way easier! moment.js has this API that will simply accept your date and convert it to time from now style.
moment([2007, 0, 29]).fromNow(); // 4 years ago
What about user input?
User input should always be converted to UTC (with timestampz this will be handled and stored in UTC by default). Many functions already return UTC by default such as new Date() in Browser JavaScript and node.
Resources: