*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
***************
*** 1977,1996 **** January 8 04:05:06 1999 PST
Interval values can also be written as
ISO 8601 time intervals, using the "format with time-unit designators",
! from section 5.5.4.2.1 (PnYnMnDTnHnMnS) or the "alternative format"
! from section 5.5.4.2.2 (PYYYY-MM-DDThh:mm:ss). In both cases, the
! string must start with a 'P', and a 'T'
! separates the date and time parts of the interval.
! In the former format, each 'n' gets replaced by a number, and
! Y represents years,
! M (in the date part) months,
! D months,
! H hours,
! M (in the time part) minutes,
! and S seconds. In the later format, the Y, M, D, h, m, s
! characters are replaced with digits. For example,
! P2Y10M15DT10H30M20S and P0002-10-15T10:30:20 both mean
! 2 years 10 months 15 days 10 hours 30 minutes and 20 seconds.
--- 1977,2046 ----
Interval values can also be written as
ISO 8601 time intervals, using the "format with time-unit designators",
! from section 4.4.4.2.1:
!
!
! P quantity unit quantity unit... T quantity unit...
!
!
! The string must start with a 'P', and may include a
! 'T' to separate the date units from the time units. The
! available unit abbreviations are given in . Units may be
! omitted, and may be specified in any order, but the meaning of
! 'M' depends on whether it is before or after
! 'T'.
!
!
!
! ISO 8601 interval unit abbreviations
!
!
!
! Abbreviation
! Meaning
!
!
!
!
! Y
! Years
!
!
! M
! Months (in the date part)
!
!
! D
! Days
!
!
! H
! Hours
!
!
! M
! Minutes (in the time part)
!
!
! S
! Seconds
!
!
!
!
!
!
! Postgres also supports the "alternative format" from ISO 8601 section
! 4.4.4.2.2, which has the following syntax:
!
!
! P years - months - days Thours : minutes : seconds
!
!
! Again, the string must begin with 'P', and a
! 'T' separates the date and time parts of the interval. In
! this format, the 'T' is mandatory.
***************
*** 2017,2027 **** January 8 04:05:06 1999 PST
'P1Y2M3DT4H5M6S'
! An ISO 8601 "format with time-unit designators" interval meaning the same thing.
'P0001-02-03T04:05:06'
! An ISO 8601 "alternative format" interval meaning the same thing.
--- 2067,2077 ----
'P1Y2M3DT4H5M6S'
! An ISO 8601 "format with time-unit designators" interval with the same meaning as the above
'P0001-02-03T04:05:06'
! An ISO 8601 "alternative format" interval with the same meaning as the above
***************
*** 2243,2260 **** January 8 04:05:06 1999 PST
- interval output looks like the input format, except
- that units like century or
- week are converted to years and days and
- ago is converted to an appropriate sign. In
- ISO mode the output looks like:
-
-
- quantity unit ... days hours:minutes:seconds
-
-
-
-
The date/time styles can be selected by the user using the
SET datestyle command, the parameter in the
--- 2293,2298 ----
***************
*** 2264,2381 **** January 8 04:05:06 1999 PST
(see ) is also available as
a more flexible way to format the date/time output.
-
-
-
- Interval Output
-
-
- interval
- output format
- formatting
-
-
-
- The output format of the interval types can be set to one of the
- four styles sql_standard, iso_8601,
- postgres, or postgres_verbose.
- The default is the postgres format.
- shows examples of each
- output style.
-
-
-
- The sql_standard style will output SQL standard
- interval literal strings where the value of the interval
- value consists of only a year-month component or a datetime
- component (as required by the SQL standard).
- For an interval containing both a year-month and a datetime
- component, the output will be a SQL standard
- unquoted year-month literal string concatenated with a
- SQL standard unquoted datetime literal
- string with a space in between; and '+' and '-' signs added
- to disambiguate mixed-sign intervals.
-
-
-
- The iso_8601 style will output ISO 8601
- time intervals using the "format with time-unit designators"
- This format always starts with the character
- 'P', followed by a string of values followed by single
- character time-unit designators. A 'T' separates the
- date and time parts of the interval.
- In this format, 'n' gets replaced by a number, and
- Y represents years,
- M (in the date part) months,
- D months,
- H hours,
- M (in the time part) minutes,
- and S seconds.
-
-
-
- The postgres style will output intervals
- matching those output by PostgreSQL releases prior to 8.4
- when the
- parameter was set to ISO.
-
-
-
- The postgres_verbose style will output intervals
- matching those output by PostgreSQL releases prior to 8.4
- when the
- parameter was set to SQL.
-
-
-
- Interval Style Example
-
-
-
- Style Specification
- Year-Month Interval
- DateTime Interval
- Nonstandardrd Extended Interval
-
-
-
-
- sql_standard
- 1-2
- 3 4:05:06
- -1-2 +3 -4:05:06
-
-
- iso_8601
- P1Y2M
- P3DT4H5M6
- P-1Y-2M3DT-4H-5M-6
-
-
- postgres
- 1 year 2 mons
- 3 days 04:05:06
- -1 year -2 mons +3 days -04:05:06
-
-
- postgres_verbose
- @ 1 year 2 mons
- @ 3 days 4 hours 5 mins 6 secs
- @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago
-
-
-
-
-
-
- Note that sql_standard style will only produce strictly
- standards-conforming interval literals when given a strictly
- SQL standard interval value - meaning that
- it needs to be a pure year-month or datetime interval and not
- mix positive and negative components.
-
--- 2302,2416 ----
(see ) is also available as
a more flexible way to format the date/time output.
+
+ Interval Output
+
+
+ interval
+ output format
+ formatting
+
+
+
+ The output format of the interval types can be set to one of the four
+ styles sql_standard, iso_8601,
+ postgres, or postgres_verbose using the
+ SET intervalstyle command. The default setting is the
+ postgres format.
+ shows examples of each output style.
+
+
+
+ The sql_standard style will output SQL standard
+ interval literal strings where the value of the interval
+ value consists of only a year-month component or a datetime
+ component (as required by the SQL standard).
+ For an interval containing both a year-month and a datetime
+ component, the output will be a SQL standard
+ unquoted year-month literal string concatenated with a
+ SQL standard unquoted datetime literal
+ string with a space in between; and '+' and '-' signs added
+ to disambiguate mixed-sign intervals.
+
+
+
+ The iso_8601 style will output ISO 8601 time intervals using
+ the "format with time-unit designators". This format always starts with
+ the character 'P', followed by a string of values followed by
+ single character time-unit designators. A 'T' separates the
+ date and time parts of the interval.
+ In this format
+ Y represents years,
+ M (in the date part) months,
+ D months,
+ H hours,
+ M (in the time part) minutes,
+ and S seconds.
+
+
+
+ The postgres style will output intervals
+ matching those output by PostgreSQL releases prior to 8.4
+ when the
+ parameter was set to ISO.
+
+
+
+ The postgres_verbose style will output intervals
+ matching those output by PostgreSQL releases prior to 8.4
+ when the
+ parameter was set to SQL.
+
+
+
+ Interval Style Example
+
+
+
+ Style Specification
+ Year-Month Interval
+ DateTime Interval
+ Nonstandard Extended Interval
+
+
+
+
+ sql_standard
+ 1-2
+ 3 4:05:06
+ -1-2 +3 -4:05:06
+
+
+ iso_8601
+ P1Y2M
+ P3DT4H5M6
+ P-1Y-2M3DT-4H-5M-6
+
+
+ postgres
+ 1 year 2 mons
+ 3 days 04:05:06
+ -1 year -2 mons +3 days -04:05:06
+
+
+ postgres_verbose
+ @ 1 year 2 mons
+ @ 3 days 4 hours 5 mins 6 secs
+ @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago
+
+
+
+
+
+
+ Note that sql_standard style will only produce strictly
+ standards-conforming interval literals when given a strictly
+ SQL standard interval value - meaning that it needs to
+ be a pure year-month or datetime interval and not mix positive and
+ negative components.
+
+
*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 2724,2736 **** DecodeSpecial(int field, char *lowtoken, int *val)
/*
! * Small helper functions to avoid cut&paste code in DecodeIso8601Interval
*/
static void
AdjustFractionalSeconds(double fval, struct pg_tm * tm, fsec_t *fsec, int scale)
{
int sec;
! if (fval == 0) return;
fval *= scale;
sec = fval;
tm->tm_sec += sec;
--- 2724,2738 ----
/*
! * Small helper functions to avoid cut&paste code in DecodeISO8601Interval
*/
static void
AdjustFractionalSeconds(double fval, struct pg_tm * tm, fsec_t *fsec, int scale)
{
int sec;
!
! if (fval == 0)
! return;
fval *= scale;
sec = fval;
tm->tm_sec += sec;
***************
*** 2745,2751 **** static void
AdjustFractionalDays(double fval, struct pg_tm * tm, fsec_t *fsec, int scale)
{
int extra_days;
! if (fval == 0) return;
fval *= scale;
extra_days = fval;
tm->tm_mday += extra_days;
--- 2747,2755 ----
AdjustFractionalDays(double fval, struct pg_tm * tm, fsec_t *fsec, int scale)
{
int extra_days;
!
! if (fval == 0)
! return;
fval *= scale;
extra_days = fval;
tm->tm_mday += extra_days;
***************
*** 2785,2796 **** DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
tm->tm_sec = 0;
*fsec = 0;
! if (strlen(str)<3 || !(str[0] == 'P'))
! {
return DTERR_BAD_FORMAT;
! }
str++;
-
while (*str)
{
if (*str == 'T') /* T indicates the beginning of the time part */
--- 2789,2798 ----
tm->tm_sec = 0;
*fsec = 0;
! if (strlen(str) < 3 || !(str[0] == 'P'))
return DTERR_BAD_FORMAT;
!
str++;
while (*str)
{
if (*str == 'T') /* T indicates the beginning of the time part */
***************
*** 2799,2805 **** DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
str++;
continue;
}
! if (! (isdigit(*str) || *str == '-' || *str == '.') )
return DTERR_BAD_FORMAT;
negative = (*str == '-');
--- 2801,2807 ----
str++;
continue;
}
! if (!(isdigit(*str) || *str == '-' || *str == '.') )
return DTERR_BAD_FORMAT;
negative = (*str == '-');
***************
*** 2832,2838 **** DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
break;
case 'T': /* ISO 8601 5.5.4.2.2 Alternative Format / Basic */
tm->tm_year += val / 10000;
! tm->tm_mon += (val/100) % 100;
tm->tm_mday += val % 100;
AdjustFractionalSeconds(fval, tm, fsec, SECS_PER_DAY);
datepart = false;
--- 2834,2840 ----
break;
case 'T': /* ISO 8601 5.5.4.2.2 Alternative Format / Basic */
tm->tm_year += val / 10000;
! tm->tm_mon += (val / 100) % 100;
tm->tm_mday += val % 100;
AdjustFractionalSeconds(fval, tm, fsec, SECS_PER_DAY);
datepart = false;
***************
*** 2847,2853 **** DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
fval -= val;
AdjustFractionalDays(fval, tm, fsec, DAYS_PER_MONTH);
! if (*str != '-') return DTERR_BAD_FORMAT;
str++;
fval = strtod(str, &str);
--- 2849,2856 ----
fval -= val;
AdjustFractionalDays(fval, tm, fsec, DAYS_PER_MONTH);
! if (*str != '-')
! return DTERR_BAD_FORMAT;
str++;
fval = strtod(str, &str);
***************
*** 2856,2862 **** DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
fval -= val;
AdjustFractionalSeconds(fval, tm, fsec, SECS_PER_DAY);
! if (*str != 'T') return DTERR_BAD_FORMAT;
str++;
datepart = false;
--- 2859,2866 ----
fval -= val;
AdjustFractionalSeconds(fval, tm, fsec, SECS_PER_DAY);
! if (*str != 'T')
! return DTERR_BAD_FORMAT;
str++;
datepart = false;
***************
*** 2897,2903 **** DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
tm->tm_min += val;
AdjustFractionalSeconds(fval, tm, fsec, SECS_PER_MINUTE);
! if (*str != ':') return DTERR_BAD_FORMAT;
str++;
fval = strtod(str, &str);
--- 2901,2908 ----
tm->tm_min += val;
AdjustFractionalSeconds(fval, tm, fsec, SECS_PER_MINUTE);
! if (*str != ':')
! return DTERR_BAD_FORMAT;
str++;
fval = strtod(str, &str);
***************
*** 2906,2912 **** DecodeISO8601Interval(char *str, struct pg_tm * tm, fsec_t *fsec)
tm->tm_sec += val;
AdjustFractionalSeconds(fval, tm, fsec, 1);
! if (*str != 0) return DTERR_BAD_FORMAT;
return 0;
default:
--- 2911,2918 ----
tm->tm_sec += val;
AdjustFractionalSeconds(fval, tm, fsec, 1);
! if (*str != 0)
! return DTERR_BAD_FORMAT;
return 0;
default:
***************
*** 3825,3831 **** EncodeDateTime(struct pg_tm * tm, fsec_t fsec, int *tzp, char **tzn, int style,
static char *
AddISO8601IntervalPart(char * cp, int value, char units)
{
! if (value==0) return cp;
return cp + sprintf(cp, "%d%c", value, units);
}
--- 3831,3838 ----
static char *
AddISO8601IntervalPart(char * cp, int value, char units)
{
! if (value == 0)
! return cp;
return cp + sprintf(cp, "%d%c", value, units);
}
***************
*** 3833,3841 **** static void
AppendSeconds(char * cp, int sec, fsec_t fsec, bool fillzeros)
{
if (fsec == 0)
- {
sprintf(cp, fillzeros ? "%02d" : "%d" , abs(sec));
- }
else
{
#ifdef HAVE_INT64_TIMESTAMP
--- 3840,3846 ----
***************
*** 3848,3854 **** AppendSeconds(char * cp, int sec, fsec_t fsec, bool fillzeros)
}
-
/* EncodeInterval()
* Interpret time structure as a delta time and convert to string.
*
--- 3853,3858 ----
***************
*** 3972,3985 **** EncodeInterval(struct pg_tm * tm, fsec_t fsec, int style, char *str)
cp = AddISO8601IntervalPart(cp, mon , 'M');
cp = AddISO8601IntervalPart(cp, mday, 'D');
if ((hour != 0) || (min != 0) || (sec != 0) || (fsec != 0))
- {
cp += sprintf(cp, "T");
! }
cp = AddISO8601IntervalPart(cp, hour, 'H');
cp = AddISO8601IntervalPart(cp, min , 'M');
if ((sec != 0) || (fsec != 0))
{
! cp += sprintf(cp, "%s", (sec<0 || fsec<0) ? "-" : "");
AppendSeconds(cp, sec, fsec, false);
cp += strlen(cp);
cp += sprintf(cp, "S");
--- 3976,3988 ----
cp = AddISO8601IntervalPart(cp, mon , 'M');
cp = AddISO8601IntervalPart(cp, mday, 'D');
if ((hour != 0) || (min != 0) || (sec != 0) || (fsec != 0))
cp += sprintf(cp, "T");
!
cp = AddISO8601IntervalPart(cp, hour, 'H');
cp = AddISO8601IntervalPart(cp, min , 'M');
if ((sec != 0) || (fsec != 0))
{
! cp += sprintf(cp, "%s", (sec < 0 || fsec < 0) ? "-" : "");
AppendSeconds(cp, sec, fsec, false);
cp += strlen(cp);
cp += sprintf(cp, "S");