Oracle TIMESTAMP w/ timezone data type confusion

Question

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

Answer 1

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.