Menu
In Oracle, date format and timezones feature multiple data types, including DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. The TIMESTAMP data type is an extension of the DATE type.
The timestamp datatype allows you to store both date and time. However, it does not have any time zone data. It means that when you change the timezone of your database server, the timestamp value stored in the database will not change automatically.
The TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ) data type stores 8-byte date values that include timestamp and time zone information in UTC format. You cannot define a TIMESTAMPTZ column with a specific precision for fractional seconds other than 6.
What is a "zone offset"? A zone offset is the difference in hours and minutes between a particular time zone and UTC. In ISO 8601, the particular zone offset can be indicated in a date or time value. The zone offset can be Z for UTC or it can be a value "+" or "-" from UTC.
In oracle, is the named timezone always stored?
I have been testing this column within our system, and in some places the timestamp is shown as:
26-FEB-09 11.36.25.390713 AM +13:00
but other times it's:
26-FEB-09 11.36.25.390713 AM Pacific/Auckland
If the value is being stored as the former, does that mean the actual timezone is not being stored?
I worry because if a future date is stored with only an offset we might not be able to determine the actual time in the original timezone, because you can determine a offset from a timezone, but not vice versa.
Thanks
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With