pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedISO time zone format
6+ messages / 3 participants
[nested] [flat]
* ISO time zone format
@ 2007-09-05 07:30 Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 2 replies; 6+ messages in thread
From: Sabin Coanda @ 2007-09-05 07:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ISO time zone format
@ 2007-09-05 07:58 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
1 sibling, 0 replies; 6+ messages in thread
From: Achilleas Mantzios @ 2007-09-05 07:58 UTC (permalink / raw)
To: pgsql-sql
Στις Τετάρτη 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ISO time zone format
@ 2007-09-05 08:16 Richard Huxton <dev@archonet.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
1 sibling, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2007-09-05 08:16 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ISO time zone format
@ 2007-09-05 13:09 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sabin Coanda @ 2007-09-05 13:09 UTC (permalink / raw)
To: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ISO time zone format
@ 2007-09-05 14:32 Richard Huxton <dev@archonet.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2007-09-05 14:32 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ISO time zone format
@ 2007-09-05 14:54 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Sabin Coanda @ 2007-09-05 14:54 UTC (permalink / raw)
To: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-09-05 14:54 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-05 07:30 ISO time zone format Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-09-05 07:58 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2007-09-05 08:16 ` Richard Huxton <dev@archonet.com>
2007-09-05 13:09 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-09-05 14:32 ` Richard Huxton <dev@archonet.com>
2007-09-05 14:54 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox