Postgres timestamp with timezone – “timestamptz”

Postgres can store your timestamp WITH or 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 the 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.

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 the timezone zone.

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, )

Dates are saved as UTC so you will eventually need to convert the time

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 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.

What about user input?

User input should always be converted to UTC. Many functions already return UTC by default such as  new Date() in Browser JavaScript and node.

 

Resources:

PostgreSQL Data Types: Date, Timestamp, and Time Zones

Detecting the time zone from the browser: 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s