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

It is possible to do this in a shared database server?

Best Regards,

On Tue, Mar 9, 2010 at 9:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

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?

Best Regards,

On Wed, Mar 10, 2010 at 1:06 AM, Alvaro Herrera
<alvherre@commandprompt.com>wrote:

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