Thread: How to get the time zone offset
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 ____________________________________________________________________
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 ____________________________________________________________________