Local value reflection using TIMESTAMP data type extensions
To cover the complexity of time zone management, local timestamp reflection should also be highlighted. This section introduces data types forming the TIMESTAMP
type extension. In principle, three TIMESTAMP
data types are available:
TIMESTAMP
does not cover time zone elements. It is mostly used for backward compatibility or values that do not need to reference time zones at all.TIMESTAMP WITH TIME ZONE
maps the time zone to the value by the offset extension. Using this data type, particular values can be time zone-shifted and coordinated across regions. Thus, it is normalized across the database.TIMESTAMP WITH LOCAL TIME ZONE
is a specific type that does not state the time zone explicitly. Instead, the transformation is applied directly. Thus, the original value of the hour and minute elements is recalculated and provided to the user depending on their time zone.
Let’s create a simple table...