Re: How to get the time zone offset

Поиск
Список
Период
Сортировка
Искать

Re: How to get the time zone offset

От:
Adrian Klaver <adrian.klaver@gmail.com>
Дата:
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

Re: How to get the time zone offset

От:
Adrian Klaver <adrian.klaver@gmail.com>
Дата:
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

Re: How to get the time zone offset

От:
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

How to get the time zone offset

От:
"Cezariusz Marek" <cezariusz.marek@comarch.pl>
Дата:

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"

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

Re: How to get the time zone offset

От:
"Cezariusz Marek" <cezariusz.marek@comarch.pl>
Дата:
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
____________________________________________________________________

Re: How to get the time zone offset

От:
"Cezariusz Marek" <cezariusz.marek@comarch.pl>
Дата:
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
____________________________________________________________________
FAQ