Re: How to? Timestamp with timezone.

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Andre Lopes <lopes80andre(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How to? Timestamp with timezone.
Date: 2010-03-10 17:03:20
Message-ID: 4B97D0D8.3030102@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Steve Crawford 2010-03-10 17:34:53 Re: Licence
Previous Message David W Noon 2010-03-10 16:57:07 Re: regexp_replace puzzle