How to get the time zone offset

On Friday, December 30, 2011 6:18:05 am Cezariusz Marek wrote:

I need to convert timestamp to a format with a time zone offset, like this:

select clock_timestamp()::text;

and it returns the following value:

"2011-12-30 11:59:06.538+01"

What is the default format for timestamp::text conversion? I've tried this:

select to_char(clock_timestamp(), 'YYYY-MM-DD HH24:MI:SSTZ');

But it gives me "CET" instead of "+01":

"2011-12-30 11:59:06CET"

Maybe I am not understanding what you want, but why not:

select clock_timestamp()::text;

It gives you the format you want.

--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver wrote:

On Friday, December 30, 2011 6:18:05 am Cezariusz Marek wrote:

I need to convert timestamp to a format with a time zone offset, like this:
select clock_timestamp()::text;
and it returns the following value:
"2011-12-30 11:59:06.538+01"
What is the default format for timestamp::text conversion? I've tried this:
select to_char(clock_timestamp(), 'YYYY-MM-DD HH24:MI:SSTZ');
But it gives me "CET" instead of "+01":
"2011-12-30 11:59:06CET"

Maybe I am not understanding what you want, but why not:

select clock_timestamp()::text;

It gives you the format you want.

Because I need more control. In this case I need the date in XML format, so it will be
something like this:

select to_char(clock_timestamp(), 'YYYY-MM-DD"T"HH24:MI:SSTZ');

Is the timestamp::text format always the same, regardless of the current locale and
language settings?

--
____________________________________________________________________
Cezariusz Marek                   mob: +48 608 646 494
http://www.comarch.com/   tel: +48 33 815 0734
____________________________________________________________________

On Friday, December 30, 2011 8:19:51 am Cezariusz Marek wrote:

Because I need more control. In this case I need the date in XML format, so
it will be something like this:

select to_char(clock_timestamp(), 'YYYY-MM-DD"T"HH24:MI:SSTZ');

So something like this:
test(5432)postgres=#select to_char(clock_timestamp(), 'YYYY-MM-DD"T"HH24:MI:SS') || extract(timezone_hour from clock_timestamp());
?column?
-----------------------
2011-12-30T08:34:22-8

Is the timestamp::text format always the same, regardless of the current
locale and language settings?

I don't know.

--
Adrian Klaver
adrian.klaver@gmail.com

On Friday, December 30, 2011 8:19:51 am Cezariusz Marek wrote:

Is the timestamp::text format always the same, regardless of the current
locale and language settings?

Now I have an answer. It can be changed via the DateStyle setting in postgresql.conf

http://www.postgresql.org/docs/9.1/interactive/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT

So for my previous setting of (iso,mdy) I got:

test(5432)postgres=#select clock_timestamp()::text;
clock_timestamp
-------------------------------
2011-12-30 08:56:39.019038-08

Changing the setting to (sql,mdy) resulted in:

test(5432)postgres=#select clock_timestamp()::text;
clock_timestamp
--------------------------------
12/30/2011 08:55:48.554618 PST

--
Adrian Klaver
adrian.klaver@gmail.com

Cezariusz Marek wrote:

I need to convert timestamp to a format with a time zone offset, like this:
[...]

I'm very sorry for multiple posting, those messages were holded because of some filters,
and now apparently someone approved them.

--
____________________________________________________________________
Cezariusz Marek                   mob: +48 608 646 494
http://www.comarch.com/   tel: +48 33 815 0734
____________________________________________________________________