Hi there,

I'd like to format a time stamp with time zone as ISO pattern (e.g.
1999-01-08 04:05:06 -8:00)

I found the patterns for all the timestamp parts, from "Data Type Formatting
Functions" Postgresql documentation, excepting the numeric ISO time zone.

Please suggest the appropiate pattern for that.

TIA,
Sabin

Στις ΤετάÏ�τη 05 ΣεπτÎ*μβÏ�ιος 2007 10:30, ο/η Sabin Coanda Î*γÏ�αψε:
> Hi there,
>
> I'd like to format a time stamp with time zone as ISO pattern (e.g.
> 1999-01-08 04:05:06 -8:00)
>
> I found the patterns for all the timestamp parts, from "Data Type
> Formatting Functions" Postgresql documentation, excepting the numeric ISO
> time zone.
>
> Please suggest the appropiate pattern for that.

Have you tried the default PgSQL behaviour, without setting DateStyle, or
doing any explicit formating?
Maybe its exactly what you want.
What does
select now();
from within psql, output in your system?
>
> TIA,
> Sabin
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Achilleas Mantzios

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sabin Coanda wrote:
> Hi there,
>
> I'd like to format a time stamp with time zone as ISO pattern (e.g.
> 1999-01-08 04:05:06 -8:00)
>
> I found the patterns for all the timestamp parts, from "Data Type Formatting
> Functions" Postgresql documentation, excepting the numeric ISO time zone.
>
> Please suggest the appropiate pattern for that.

If your DateStyle is set to ISO that's the default format. You don't
need to use to_char()

Or am I missing your point?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

>
> If your DateStyle is set to ISO that's the default format. You don't need
> to use to_char()
>
> Or am I missing your point?
>

Hi Richard,

I'd just like to format it independent of my DateStyle. Some timestamp parts
may be formatted this way.

For instance I can format ISO date with to_char( dt, 'YYYY-MM-DD'), and ISO
time with to_char( dt, 'HH24:MI:SS') as well. I'd just like to find the
format pattern for the ISO time zone (numeric).

Regards,
Sabin

Sabin Coanda wrote:
>
> I'd just like to format it independent of my DateStyle. Some timestamp parts
> may be formatted this way.
>
> For instance I can format ISO date with to_char( dt, 'YYYY-MM-DD'), and ISO
> time with to_char( dt, 'HH24:MI:SS') as well. I'd just like to find the
> format pattern for the ISO time zone (numeric).

Hmm - we don't seem to support those codes (TZH, TZM) for some reason.

http://download.oracle.com/docs/cd/B....htm#sthref405

But it seems the developers know about it:
http://www.postgresql.org/docs/faqs.TODO.html
http://archives.postgresql.org/pgsql...2/msg00948.php

If you know a little bit of "C" then you should be able to submit a
patch for this without too much trouble.


For the moment, you'll have to use extract() to pick out the bit(s) you
need:
to_char(extract(timezone_hour from current_timestamp), 'S00');


HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

>
> For the moment, you'll have to use extract() to pick out the bit(s) you
> need:
> to_char(extract(timezone_hour from current_timestamp), 'S00');
>

It's perfect for me, thanks a lot.
Sabin