Hello,
this patch ensures independency datetime fields on current datestyle
setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same to
USE_ISO_DATESTYLE. Differences are for timestamp:
ISO: yyyy-mm-dd hh24:mi:ss
XSD: yyyy-mm-ddThh24:mi:ss
I found one link about this topic:
http://forums.oracle.com/forums/thread.jspa?threadID=467278&tstart=0
Regards
Pavel Stehule
_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/
*** ./src/backend/utils/adt/datetime.c.orig 2007-02-19 21:46:54.000000000 +0100
--- ./src/backend/utils/adt/datetime.c 2007-02-19 22:06:20.000000000 +0100
***************
*** 3188,3193 ****
--- 3188,3194 ----
switch (style)
{
case USE_ISO_DATES:
+ case USE_XSD_DATES:
/* compatible with ISO date formats */
if (tm->tm_year > 0)
sprintf(str, "%04d-%02d-%02d",
***************
*** 3278,3283 ****
--- 3279,3285 ----
* SQL - mm/dd/yyyy hh:mm:ss.ss tz
* ISO - yyyy-mm-dd hh:mm:ss+/-tz
* German - dd.mm.yyyy hh:mm:ss tz
+ * XSD - yyyy-mm-ddThh:mm:ss.ss+/-tz
* Variants (affects order of month and day for Postgres and SQL styles):
* US - mm/dd/yyyy
* European - dd/mm/yyyy
***************
*** 3296,3306 ****
switch (style)
{
case USE_ISO_DATES:
/* Compatible with ISO-8601 date formats */
! sprintf(str, "%04d-%02d-%02d %02d:%02d",
(tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1),
tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min);
/*
* Print fractional seconds if any. The field widths here should
--- 3298,3315 ----
switch (style)
{
case USE_ISO_DATES:
+ case USE_XSD_DATES:
/* Compatible with ISO-8601 date formats */
! if (style == USE_ISO_DATES)
! sprintf(str, "%04d-%02d-%02d %02d:%02d",
(tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1),
tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min);
+ else
+ sprintf(str, "%04d-%02d-%02dT%02d:%02d",
+ (tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1),
+ tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min);
+
/*
* Print fractional seconds if any. The field widths here should
*** ./src/backend/utils/adt/xml.c.orig 2007-02-19 19:37:27.000000000 +0100
--- ./src/backend/utils/adt/xml.c 2007-02-19 22:33:11.000000000 +0100
***************
*** 65,73 ****
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/xml.h"
-
#ifdef USE_LIBXML
static StringInfo xml_err_buf = NULL;
--- 65,74 ----
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
+ #include "utils/date.h"
+ #include "utils/datetime.h"
#include "utils/xml.h"
#ifdef USE_LIBXML
static StringInfo xml_err_buf = NULL;
***************
*** 1513,1526 ****
bool isvarlena;
char *p, *str;
! if (type == BOOLOID)
{
! if (DatumGetBool(value))
! return "true";
! else
! return "false";
! }
getTypeOutputInfo(type, &typeOut, &isvarlena);
str = OidOutputFunctionCall(typeOut, value);
--- 1514,1595 ----
bool isvarlena;
char *p, *str;
! /* xsd format doesn't depend on current settings */
! switch (type)
{
! case BOOLOID:
! if (DatumGetBool(value))
! return "true";
! else
! return "false";
! case DATEOID:
! {
! struct pg_tm tt,
! *tm = &tt;
! char buf[MAXDATELEN + 1];
! DateADT date = DatumGetDateADT(value);
!
! j2date(date + POSTGRES_EPOCH_JDATE,
! &(tm->tm_year), &(tm->tm_mon), &(tm->tm_mday));
!
! EncodeDateOnly(tm, USE_XSD_DATES, buf);
! return pstrdup(buf);
! }
!
! case TIMEOID:
! /* datestyle hasn't affect on time formating */
! break;
!
! case TIMESTAMPOID:
! {
! Timestamp timestamp = DatumGetTimestamp(value);
! struct pg_tm tt,
! *tm = &tt;
! fsec_t fsec;
! char *tzn = NULL;
! char buf[MAXDATELEN + 1];
!
! /* xsd doesn't support infinite values */
! if (TIMESTAMP_NOT_FINITE(timestamp))
! ereport(ERROR,
! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
! errmsg("timestamp out of range")));
! else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
! EncodeDateTime(tm, fsec, NULL, &tzn, USE_XSD_DATES, buf);
! else
! ereport(ERROR,
! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
! errmsg("timestamp out of range")));
!
! return pstrdup(buf);
! }
+ case TIMESTAMPTZOID:
+ {
+ TimestampTz dt = DatumGetTimestamp(value);
+ struct pg_tm tt,
+ *tm = &tt;
+ int tz;
+ fsec_t fsec;
+ char *tzn = NULL;
+ char buf[MAXDATELEN + 1];
+
+ /* xsd doesn't support infinite values */
+ if (TIMESTAMP_NOT_FINITE(dt))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+ else if (timestamp2tm(dt, &tz, tm, &fsec, &tzn, NULL) == 0)
+ EncodeDateTime(tm, fsec, &tz, &tzn, USE_XSD_DATES, buf);
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+ errmsg("timestamp out of range")));
+
+ return pstrdup(buf);
+ }
+ }
+
getTypeOutputInfo(type, &typeOut, &isvarlena);
str = OidOutputFunctionCall(typeOut, value);
*** ./src/include/miscadmin.h.orig 2007-02-19 21:44:41.000000000 +0100
--- ./src/include/miscadmin.h 2007-02-19 21:45:31.000000000 +0100
***************
*** 178,183 ****
--- 178,184 ----
#define USE_ISO_DATES 1
#define USE_SQL_DATES 2
#define USE_GERMAN_DATES 3
+ #define USE_XSD_DATES 4
/* valid DateOrder values */
#define DATEORDER_YMD 0
*** ./src/test/regress/expected/xml.out.orig 2007-02-19 23:36:47.000000000 +0100
--- ./src/test/regress/expected/xml.out 2007-02-19 23:35:28.000000000 +0100
***************
*** 151,156 ****
--- 151,177 ----
<foo>626172</foo>
(1 row)
+ -- check independency and correct formating for date, time and timestamp types
+ SET DateStyle = 'German';
+ SELECT xmlelement(name foo, date '19.02.2007');
+ xmlelement
+ -----------------------
+ <foo>2007-02-19</foo>
+ (1 row)
+
+ SELECT xmlelement(name foo, timestamp with time zone '19.02.2007 23:30:06.789+01');
+ xmlelement
+ ---------------------------------------
+ <foo>2007-02-19T14:30:06.789-08</foo>
+ (1 row)
+
+ SELECT xmlelement(name foo, timestamp without time zone '19.02.2007 23:30:06.789');
+ xmlelement
+ ------------------------------------
+ <foo>2007-02-19T23:30:06.789</foo>
+ (1 row)
+
+ SET DATESTYLE TO DEFAULT;
SELECT xmlparse(content 'abc');
xmlparse
----------
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
