Skip to content

Commit 764f72d

Browse files
tglsfdctglsfdc
committed
Make EXTRACT(TIMEZONE) and SET/SHOW TIMEZONE follow the SQL convention
for the sign of timezone offsets, ie, positive is east from UTC. These were previously out of step with other operations that accept or show timezones, such as I/O of timestamptz values.
1 parent 93236b5 commit 764f72d

File tree

10 files changed

+74
-41
lines changed

10 files changed

+74
-41
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.159 2003/07/15 19:19:55 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.160 2003/07/17 00:55:36 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -5016,16 +5016,16 @@ SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
50165016
</screen>
50175017
</listitem>
50185018
</varlistentry>
5019-
<!--
50205019
<varlistentry>
50215020
<term><literal>timezone</literal></term>
50225021
<listitem>
50235022
<para>
5024-
The time zone offset. XXX But in what units?
5023+
The time zone offset from UTC, measured in seconds. Positive values
5024+
correspond to time zones east of UTC, negative values to
5025+
zones west of UTC.
50255026
</para>
50265027
</listitem>
50275028
</varlistentry>
5028-
-->
50295029

50305030
<varlistentry>
50315031
<term><literal>timezone_hour</literal></term>

‎doc/src/sgml/ref/set.sgml

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/set.sgml,v 1.78 2003/07/15 19:19:56 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/set.sgml,v 1.79 2003/07/17 00:55:36 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -182,16 +182,16 @@ SELECT setseed(<replaceable>value</replaceable>);
182182
</listitem>
183183
</varlistentry>
184184
<varlistentry>
185-
<term><literal>7</literal></term>
185+
<term><literal>-7</literal></term>
186186
<listitem>
187187
<para>
188188
The time zone 7 hours west from UTC (equivalent
189-
to PDT). Negative values are east from UTC.
189+
to PDT). Positive values are east from UTC.
190190
</para>
191191
</listitem>
192192
</varlistentry>
193193
<varlistentry>
194-
<term><literal>INTERVAL '08:00' HOUR TO MINUTE</literal></term>
194+
<term><literal>INTERVAL '-08:00' HOUR TO MINUTE</literal></term>
195195
<listitem>
196196
<para>
197197
The time zone 8 hours west from UTC (equivalent
@@ -205,7 +205,7 @@ SELECT setseed(<replaceable>value</replaceable>);
205205
<listitem>
206206
<para>
207207
Set the time zone to your local time zone (the one that
208-
your operating system defaults to).
208+
the server's operating system defaults to).
209209
</para>
210210
</listitem>
211211
</varlistentry>

‎doc/src/sgml/release.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.197 2003/07/15 19:19:55 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.198 2003/07/17 00:55:36 tgl Exp $
33
-->
44

55
<appendix id="release">
@@ -24,6 +24,8 @@ CDATA means the content is "SGML-free", so you can write without
2424
worries about funny characters.
2525
-->
2626
<literallayout><![CDATA[
27+
EXTRACT(TIMEZONE) and SET/SHOW TIMEZONE now follow SQL sign convention
28+
(positive = east of UTC)
2729
Output of SHOW DATESTYLE is now in the same format accepted by SET DATESTYLE
2830
PL/Python is now an untrusted language, and is renamed to 'plpythonu'
2931
Dollar sign ($) is no longer allowed in operator names

‎src/backend/commands/variable.c

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $Header: /cvsroot/pgsql/src/backend/commands/variable.c,v 1.81 2003/07/15 19:34:43 tgl Exp $
12+
* $Header: /cvsroot/pgsql/src/backend/commands/variable.c,v 1.82 2003/07/17 00:55:37 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -429,7 +429,8 @@ assign_timezone(const char *value, bool doit, bool interactive)
429429
}
430430
if (doit)
431431
{
432-
CTimeZone = interval->time;
432+
/* Here we change from SQL to Unix sign convention */
433+
CTimeZone = - interval->time;
433434
HasCTZSet = true;
434435
}
435436
pfree(interval);
@@ -444,7 +445,8 @@ assign_timezone(const char *value, bool doit, bool interactive)
444445
{
445446
if (doit)
446447
{
447-
CTimeZone = hours * 3600;
448+
/* Here we change from SQL to Unix sign convention */
449+
CTimeZone = - hours * 3600;
448450
HasCTZSet = true;
449451
}
450452
}
@@ -557,7 +559,8 @@ assign_timezone(const char *value, bool doit, bool interactive)
557559
return NULL;
558560

559561
if (HasCTZSet)
560-
snprintf(result, sizeof(tzbuf), "%.5f", (double) CTimeZone / 3600.0);
562+
snprintf(result, sizeof(tzbuf), "%.5f",
563+
(double) (-CTimeZone) / 3600.0);
561564
else if (tzbuf[0] == 'T')
562565
strcpy(result, tzbuf + 3);
563566
else
@@ -579,7 +582,7 @@ show_timezone(void)
579582
Interval interval;
580583

581584
interval.month = 0;
582-
interval.time = CTimeZone;
585+
interval.time = - CTimeZone;
583586

584587
tzn = DatumGetCString(DirectFunctionCall1(interval_out,
585588
IntervalPGetDatum(&interval)));

‎src/backend/utils/adt/date.c

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/date.c,v 1.83 2003/06/16 18:56:45 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/date.c,v 1.84 2003/07/17 00:55:37 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -2031,17 +2031,18 @@ timetz_part(PG_FUNCTION_ARGS)
20312031
switch (val)
20322032
{
20332033
case DTK_TZ:
2034-
result = tz;
2034+
result = -tz;
20352035
break;
20362036

20372037
case DTK_TZ_MINUTE:
2038-
result = tz / 60;
2039-
TMODULO(result, dummy, 60e0);
2038+
result = -tz;
2039+
result /= 60;
2040+
FMODULO(result, dummy, 60e0);
20402041
break;
20412042

20422043
case DTK_TZ_HOUR:
2043-
dummy = tz;
2044-
TMODULO(dummy, result, 3600e0);
2044+
dummy = -tz;
2045+
FMODULO(dummy, result, 3600e0);
20452046
break;
20462047

20472048
case DTK_MICROSEC:

‎src/backend/utils/adt/nabstime.c

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/nabstime.c,v 1.108 2003/05/12 23:08:50 tgl Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/nabstime.c,v 1.109 2003/07/17 00:55:37 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -825,12 +825,14 @@ reltimesend(PG_FUNCTION_ARGS)
825825
static void
826826
reltime2tm(RelativeTime time, struct tm * tm)
827827
{
828-
TMODULO(time, tm->tm_year, 31557600);
829-
TMODULO(time, tm->tm_mon, 2592000);
830-
TMODULO(time, tm->tm_mday, 86400);
831-
TMODULO(time, tm->tm_hour, 3600);
832-
TMODULO(time, tm->tm_min, 60);
833-
TMODULO(time, tm->tm_sec, 1);
828+
double dtime = time;
829+
830+
FMODULO(dtime, tm->tm_year, 31557600);
831+
FMODULO(dtime, tm->tm_mon, 2592000);
832+
FMODULO(dtime, tm->tm_mday, 86400);
833+
FMODULO(dtime, tm->tm_hour, 3600);
834+
FMODULO(dtime, tm->tm_min, 60);
835+
FMODULO(dtime, tm->tm_sec, 1);
834836
}
835837

836838

‎src/backend/utils/adt/timestamp.c

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v 1.85 2003/07/04 18:21:13 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v 1.86 2003/07/17 00:55:37 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3080,17 +3080,18 @@ timestamptz_part(PG_FUNCTION_ARGS)
30803080
switch (val)
30813081
{
30823082
case DTK_TZ:
3083-
result = tz;
3083+
result = -tz;
30843084
break;
30853085

30863086
case DTK_TZ_MINUTE:
3087-
result = tz / 60;
3088-
TMODULO(result, dummy, 60e0);
3087+
result = -tz;
3088+
result /= 60;
3089+
FMODULO(result, dummy, 60e0);
30893090
break;
30903091

30913092
case DTK_TZ_HOUR:
3092-
dummy = tz;
3093-
TMODULO(dummy, result, 3600e0);
3093+
dummy = -tz;
3094+
FMODULO(dummy, result, 3600e0);
30943095
break;
30953096

30963097
case DTK_MICROSEC:

‎src/include/miscadmin.h

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
1313
* Portions Copyright (c) 1994, Regents of the University of California
1414
*
15-
* $Id: miscadmin.h,v 1.125 2003/06/27 19:08:38 tgl Exp $
15+
* $Id: miscadmin.h,v 1.126 2003/07/17 00:55:37 tgl Exp $
1616
*
1717
* NOTES
1818
* some of the information in this file should be moved to
@@ -147,7 +147,9 @@ extern DLLIMPORT Oid MyDatabaseId;
147147
* EuroDates if client prefers dates interpreted and written w/European conventions.
148148
*
149149
* HasCTZSet is true if user has set timezone as a numeric offset from UTC.
150-
* If so, CTimeZone is the timezone offset in seconds.
150+
* If so, CTimeZone is the timezone offset in seconds (using the Unix-ish
151+
* sign convention, ie, positive offset is west of UTC, rather than the
152+
* SQL-ish convention that positive is east of UTC).
151153
*/
152154

153155
#define MAXTZLEN 10 /* max TZ name len, not counting tr. null */

‎src/include/utils/datetime.h

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
1010
* Portions Copyright (c) 1994, Regents of the University of California
1111
*
12-
* $Id: datetime.h,v 1.39 2003/05/18 01:06:26 tgl Exp $
12+
* $Id: datetime.h,v 1.40 2003/07/17 00:55:37 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -196,12 +196,23 @@ typedef struct
196196
} datetkn;
197197

198198

199-
/* TMODULO()
199+
/* FMODULO()
200200
* Macro to replace modf(), which is broken on some platforms.
201201
* t = input and remainder
202202
* q = integer part
203203
* u = divisor
204204
*/
205+
#define FMODULO(t,q,u) \
206+
do { \
207+
q = ((t < 0) ? ceil(t / u): floor(t / u)); \
208+
if (q != 0) t -= rint(q * u); \
209+
} while(0)
210+
211+
/* TMODULO()
212+
* Like FMODULO(), but work on the timestamp datatype (either int64 or float8).
213+
* We assume that int64 follows the C99 semantics for division (negative
214+
* quotients truncate towards zero).
215+
*/
205216
#ifdef HAVE_INT64_TIMESTAMP
206217
#define TMODULO(t,q,u) \
207218
do { \
@@ -211,7 +222,7 @@ do { \
211222
#else
212223
#define TMODULO(t,q,u) \
213224
do { \
214-
q = ((t < 0)? ceil(t / u): floor(t / u)); \
225+
q = ((t < 0) ? ceil(t / u): floor(t / u)); \
215226
if (q != 0) t -= rint(q * u); \
216227
} while(0)
217228
#endif

‎src/interfaces/ecpg/pgtypeslib/dt.h

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -183,12 +183,23 @@ typedef struct
183183
} datetkn;
184184

185185

186-
/* TMODULO()
186+
/* FMODULO()
187187
* Macro to replace modf(), which is broken on some platforms.
188188
* t = input and remainder
189189
* q = integer part
190190
* u = divisor
191191
*/
192+
#define FMODULO(t,q,u) \
193+
do { \
194+
q = ((t < 0) ? ceil(t / u): floor(t / u)); \
195+
if (q != 0) t -= rint(q * u); \
196+
} while(0)
197+
198+
/* TMODULO()
199+
* Like FMODULO(), but work on the timestamp datatype (either int64 or float8).
200+
* We assume that int64 follows the C99 semantics for division (negative
201+
* quotients truncate towards zero).
202+
*/
192203
#ifdef HAVE_INT64_TIMESTAMP
193204
#define TMODULO(t,q,u) \
194205
do { \
@@ -198,7 +209,7 @@ do { \
198209
#else
199210
#define TMODULO(t,q,u) \
200211
do { \
201-
q = ((t < 0)? ceil(t / u): floor(t / u)); \
212+
q = ((t < 0) ? ceil(t / u): floor(t / u)); \
202213
if (q != 0) t -= rint(q * u); \
203214
} while(0)
204215
#endif

0 commit comments

Comments
 (0)