Postgres Pro
Downloads
Home   >   mailing lists

Thread: ISO time zone format

ISO time zone format

From
"Sabin Coanda"
Date:
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

Re: ISO time zone format

From
Achilleas Mantzios
Date:
Στις Τετάρτη 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

Re: ISO time zone format

From
Richard Huxton
Date:
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

Re: ISO time zone format

From
"Sabin Coanda"
Date:
>
> 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

Re: ISO time zone format

From
Richard Huxton
Date:
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/B19306_01/server.102/b14200/sql_elements004.htm#sthref405

But it seems the developers know about it:  http://www.postgresql.org/docs/faqs.TODO.html
http://archives.postgresql.org/pgsql-hackers/2005-12/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

Re: ISO time zone format

From
"Sabin Coanda"
Date:
>
> 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