Oracle TIMESTAMP w/ timezone data type confusion

When would you use TIMESTAMP w/ timezone as opposed to TIMESTAMP w/ local time zone?

When data is stored in a column of data type TIMESTAMP w/ local tz, the data is normalized to the database time zone, and the time zone displacement is not stored as part of the c开发者_StackOverflow中文版olumn data. When users retrieve the data, Oracle returns it in the users' local session time zone.

Isn't that much more useful? I can't think of a reason why I'd want to use TIMESTAMP w/ timezone and get back some gobble gook with a UTC offset.

---

Sometimes it may be important to know what time an event occurred within the context of its local timezone - not whatever equivalent time it is for the person querying it.

Hypothetical example: a medical application records a person's sleep patterns. You're probably going to be more interested in what time they went to bed, in their timezone, rather than what time they went to bed as of your own local timezone; plus, you can also determine what their timezone was at the time, so you can tell if they were travelling between timezones at the time and might have been suffering from jet lag.

---

Normalization by database may not be accurate always due to: 1. While normalizing TIMESTAMP w/ local time zone, database depends on timezone supplied by the client (i.e. session timezone). Session timezone could be inaccurate due to number of reasons like system timezone was not set etc. 2. If client has supplied timezone name (instead of timezone offset), database needs to retrieve corresponding timezone offset. It does it automatically using system tables. Now think about DST. If there are frequent changes in Daylight Savings Transition dates, data may be inaccurate in the database system tables. You need to track such changes and obtain/apply database patch immediately (which may not be possible always), so database can update its system tables to reflect time zone offset.