Data Types - PostgreSQL: Up and Running - page 87

opens up in San Francisco. The new franchisee phones McDonald's headquarters to add
its store to the master directory with an opening time of 7 a.m. The data entry dude
entered the information as he is told: 7 a.m. The East Coast PostgreSQL server interprets
this to mean 7 a.m. Eastern, and now early risers in San Francisco are lining up at the
door wondering why they can't get their McBreakfasts at 4 a.m. Being hungry is one
thing, but we can imagine many situations in which confusion over a difference of three
hours could mean life or death.
Given the pitfalls, why would anyone want to use time-zone-aware data types? First, it
does spare you from having to do time zone conversions manually. For example, if a
flight leaves Boston at 8 a.m. and arrives in Los Angeles at 11 a.m., and your server is
in Europe, you don't want to have to figure out the offset for each manually. You could
just enter the data with the Boston and Los Angeles local times. There's another con‐
vincing reason to use time-zone-aware data types: the automatic handling of DST. With
countries deviating more and more from one another in DST schedules, manually
keeping track of DST changes for a globally used database would require a dedicated
programmer who does nothing but keep up to date with the latest DST schedules and
map them to geographic enclaves.
Here's an interesting example: a traveling salesperson catches a flight home from San
Francisco to nearby Oakland. When she boards the plane, the clock at the terminal reads
2012-03-11 1:50 a.m. When she lands, the clock in the terminal reads 2012-03-11 3:10
a.m. How long was the flight? The key to the solution is that the change to DST occurred
during the flight—the clocks sprang forward. With time-zone-aware timestamps, you
get 20 minutes, which the plausible answer for a short flight across the Bay. We get the
wrong answer if we don't use time-zone-aware timestamps:
SELECT '2012-03-11 3:10 AM America/Los_Angeles' :: timestamptz
- '2012-03-11 1:50 AM America/Los_Angeles' :: timestamptz ;
gives you 20 minutes, whereas:
SELECT '2012-03-11 3:10 AM' :: timestamp - '2012-03-11 1:50 AM' :: timestamp ;
gives you 1 hour and 20 minutes.
Let's drive the point home with more examples, using a Boston server. For
Example 5-8 , I input my time in Los Angeles local time, but because my server is in
Boston, I get a time returned in Boston local time. Note that it does give me the offset,
but that is merely display information. The timestamp is internally stored in UTC.
Example 5-8. Inputting time in one time zone and output in another
SELECT '2012-02-28 10:00 PM America/Los_Angeles' :: timestamptz ;
2012-02-29 01:00:00-05