Xavier Robin <xavier.robin@bluewin.ch> wrote:
> [...]
> In both examples, the timezone is lost. I read that
>> To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type
> but how can I do that in pactice?
You should have read on :-):
| For timestamp with time zone, the internally stored value is
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| always in UTC (Universal Coordinated Time, traditionally
^^^^^^^^^^^^^
| known as Greenwich Mean Time, GMT). An input value that has
| an explicit time zone specified is converted to UTC using
| the appropriate offset for that time zone. If no time zone
| is stated in the input string, then it is assumed to be in
| the time zone indicated by the system's timezone parameter,
| and is converted to UTC using the offset for the timezone
| zone.
| When a timestamp with time zone value is output, it is al-
^^^^^^^^^
| ways converted from UTC to the current timezone zone, and
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| displayed as local time in that zone. To see the time in an-
| other time zone, either change timezone or use the AT TIME
| ZONE construct (see Section 9.9.3).
So if you need the timezone information, you'll have to re-
cord it separately.
IMVHO best practice is to treat TIMESTAMP WITH TIME ZONE
as truly evil (TM), only use UTC timestamps in the database
and only convert them from and to something timezonic in
your application when it has to interact with a human being.
Tim