Обсуждение: How to? Timestamp with timezone.

Andre Lopes <lopes80andre@gmail.com> writes:
> I have a database in a US based Server. I need to get a TIMESTAMP with the
> PORTUGAL time. How can I do this?

Try AT TIME ZONE, or just temporarily change the timezone setting.

            regards, tom lane

Andre Lopes wrote:
> It is possible to do this in a shared database server?
>

sure, client timezone is a per connection setting.


    SET TIME ZONE 'WET';

or

    SET TIME ZONE 'Europe/Lisbon';

Andre Lopes escribió:
> It is possible to do this in a shared database server?

You can also do

ALTER DATABASE foo SET timezone TO 'someval'
ALTER ROLE bar SET timezone TO 'someval'

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Andre Lopes escribió:
> Hi,
>
> Thanks for the reply's.
>
> [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?

One of them suffices.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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