to_char timezone

Started by Andrew Dunstanabout 13 years ago4 messages

andrew@dunslane.net

about 13 years ago

I'm not sure if this has come up before.

A client was just finding difficulties because to_char() doesn't support
formatting the timezone part of a timestamptz numerically (i.e. as
+-hhmm) instead of using a timezone name. Is there any reason for that?
Would it be something worth having?

cheers

andrew

Bruce Momjian

bruce@momjian.us

about 13 years ago

In reply to: Andrew Dunstan (#1)

Re: to_char timezone

On Sun, Oct 21, 2012 at 05:40:40PM -0400, Andrew Dunstan wrote:

I'm not sure if this has come up before.

A client was just finding difficulties because to_char() doesn't
support formatting the timezone part of a timestamptz numerically
(i.e. as +-hhmm) instead of using a timezone name. Is there any
reason for that? Would it be something worth having?

Yes, it is odd it is missing, and would be good to have.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian

bruce@momjian.us

over 12 years ago

In reply to: Andrew Dunstan (#1)

1 attachment(s)

Outputting UTC offset with to_char()

On Sun, Oct 21, 2012 at 05:40:40PM -0400, Andrew Dunstan wrote:

I'm not sure if this has come up before.

A client was just finding difficulties because to_char() doesn't
support formatting the timezone part of a timestamptz numerically
(i.e. as +-hhmm) instead of using a timezone name. Is there any
reason for that? Would it be something worth having?

Great idea! I have developed the attached patch to do this:

test=> SELECT to_char(current_timestamp, 'OF');
to_char
---------
-04
(1 row)

test=> SELECT to_char(current_timestamp, 'TMOF');
to_char
---------
-04
(1 row)

test=> SET timezone = 'Asia/Calcutta';
SET
test=> SELECT to_char(current_timestamp, 'OF');
to_char
---------
+05:30
(1 row)

test=> SELECT to_char(current_timestamp, 'FMOF');
to_char
---------
+5:30
(1 row)

I went with the optional colon and minutes because this is how we output
it:

test=> SELECT current_timestamp;
now
-------------------------------
2013-06-28 22:02:24.773587-04
---
(1 row)

test=> set timezone = 'Asia/Calcutta';
SET
test=> SELECT current_timestamp;
now
----------------------------------
2013-06-29 07:32:29.157565+05:30
------
(1 row)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Attachments:

tz.difftext/x-diff; charset=us-asciiDownload

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 7c009d8..5765ddf
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1
*** 5645,5650 ****
--- 5645,5654 ----
          <entry><literal>tz</literal></entry>
          <entry>lower case time-zone name</entry>
         </row>
+        <row>
+         <entry><literal>OF</literal></entry>
+         <entry>time-zone offset</entry>
+        </row>
        </tbody>
       </tgroup>
      </table>
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 7b85406..4c272ef
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*************** typedef enum
*** 600,605 ****
--- 600,606 ----
  	DCH_MS,
  	DCH_Month,
  	DCH_Mon,
+ 	DCH_OF,
  	DCH_P_M,
  	DCH_PM,
  	DCH_Q,
*************** static const KeyWord DCH_keywords[] = {
*** 746,751 ****
--- 747,753 ----
  	{"MS", 2, DCH_MS, TRUE, FROM_CHAR_DATE_NONE},
  	{"Month", 5, DCH_Month, FALSE, FROM_CHAR_DATE_GREGORIAN},
  	{"Mon", 3, DCH_Mon, FALSE, FROM_CHAR_DATE_GREGORIAN},
+ 	{"OF", 2, DCH_OF, FALSE, FROM_CHAR_DATE_NONE},		/* O */
  	{"P.M.", 4, DCH_P_M, FALSE, FROM_CHAR_DATE_NONE},	/* P */
  	{"PM", 2, DCH_PM, FALSE, FROM_CHAR_DATE_NONE},
  	{"Q", 1, DCH_Q, TRUE, FROM_CHAR_DATE_NONE}, /* Q */
*************** static const int DCH_index[KeyWord_INDEX
*** 874,880 ****
  	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
  	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
  	-1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1,
! 	DCH_FX, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, -1,
  	DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZ, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY,
  	-1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc,
  	DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi,
--- 876,882 ----
  	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
  	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
  	-1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1,
! 	DCH_FX, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, DCH_OF,
  	DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZ, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY,
  	-1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc,
  	DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi,
*************** DCH_to_char(FormatNode *node, bool is_in
*** 2502,2507 ****
--- 2504,2519 ----
  					s += strlen(s);
  				}
  				break;
+ 			case DCH_OF:
+ 				INVALID_FOR_INTERVAL;
+ 				sprintf(s, "%+0*ld", S_FM(n->suffix) ? 0 : 3, tm->tm_gmtoff / 3600);
+ 				s += strlen(s);
+ 				if (tm->tm_gmtoff % 3600 != 0)
+ 				{
+ 					sprintf(s, ":%02ld", (tm->tm_gmtoff % 3600) / 60);
+ 					s += strlen(s);
+ 				}
+ 				break;
  			case DCH_A_D:
  			case DCH_B_C:
  				INVALID_FOR_INTERVAL;
*************** DCH_from_char(FormatNode *node, char *in
*** 2915,2923 ****
  				break;
  			case DCH_tz:
  			case DCH_TZ:
  				ereport(ERROR,
  						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! 						 errmsg("\"TZ\"/\"tz\" format patterns are not supported in to_date")));
  			case DCH_A_D:
  			case DCH_B_C:
  			case DCH_a_d:
--- 2927,2936 ----
  				break;
  			case DCH_tz:
  			case DCH_TZ:
+ 			case DCH_OF:
  				ereport(ERROR,
  						(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! 						 errmsg("\"TZ\"/\"tz\"/\"OF\" format patterns are not supported in to_date")));
  			case DCH_A_D:
  			case DCH_B_C:
  			case DCH_a_d:

Bruce Momjian

bruce@momjian.us

over 12 years ago

In reply to: Bruce Momjian (#3)

Re: Outputting UTC offset with to_char()

Applied. I referenced macros for some of the new constants, e.g.
SECS_PER_HOUR.

---------------------------------------------------------------------------

On Fri, Jun 28, 2013 at 10:04:49PM -0400, Bruce Momjian wrote:

On Sun, Oct 21, 2012 at 05:40:40PM -0400, Andrew Dunstan wrote:

I'm not sure if this has come up before.

A client was just finding difficulties because to_char() doesn't
support formatting the timezone part of a timestamptz numerically
(i.e. as +-hhmm) instead of using a timezone name. Is there any
reason for that? Would it be something worth having?

Great idea! I have developed the attached patch to do this:

test=> SELECT to_char(current_timestamp, 'OF');
to_char
---------
-04
(1 row)

test=> SELECT to_char(current_timestamp, 'TMOF');
to_char
---------
-04
(1 row)

test=> SET timezone = 'Asia/Calcutta';
SET
test=> SELECT to_char(current_timestamp, 'OF');
to_char
---------
+05:30
(1 row)

test=> SELECT to_char(current_timestamp, 'FMOF');
to_char
---------
+5:30
(1 row)

I went with the optional colon and minutes because this is how we output
it:

test=> SELECT current_timestamp;
now
-------------------------------
2013-06-28 22:02:24.773587-04
---
(1 row)

test=> set timezone = 'Asia/Calcutta';
SET
test=> SELECT current_timestamp;
now
----------------------------------
2013-06-29 07:32:29.157565+05:30
------
(1 row)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 7c009d8..5765ddf
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1
*** 5645,5650 ****
--- 5645,5654 ----
<entry><literal>tz</literal></entry>
<entry>lower case time-zone name</entry>
</row>
+        <row>
+         <entry><literal>OF</literal></entry>
+         <entry>time-zone offset</entry>
+        </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 7b85406..4c272ef
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*************** typedef enum
*** 600,605 ****
--- 600,606 ----
DCH_MS,
DCH_Month,
DCH_Mon,
+ 	DCH_OF,
DCH_P_M,
DCH_PM,
DCH_Q,
*************** static const KeyWord DCH_keywords[] = {
*** 746,751 ****
--- 747,753 ----
{"MS", 2, DCH_MS, TRUE, FROM_CHAR_DATE_NONE},
{"Month", 5, DCH_Month, FALSE, FROM_CHAR_DATE_GREGORIAN},
{"Mon", 3, DCH_Mon, FALSE, FROM_CHAR_DATE_GREGORIAN},
+ 	{"OF", 2, DCH_OF, FALSE, FROM_CHAR_DATE_NONE},		/* O */
{"P.M.", 4, DCH_P_M, FALSE, FROM_CHAR_DATE_NONE},	/* P */
{"PM", 2, DCH_PM, FALSE, FROM_CHAR_DATE_NONE},
{"Q", 1, DCH_Q, TRUE, FROM_CHAR_DATE_NONE}, /* Q */
*************** static const int DCH_index[KeyWord_INDEX
*** 874,880 ****
-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
-1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1,
! 	DCH_FX, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, -1,
DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZ, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY,
-1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc,
DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi,
--- 876,882 ----
-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
-1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
-1, -1, -1, -1, -1, DCH_A_D, DCH_B_C, DCH_CC, DCH_DAY, -1,
! 	DCH_FX, -1, DCH_HH24, DCH_IDDD, DCH_J, -1, -1, DCH_MI, -1, DCH_OF,
DCH_P_M, DCH_Q, DCH_RM, DCH_SSSS, DCH_TZ, DCH_US, -1, DCH_WW, -1, DCH_Y_YYY,
-1, -1, -1, -1, -1, -1, -1, DCH_a_d, DCH_b_c, DCH_cc,
DCH_day, -1, DCH_fx, -1, DCH_hh24, DCH_iddd, DCH_j, -1, -1, DCH_mi,
*************** DCH_to_char(FormatNode *node, bool is_in
*** 2502,2507 ****
--- 2504,2519 ----
s += strlen(s);
}
break;
+ 			case DCH_OF:
+ 				INVALID_FOR_INTERVAL;
+ 				sprintf(s, "%+0*ld", S_FM(n->suffix) ? 0 : 3, tm->tm_gmtoff / 3600);
+ 				s += strlen(s);
+ 				if (tm->tm_gmtoff % 3600 != 0)
+ 				{
+ 					sprintf(s, ":%02ld", (tm->tm_gmtoff % 3600) / 60);
+ 					s += strlen(s);
+ 				}
+ 				break;
case DCH_A_D:
case DCH_B_C:
INVALID_FOR_INTERVAL;
*************** DCH_from_char(FormatNode *node, char *in
*** 2915,2923 ****
break;
case DCH_tz:
case DCH_TZ:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! 						 errmsg("\"TZ\"/\"tz\" format patterns are not supported in to_date")));
case DCH_A_D:
case DCH_B_C:
case DCH_a_d:
--- 2927,2936 ----
break;
case DCH_tz:
case DCH_TZ:
+ 			case DCH_OF:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! 						 errmsg("\"TZ\"/\"tz\"/\"OF\" format patterns are not supported in to_date")));
case DCH_A_D:
case DCH_B_C:
case DCH_a_d:

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers