Re: How to? Timestamp with timezone.

Andre Lopes wrote:
> [code]
> ALTER DATABASE foo SET timezone TO 'someval'
> ALTER ROLE bar SET timezone TO 'someval'
> [/code]
>
> I need to alter only the Timezone of the database OR I need also to
> alter the Role?

Timestamp and timezone handling in PostgreSQL is very powerful. Think of
it as a hierarchy. The default timezone is that of my database cluster
(say America/Los_Angeles).

But say I maintain a database for someone in New York who wants to see
things in their zone. I can override the default for that one database
with:
ALTER DATABASE newyorkdb SET timezone TO 'America/New_York';

Now they have a user, Anwyn, who telecommutes from Wales so:
ALTER ROLE anwyn SET timezone TO 'WET';
makes her default timezone Western European Time regardless of the
server and database settings.

But when she runs reports for her customer in Ethiopia she overrides all
of the above with an explicit:
SET timezone to 'Africa/Addis_Ababa';

(The above setting remains in effect for the current session unless
reset with:
SET timezone to DEFAULT;)

Her customer, as customers do, has a special request and wants the
report to show the event times in the time zone of both their home
office in Ethiopia and their branch office in Tokyo. No problem:
SELECT event_time, event_time at time zone 'Asia/Tokyo' as tokyo_event,
...FROM ...;

You can get a list of time zones with:
SELECT * FROM pg_timezone_names;

Cheers,
Steve