Hi,
I got the advice to split the patches for:
- fixing the to_char() function
- changing the to_date()/to_timestamp() behaviour
So I appended the split patches.
Kind regards,
Mark Lorenz
From 4e35bd88bef1916e7d11ad0776b3075e3183f7d0 Mon Sep 17 00:00:00 2001
From: Mark Lorenz <mark.lor...@four-two.de>
Date: Fri, 20 Dec 2019 14:30:41 +0100
Subject: [PATCH] change to_date()/to_timestamp() behaviour with 'YYYY-WW-D'
pattern
Currently, the D part is ignored at non-ISO week pattern. Now the D
pattern works as well.
Added regression tests
---
src/backend/utils/adt/formatting.c | 44 +++++++++++---
src/backend/utils/adt/timestamp.c | 84 ++++++++++++++++++++++++++
src/include/utils/timestamp.h | 6 ++
src/test/regress/expected/horology.out | 26 +++++++-
src/test/regress/sql/horology.sql | 4 ++
5 files changed, 156 insertions(+), 8 deletions(-)
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 8fcbc22..603c51c 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -4494,21 +4494,51 @@ do_to_timestamp(text *date_txt, text *fmt, bool std,
fmask |= DTK_DATE_M;
}
else
- tmfc.ddd = (tmfc.ww - 1) * 7 + 1;
+ {
+ /*
+ * If tmfc.d is not set, then the date is left at the beginning of
+ * the week (Sunday).
+ */
+ if (tmfc.d)
+ weekdate2date(tmfc.ww, tmfc.d, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+ else
+ week2date(tmfc.ww, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+ fmask |= DTK_DATE_M;
+ }
+ }
+
+ if (tmfc.mm)
+ {
+ tm->tm_mon = tmfc.mm;
+ fmask |= DTK_M(MONTH);
}
if (tmfc.w)
- tmfc.dd = (tmfc.w - 1) * 7 + 1;
+ {
+ /* if tmfc.mm is set, the date can be calculated */
+ if (tmfc.mm)
+ {
+ /*
+ * If tmfc.d is not set, then the date is left at the beginning of
+ * the week (Sunday).
+ */
+ if (tmfc.d)
+ monthweekdate2date(tmfc.mm, tmfc.w, tmfc.d, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+ else
+ monthweek2date(tmfc.mm, tmfc.w, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+
+ fmask |= DTK_DATE_M;
+ tmfc.dd = tm->tm_mday;
+ }
+ else
+ tmfc.dd = (tmfc.w - 1) * 7 + 1;
+ }
+
if (tmfc.dd)
{
tm->tm_mday = tmfc.dd;
fmask |= DTK_M(DAY);
}
- if (tmfc.mm)
- {
- tm->tm_mon = tmfc.mm;
- fmask |= DTK_M(MONTH);
- }
if (tmfc.ddd && (tm->tm_mon <= 1 || tm->tm_mday <= 1))
{
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 945b8f8..3e2f499 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -4264,6 +4264,90 @@ interval_trunc(PG_FUNCTION_ARGS)
PG_RETURN_INTERVAL_P(result);
}
+/* monthweek2j()
+ *
+ * Return the Julian day which corresponds to the first day (Sunday) of the given month/year and week.
+ * Julian days are used to convert between ISO week dates and Gregorian dates.
+ */
+int
+monthweek2j(int year, int month, int week)
+{
+ int day0,
+ day1;
+
+ /* first day of given month */
+ day1 = date2j(year, month, 1);
+
+ // day0 == offset to first day of week (Sunday)
+ day0 = j2day(day1);
+
+ return ((week - 1) * 7) + (day1 - day0);
+}
+
+/* monthweek2date()
+ * Convert week of month and year number to date.
+ */
+void
+monthweek2date(int month, int wom, int *year, int *mon, int *mday)
+{
+ j2date(monthweek2j(*year, month, wom), year, mon, mday);
+}
+
+/* monthweek2date()
+ *
+ * Convert a week of month date (year, month, week of month) into a Gregorian date.
+ * Gregorian day of week sent so weekday strings can be supplied.
+ * Populates year, mon, and mday with the correct Gregorian values.
+ */
+void
+monthweekdate2date(int month, int wom, int wday, int *year, int *mon, int *mday)
+{
+ int jday;
+
+ jday = monthweek2j(*year, month, wom);
+ jday += wday - 1;
+
+ j2date(jday, year, mon, mday);
+}
+
+/* week2j()
+ *
+ * Return the Julian day which corresponds to the first day (Sunday) of the given year and week.
+ * Julian days are used to convert between ISO week dates and Gregorian dates.
+ */
+int
+week2j(int year, int week)
+{
+ /* calculating the Julian Day from first month of current year */
+ return monthweek2j(year, 1, week);
+}
+
+/* week2date()
+ * Convert week of year number to date.
+ */
+void
+week2date(int woy, int *year, int *mon, int *mday)
+{
+ j2date(week2j(*year, woy), year, mon, mday);
+}
+
+/* weekdate2date()
+ *
+ * Convert a week date (year, week) into a Gregorian date.
+ * Gregorian day of week sent so weekday strings can be supplied.
+ * Populates year, mon, and mday with the correct Gregorian values.
+ */
+void
+weekdate2date(int woy, int wday, int *year, int *mon, int *mday)
+{
+ int jday;
+
+ jday = week2j(*year, woy);
+ jday += wday - 1;
+
+ j2date(jday, year, mon, mday);
+}
+
/* isoweek2j()
*
* Return the Julian day which corresponds to the first day (Monday) of the given ISO 8601 year and week.
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index 7652b41..4c417fc 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -100,6 +100,12 @@ extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2);
extern TimestampTz timestamp2timestamptz_opt_overflow(Timestamp timestamp,
int *overflow);
+extern int monthweek2j(int year, int month, int week);
+extern void monthweek2date(int month, int wom, int *year, int *mon, int *mday);
+extern void monthweekdate2date(int month, int wom, int wday, int *year, int *mon, int *mday);
+extern int week2j(int year, int week);
+extern void week2date(int woy, int *year, int *mon, int *mday);
+extern void weekdate2date(int woy, int wday, int *year, int *mon, int *mday);
extern int isoweek2j(int year, int week);
extern void isoweek2date(int woy, int *year, int *mon, int *mday);
extern void isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday);
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 6b53876..d0dcc2a 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -2681,7 +2681,31 @@ SELECT to_timestamp('995-1116', 'YYY-MMDD');
SELECT to_timestamp('2005426', 'YYYYWWD');
to_timestamp
------------------------------
- Sat Oct 15 00:00:00 2005 PDT
+ Fri Oct 14 00:00:00 2005 PDT
+(1 row)
+
+SELECT to_timestamp('2019011', 'YYYYWWD');
+ to_timestamp
+------------------------------
+ Sun Dec 30 00:00:00 2018 PST
+(1 row)
+
+SELECT to_timestamp('2019013', 'YYYYWWD');
+ to_timestamp
+------------------------------
+ Tue Jan 01 00:00:00 2019 PST
+(1 row)
+
+SELECT to_timestamp('2019017', 'YYYYWWD');
+ to_timestamp
+------------------------------
+ Sat Jan 05 00:00:00 2019 PST
+(1 row)
+
+SELECT to_timestamp('2019021', 'YYYYWWD');
+ to_timestamp
+------------------------------
+ Sun Jan 06 00:00:00 2019 PST
(1 row)
SELECT to_timestamp('2005300', 'YYYYDDD');
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index f7a9da1..2ae64af 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -372,6 +372,10 @@ SELECT to_timestamp('95-1116', 'YY-MMDD');
SELECT to_timestamp('995-1116', 'YYY-MMDD');
SELECT to_timestamp('2005426', 'YYYYWWD');
+SELECT to_timestamp('2019011', 'YYYYWWD');
+SELECT to_timestamp('2019013', 'YYYYWWD');
+SELECT to_timestamp('2019017', 'YYYYWWD');
+SELECT to_timestamp('2019021', 'YYYYWWD');
SELECT to_timestamp('2005300', 'YYYYDDD');
--
2.20.1
From 1873819915405732121ffb2712a819b325330637 Mon Sep 17 00:00:00 2001
From: Mark Lorenz <mark.lor...@four-two.de>
Date: Fri, 20 Dec 2019 14:27:47 +0100
Subject: [PATCH] fix to_char() issue with 'YYYY-WW-D' pattern
Currently, calculating the right non-ISO week, the offset of the first
day of the year is not considered. So in some cases the week number is
calculated wrong:
e.g. If the year starts at Thursday, all Sundays, Mondays, Tuesdays are
counted to the previous week.
This has been fixed.
Added some regression test; changed existing ones, because they worked
with the wrong behaviour.
---
src/backend/utils/adt/formatting.c | 2 +-
src/backend/utils/adt/timestamp.c | 37 ++++
src/include/utils/timestamp.h | 1 +
src/test/regress/expected/horology.out | 40 +++-
src/test/regress/expected/timestamp.out | 238 ++++++++++++---------
src/test/regress/expected/timestamptz.out | 242 +++++++++++++---------
src/test/regress/sql/timestamp.sql | 2 +
src/test/regress/sql/timestamptz.sql | 2 +
8 files changed, 358 insertions(+), 206 deletions(-)
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 8fcbc22..6101dd0 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -3017,7 +3017,7 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
break;
case DCH_WW:
sprintf(s, "%0*d", S_FM(n->suffix) ? 0 : 2,
- (tm->tm_yday - 1) / 7 + 1);
+ date2week(tm->tm_year, tm->tm_mon, tm->tm_mday));
if (S_THth(n->suffix))
str_numth(s, s, S_TH_TYPE(n->suffix));
s += strlen(s);
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 945b8f8..486a262 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -4316,6 +4316,43 @@ isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday)
j2date(jday, year, mon, mday);
}
+/* date2week()
+ *
+ * Returns Gregorian week number of year.
+ */
+int
+date2week(int year, int mon, int mday)
+{
+ float8 result;
+ int dayn,
+ day0,
+ ydayn,
+ weekdayn,
+ weekday0;
+
+ /* current day */
+ dayn = date2j(year, mon, mday);
+
+ /* first day of current year */
+ day0 = date2j(year, 1, 1);
+
+ /* current day of year */
+ ydayn = dayn - day0 + 1;
+
+ /* first weekday of current day */
+ weekday0 = (day0 + 1) % 7;
+
+ /* weekday of current day */
+ weekdayn = (dayn + 1) % 7;
+
+ result = (ydayn + 7 - weekdayn - 1) / 7;
+
+ if (weekday0 > 0)
+ result += 1;
+
+ return (int) result;
+}
+
/* date2isoweek()
*
* Returns ISO week number of year.
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index 7652b41..4571f03 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -103,6 +103,7 @@ extern TimestampTz timestamp2timestamptz_opt_overflow(Timestamp timestamp,
extern int isoweek2j(int year, int week);
extern void isoweek2date(int woy, int *year, int *mon, int *mday);
extern void isoweekdate2date(int isoweek, int wday, int *year, int *mon, int *mday);
+extern int date2week(int year, int mon, int mday);
extern int date2isoweek(int year, int mon, int mday);
extern int date2isoyear(int year, int mon, int mday);
extern int date2isoyearday(int year, int mon, int mday);
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 6b53876..2840963 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -531,7 +531,9 @@ SELECT '' AS "64", d1 + interval '1 year' AS one_year FROM TIMESTAMP_TBL;
| Mon Jan 01 17:32:01 2001
| Mon Dec 31 17:32:01 2001
| Tue Jan 01 17:32:01 2002
-(65 rows)
+ | Sun Dec 31 17:32:01 2017
+ | Mon Jan 01 17:32:01 2018
+(67 rows)
SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMP_TBL;
64 | one_year
@@ -601,7 +603,9 @@ SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMP_TBL;
| Fri Jan 01 17:32:01 1999
| Fri Dec 31 17:32:01 1999
| Sat Jan 01 17:32:01 2000
-(65 rows)
+ | Thu Dec 31 17:32:01 2015
+ | Fri Jan 01 17:32:01 2016
+(67 rows)
SELECT timestamp with time zone '1996-03-01' - interval '1 second' AS "Feb 29";
Feb 29
@@ -777,7 +781,9 @@ SELECT '' AS "64", d1 + interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL;
| Mon Jan 01 17:32:01 2001 PST
| Mon Dec 31 17:32:01 2001 PST
| Tue Jan 01 17:32:01 2002 PST
-(66 rows)
+ | Sun Dec 31 17:32:01 2017 PST
+ | Mon Jan 01 17:32:01 2018 PST
+(68 rows)
SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL;
64 | one_year
@@ -848,7 +854,9 @@ SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMPTZ_TBL;
| Fri Jan 01 17:32:01 1999 PST
| Fri Dec 31 17:32:01 1999 PST
| Sat Jan 01 17:32:01 2000 PST
-(66 rows)
+ | Thu Dec 31 17:32:01 2015 PST
+ | Fri Jan 01 17:32:01 2016 PST
+(68 rows)
--
-- time, interval arithmetic
@@ -2152,7 +2160,9 @@ SELECT '' AS "64", d1 AS us_postgres FROM TIMESTAMP_TBL;
| Sat Jan 01 17:32:01 2000
| Sun Dec 31 17:32:01 2000
| Mon Jan 01 17:32:01 2001
-(65 rows)
+ | Sat Dec 31 17:32:01 2016
+ | Sun Jan 01 17:32:01 2017
+(67 rows)
SET DateStyle TO 'US,ISO';
SELECT '' AS "64", d1 AS us_iso FROM TIMESTAMP_TBL;
@@ -2223,7 +2233,9 @@ SELECT '' AS "64", d1 AS us_iso FROM TIMESTAMP_TBL;
| 2000-01-01 17:32:01
| 2000-12-31 17:32:01
| 2001-01-01 17:32:01
-(65 rows)
+ | 2016-12-31 17:32:01
+ | 2017-01-01 17:32:01
+(67 rows)
SET DateStyle TO 'US,SQL';
SHOW DateStyle;
@@ -2300,7 +2312,9 @@ SELECT '' AS "64", d1 AS us_sql FROM TIMESTAMP_TBL;
| 01/01/2000 17:32:01
| 12/31/2000 17:32:01
| 01/01/2001 17:32:01
-(65 rows)
+ | 12/31/2016 17:32:01
+ | 01/01/2017 17:32:01
+(67 rows)
SET DateStyle TO 'European,Postgres';
SHOW DateStyle;
@@ -2384,8 +2398,10 @@ SELECT '' AS "65", d1 AS european_postgres FROM TIMESTAMP_TBL;
| Sat 01 Jan 17:32:01 2000
| Sun 31 Dec 17:32:01 2000
| Mon 01 Jan 17:32:01 2001
+ | Sat 31 Dec 17:32:01 2016
+ | Sun 01 Jan 17:32:01 2017
| Thu 13 Jun 00:00:00 1957
-(66 rows)
+(68 rows)
SET DateStyle TO 'European,ISO';
SHOW DateStyle;
@@ -2462,8 +2478,10 @@ SELECT '' AS "65", d1 AS european_iso FROM TIMESTAMP_TBL;
| 2000-01-01 17:32:01
| 2000-12-31 17:32:01
| 2001-01-01 17:32:01
+ | 2016-12-31 17:32:01
+ | 2017-01-01 17:32:01
| 1957-06-13 00:00:00
-(66 rows)
+(68 rows)
SET DateStyle TO 'European,SQL';
SHOW DateStyle;
@@ -2540,8 +2558,10 @@ SELECT '' AS "65", d1 AS european_sql FROM TIMESTAMP_TBL;
| 01/01/2000 17:32:01
| 31/12/2000 17:32:01
| 01/01/2001 17:32:01
+ | 31/12/2016 17:32:01
+ | 01/01/2017 17:32:01
| 13/06/1957 00:00:00
-(66 rows)
+(68 rows)
RESET DateStyle;
--
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 39a4d49..bf3844a 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -158,6 +158,8 @@ INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1999');
INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2000');
INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 2000');
INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2001');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 2016');
+INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2017');
-- Currently unsupported syntax and ranges
INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 -0097');
ERROR: time zone displacement out of range: "Feb 16 17:32:01 -0097"
@@ -235,7 +237,9 @@ SELECT '' AS "64", d1 FROM TIMESTAMP_TBL;
| Sat Jan 01 17:32:01 2000
| Sun Dec 31 17:32:01 2000
| Mon Jan 01 17:32:01 2001
-(65 rows)
+ | Sat Dec 31 17:32:01 2016
+ | Sun Jan 01 17:32:01 2017
+(67 rows)
-- Check behavior at the lower boundary of the timestamp range
SELECT '4714-11-24 00:00:00 BC'::timestamp;
@@ -303,7 +307,9 @@ SELECT '' AS "48", d1 FROM TIMESTAMP_TBL
| Sat Jan 01 17:32:01 2000
| Sun Dec 31 17:32:01 2000
| Mon Jan 01 17:32:01 2001
-(49 rows)
+ | Sat Dec 31 17:32:01 2016
+ | Sun Jan 01 17:32:01 2017
+(51 rows)
SELECT '' AS "15", d1 FROM TIMESTAMP_TBL
WHERE d1 < timestamp without time zone '1997-01-02';
@@ -401,7 +407,9 @@ SELECT '' AS "63", d1 FROM TIMESTAMP_TBL
| Sat Jan 01 17:32:01 2000
| Sun Dec 31 17:32:01 2000
| Mon Jan 01 17:32:01 2001
-(64 rows)
+ | Sat Dec 31 17:32:01 2016
+ | Sun Jan 01 17:32:01 2017
+(66 rows)
SELECT '' AS "16", d1 FROM TIMESTAMP_TBL
WHERE d1 <= timestamp without time zone '1997-01-02';
@@ -479,7 +487,9 @@ SELECT '' AS "49", d1 FROM TIMESTAMP_TBL
| Sat Jan 01 17:32:01 2000
| Sun Dec 31 17:32:01 2000
| Mon Jan 01 17:32:01 2001
-(50 rows)
+ | Sat Dec 31 17:32:01 2016
+ | Sun Jan 01 17:32:01 2017
+(52 rows)
SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
@@ -540,7 +550,9 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
| @ 1094 days 17 hours 32 mins 1 sec
| @ 1459 days 17 hours 32 mins 1 sec
| @ 1460 days 17 hours 32 mins 1 sec
-(55 rows)
+ | @ 7303 days 17 hours 32 mins 1 sec
+ | @ 7304 days 17 hours 32 mins 1 sec
+(57 rows)
SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc;
date_trunc_week | week_trunc
@@ -610,7 +622,9 @@ SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff
| @ 1094 days 17 hours 32 mins 1 sec
| @ 1459 days 17 hours 32 mins 1 sec
| @ 1460 days 17 hours 32 mins 1 sec
-(55 rows)
+ | @ 7303 days 17 hours 32 mins 1 sec
+ | @ 7304 days 17 hours 32 mins 1 sec
+(57 rows)
-- DATE_PART (timestamp_part)
SELECT d1 as "timestamp",
@@ -685,7 +699,9 @@ SELECT d1 as "timestamp",
Sat Jan 01 17:32:01 2000 | 2000 | 1 | 1 | 17 | 32 | 1
Sun Dec 31 17:32:01 2000 | 2000 | 12 | 31 | 17 | 32 | 1
Mon Jan 01 17:32:01 2001 | 2001 | 1 | 1 | 17 | 32 | 1
-(65 rows)
+ Sat Dec 31 17:32:01 2016 | 2016 | 12 | 31 | 17 | 32 | 1
+ Sun Jan 01 17:32:01 2017 | 2017 | 1 | 1 | 17 | 32 | 1
+(67 rows)
SELECT d1 as "timestamp",
date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
@@ -758,7 +774,9 @@ SELECT d1 as "timestamp",
Sat Jan 01 17:32:01 2000 | 1 | 1000 | 1000000
Sun Dec 31 17:32:01 2000 | 4 | 1000 | 1000000
Mon Jan 01 17:32:01 2001 | 1 | 1000 | 1000000
-(65 rows)
+ Sat Dec 31 17:32:01 2016 | 4 | 1000 | 1000000
+ Sun Jan 01 17:32:01 2017 | 1 | 1000 | 1000000
+(67 rows)
SELECT d1 as "timestamp",
date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
@@ -832,7 +850,9 @@ SELECT d1 as "timestamp",
Sat Jan 01 17:32:01 2000 | 1999 | 52 | 6 | 6 | 1
Sun Dec 31 17:32:01 2000 | 2000 | 52 | 7 | 0 | 366
Mon Jan 01 17:32:01 2001 | 2001 | 1 | 1 | 1 | 1
-(65 rows)
+ Sat Dec 31 17:32:01 2016 | 2016 | 52 | 6 | 6 | 366
+ Sun Jan 01 17:32:01 2017 | 2016 | 52 | 7 | 0 | 1
+(67 rows)
SELECT d1 as "timestamp",
date_part( 'decade', d1) AS decade,
@@ -907,7 +927,9 @@ SELECT d1 as "timestamp",
Sat Jan 01 17:32:01 2000 | 200 | 20 | 2 | 2451546
Sun Dec 31 17:32:01 2000 | 200 | 20 | 2 | 2451911
Mon Jan 01 17:32:01 2001 | 200 | 21 | 3 | 2451912
-(65 rows)
+ Sat Dec 31 17:32:01 2016 | 201 | 21 | 3 | 2457755
+ Sun Jan 01 17:32:01 2017 | 201 | 21 | 3 | 2457756
+(67 rows)
-- TO_CHAR()
SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
@@ -979,7 +1001,9 @@ SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM M
| SATURDAY Saturday saturday SAT Sat sat JANUARY January january I JAN Jan jan
| SUNDAY Sunday sunday SUN Sun sun DECEMBER December december XII DEC Dec dec
| MONDAY Monday monday MON Mon mon JANUARY January january I JAN Jan jan
-(65 rows)
+ | SATURDAY Saturday saturday SAT Sat sat DECEMBER December december XII DEC Dec dec
+ | SUNDAY Sunday sunday SUN Sun sun JANUARY January january I JAN Jan jan
+(67 rows)
SELECT '' AS to_char_2, to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM')
FROM TIMESTAMP_TBL;
@@ -1050,7 +1074,9 @@ SELECT '' AS to_char_2, to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth F
| SATURDAY Saturday saturday JANUARY January january I
| SUNDAY Sunday sunday DECEMBER December december XII
| MONDAY Monday monday JANUARY January january I
-(65 rows)
+ | SATURDAY Saturday saturday DECEMBER December december XII
+ | SUNDAY Sunday sunday JANUARY January january I
+(67 rows)
SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
FROM TIMESTAMP_TBL;
@@ -1059,53 +1085,53 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
|
|
| 1,970 1970 970 70 0 20 1 01 01 001 01 5 2440588
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
| 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
| 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 2 06 24 161 10 3 2450610
| 2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175
- | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
- | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
- | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
- | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
- | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 042 11 3 2450491
+ | 2,000 2000 000 00 0 20 1 03 12 075 15 4 2451619
+ | 2,000 2000 000 00 0 20 1 03 12 075 15 4 2451619
+ | 2,000 2000 000 00 0 20 1 03 12 075 15 4 2451619
+ | 2,000 2000 000 00 0 20 1 03 12 075 15 4 2451619
+ | 2,000 2000 000 00 0 20 1 03 12 075 15 4 2451619
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 2 06 24 161 10 3 2450610
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 042 11 3 2450491
| 1,997 1997 997 97 7 20 1 02 07 043 12 4 2450492
| 1,997 1997 997 97 7 20 1 02 07 044 13 5 2450493
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
- | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
- | 0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042
+ | 1,997 1997 997 97 7 20 1 02 08 047 16 1 2450496
+ | 0,097 0097 097 97 7 -01 1 02 08 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
- | 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
+ | 1,097 1097 097 97 7 11 1 02 08 047 16 3 2121778
| 1,697 1697 697 97 7 17 1 02 07 047 16 7 2340924
| 1,797 1797 797 97 7 18 1 02 07 047 16 5 2377448
- | 1,897 1897 897 97 7 19 1 02 07 047 16 3 2413972
- | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
+ | 1,897 1897 897 97 7 19 1 02 08 047 16 3 2413972
+ | 1,997 1997 997 97 7 20 1 02 08 047 16 1 2450496
| 2,097 2097 097 97 7 21 1 02 07 047 16 7 2487021
| 1,996 1996 996 96 6 20 1 02 09 059 28 4 2450142
| 1,996 1996 996 96 6 20 1 02 09 060 29 5 2450143
@@ -1115,13 +1141,15 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
| 1,997 1997 997 97 7 20 1 01 01 001 01 4 2450450
| 1,997 1997 997 97 7 20 1 02 09 059 28 6 2450508
| 1,997 1997 997 97 7 20 1 03 09 060 01 7 2450509
- | 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
+ | 1,997 1997 997 97 7 20 4 12 53 364 30 3 2450813
| 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
| 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
| 2,000 2000 000 00 0 20 1 01 01 001 01 7 2451545
- | 2,000 2000 000 00 0 20 4 12 53 366 31 1 2451910
+ | 2,000 2000 000 00 0 20 4 12 54 366 31 1 2451910
| 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911
-(65 rows)
+ | 2,016 2016 016 16 6 21 4 12 53 366 31 7 2457754
+ | 2,017 2017 017 17 7 21 1 01 01 001 01 1 2457755
+(67 rows)
SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ')
FROM TIMESTAMP_TBL;
@@ -1130,53 +1158,53 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
|
|
| 1,970 1970 970 70 0 20 1 1 1 1 1 5 2440588
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
| 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
| 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 2 6 24 161 10 3 2450610
| 2,001 2001 1 1 1 21 3 9 38 265 22 7 2452175
- | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
- | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
- | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
- | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
- | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 42 11 3 2450491
+ | 2,000 2000 0 0 0 20 1 3 12 75 15 4 2451619
+ | 2,000 2000 0 0 0 20 1 3 12 75 15 4 2451619
+ | 2,000 2000 0 0 0 20 1 3 12 75 15 4 2451619
+ | 2,000 2000 0 0 0 20 1 3 12 75 15 4 2451619
+ | 2,000 2000 0 0 0 20 1 3 12 75 15 4 2451619
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 2 6 24 161 10 3 2450610
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 42 11 3 2450491
| 1,997 1997 997 97 7 20 1 2 7 43 12 4 2450492
| 1,997 1997 997 97 7 20 1 2 7 44 13 5 2450493
| 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
| 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
- | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
- | 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042
+ | 1,997 1997 997 97 7 20 1 2 8 47 16 1 2450496
+ | 0,097 97 97 97 7 -1 1 2 8 47 16 3 1686042
| 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
| 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
- | 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778
+ | 1,097 1097 97 97 7 11 1 2 8 47 16 3 2121778
| 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
| 1,797 1797 797 97 7 18 1 2 7 47 16 5 2377448
- | 1,897 1897 897 97 7 19 1 2 7 47 16 3 2413972
- | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
+ | 1,897 1897 897 97 7 19 1 2 8 47 16 3 2413972
+ | 1,997 1997 997 97 7 20 1 2 8 47 16 1 2450496
| 2,097 2097 97 97 7 21 1 2 7 47 16 7 2487021
| 1,996 1996 996 96 6 20 1 2 9 59 28 4 2450142
| 1,996 1996 996 96 6 20 1 2 9 60 29 5 2450143
@@ -1186,13 +1214,15 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
| 1,997 1997 997 97 7 20 1 1 1 1 1 4 2450450
| 1,997 1997 997 97 7 20 1 2 9 59 28 6 2450508
| 1,997 1997 997 97 7 20 1 3 9 60 1 7 2450509
- | 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
+ | 1,997 1997 997 97 7 20 4 12 53 364 30 3 2450813
| 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
| 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
| 2,000 2000 0 0 0 20 1 1 1 1 1 7 2451545
- | 2,000 2000 0 0 0 20 4 12 53 366 31 1 2451910
+ | 2,000 2000 0 0 0 20 4 12 54 366 31 1 2451910
| 2,001 2001 1 1 1 21 1 1 1 1 1 2 2451911
-(65 rows)
+ | 2,016 2016 16 16 6 21 4 12 53 366 31 7 2457754
+ | 2,017 2017 17 17 7 21 1 1 1 1 1 1 2457755
+(67 rows)
SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
FROM TIMESTAMP_TBL;
@@ -1263,7 +1293,9 @@ SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
| 05 05 17 32 01 63121
| 05 05 17 32 01 63121
| 05 05 17 32 01 63121
-(65 rows)
+ | 05 05 17 32 01 63121
+ | 05 05 17 32 01 63121
+(67 rows)
SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
FROM TIMESTAMP_TBL;
@@ -1334,7 +1366,9 @@ SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between qu
| HH:MI:SS is 05:32:01 "text between quote marks"
| HH:MI:SS is 05:32:01 "text between quote marks"
| HH:MI:SS is 05:32:01 "text between quote marks"
-(65 rows)
+ | HH:MI:SS is 05:32:01 "text between quote marks"
+ | HH:MI:SS is 05:32:01 "text between quote marks"
+(67 rows)
SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
FROM TIMESTAMP_TBL;
@@ -1405,7 +1439,9 @@ SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
| 17--text--32--text--01
| 17--text--32--text--01
| 17--text--32--text--01
-(65 rows)
+ | 17--text--32--text--01
+ | 17--text--32--text--01
+(67 rows)
SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth')
FROM TIMESTAMP_TBL;
@@ -1476,7 +1512,9 @@ SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth')
| 2000TH 2000th 2451545th
| 2000TH 2000th 2451910th
| 2001ST 2001st 2451911th
-(65 rows)
+ | 2016TH 2016th 2457754th
+ | 2017TH 2017th 2457755th
+(67 rows)
SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. HH:MI:SS p.m. HH:MI:SS pm')
FROM TIMESTAMP_TBL;
@@ -1547,7 +1585,9 @@ SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. H
| 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
| 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
| 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
-(65 rows)
+ | 2016 A.D. 2016 a.d. 2016 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
+ | 2017 A.D. 2017 a.d. 2017 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
+(67 rows)
SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
FROM TIMESTAMP_TBL;
@@ -1618,7 +1658,9 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
| 1999 999 99 9 52 363 6
| 2000 000 00 0 52 364 7
| 2001 001 01 1 01 001 1
-(65 rows)
+ | 2016 016 16 6 52 363 6
+ | 2016 016 16 6 52 364 7
+(67 rows)
SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
FROM TIMESTAMP_TBL;
@@ -1689,7 +1731,9 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
| 1999 999 99 9 52 363 6
| 2000 0 0 0 52 364 7
| 2001 1 1 1 1 1 1
-(65 rows)
+ | 2016 16 16 6 52 363 6
+ | 2016 16 16 6 52 364 7
+(67 rows)
SELECT '' AS to_char_12, to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 MS US')
FROM (VALUES
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index bb89910..a84ae90 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -197,6 +197,8 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1999');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2000');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 2000');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2001');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 2016');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2017');
-- Currently unsupported syntax and ranges
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097');
ERROR: time zone displacement out of range: "Feb 16 17:32:01 -0097"
@@ -307,7 +309,9 @@ SELECT '' AS "64", d1 FROM TIMESTAMPTZ_TBL;
| Sat Jan 01 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
-(66 rows)
+ | Sat Dec 31 17:32:01 2016 PST
+ | Sun Jan 01 17:32:01 2017 PST
+(68 rows)
-- Check behavior at the lower boundary of the timestamp range
SELECT '4714-11-24 00:00:00+00 BC'::timestamptz;
@@ -388,7 +392,9 @@ SELECT '' AS "48", d1 FROM TIMESTAMPTZ_TBL
| Sat Jan 01 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
-(50 rows)
+ | Sat Dec 31 17:32:01 2016 PST
+ | Sun Jan 01 17:32:01 2017 PST
+(52 rows)
SELECT '' AS "15", d1 FROM TIMESTAMPTZ_TBL
WHERE d1 < timestamp with time zone '1997-01-02';
@@ -487,7 +493,9 @@ SELECT '' AS "63", d1 FROM TIMESTAMPTZ_TBL
| Sat Jan 01 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
-(65 rows)
+ | Sat Dec 31 17:32:01 2016 PST
+ | Sun Jan 01 17:32:01 2017 PST
+(67 rows)
SELECT '' AS "16", d1 FROM TIMESTAMPTZ_TBL
WHERE d1 <= timestamp with time zone '1997-01-02';
@@ -566,7 +574,9 @@ SELECT '' AS "49", d1 FROM TIMESTAMPTZ_TBL
| Sat Jan 01 17:32:01 2000 PST
| Sun Dec 31 17:32:01 2000 PST
| Mon Jan 01 17:32:01 2001 PST
-(51 rows)
+ | Sat Dec 31 17:32:01 2016 PST
+ | Sun Jan 01 17:32:01 2017 PST
+(53 rows)
SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
@@ -628,7 +638,9 @@ SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
| @ 1094 days 17 hours 32 mins 1 sec
| @ 1459 days 17 hours 32 mins 1 sec
| @ 1460 days 17 hours 32 mins 1 sec
-(56 rows)
+ | @ 7303 days 17 hours 32 mins 1 sec
+ | @ 7304 days 17 hours 32 mins 1 sec
+(58 rows)
SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp with time zone '2004-02-29 15:44:17.71393' ) AS week_trunc;
date_trunc_week | week_trunc
@@ -716,7 +728,9 @@ SELECT '' AS "54", d1 - timestamp with time zone '1997-01-02' AS diff
| @ 1094 days 17 hours 32 mins 1 sec
| @ 1459 days 17 hours 32 mins 1 sec
| @ 1460 days 17 hours 32 mins 1 sec
-(56 rows)
+ | @ 7303 days 17 hours 32 mins 1 sec
+ | @ 7304 days 17 hours 32 mins 1 sec
+(58 rows)
-- DATE_PART (timestamptz_part)
SELECT d1 as timestamptz,
@@ -792,7 +806,9 @@ SELECT d1 as timestamptz,
Sat Jan 01 17:32:01 2000 PST | 2000 | 1 | 1 | 17 | 32 | 1
Sun Dec 31 17:32:01 2000 PST | 2000 | 12 | 31 | 17 | 32 | 1
Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 17 | 32 | 1
-(66 rows)
+ Sat Dec 31 17:32:01 2016 PST | 2016 | 12 | 31 | 17 | 32 | 1
+ Sun Jan 01 17:32:01 2017 PST | 2017 | 1 | 1 | 17 | 32 | 1
+(68 rows)
SELECT d1 as timestamptz,
date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
@@ -866,7 +882,9 @@ SELECT d1 as timestamptz,
Sat Jan 01 17:32:01 2000 PST | 1 | 1000 | 1000000
Sun Dec 31 17:32:01 2000 PST | 4 | 1000 | 1000000
Mon Jan 01 17:32:01 2001 PST | 1 | 1000 | 1000000
-(66 rows)
+ Sat Dec 31 17:32:01 2016 PST | 4 | 1000 | 1000000
+ Sun Jan 01 17:32:01 2017 PST | 1 | 1000 | 1000000
+(68 rows)
SELECT d1 as timestamptz,
date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
@@ -941,7 +959,9 @@ SELECT d1 as timestamptz,
Sat Jan 01 17:32:01 2000 PST | 1999 | 52 | 6 | 6 | 1
Sun Dec 31 17:32:01 2000 PST | 2000 | 52 | 7 | 0 | 366
Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 1 | 1
-(66 rows)
+ Sat Dec 31 17:32:01 2016 PST | 2016 | 52 | 6 | 6 | 366
+ Sun Jan 01 17:32:01 2017 PST | 2016 | 52 | 7 | 0 | 1
+(68 rows)
SELECT d1 as timestamptz,
date_part( 'decade', d1) AS decade,
@@ -1017,7 +1037,9 @@ SELECT d1 as timestamptz,
Sat Jan 01 17:32:01 2000 PST | 200 | 20 | 2 | 2451546
Sun Dec 31 17:32:01 2000 PST | 200 | 20 | 2 | 2451911
Mon Jan 01 17:32:01 2001 PST | 200 | 21 | 3 | 2451912
-(66 rows)
+ Sat Dec 31 17:32:01 2016 PST | 201 | 21 | 3 | 2457755
+ Sun Jan 01 17:32:01 2017 PST | 201 | 21 | 3 | 2457756
+(68 rows)
SELECT d1 as timestamptz,
date_part( 'timezone', d1) AS timezone,
@@ -1092,7 +1114,9 @@ SELECT d1 as timestamptz,
Sat Jan 01 17:32:01 2000 PST | -28800 | -8 | 0
Sun Dec 31 17:32:01 2000 PST | -28800 | -8 | 0
Mon Jan 01 17:32:01 2001 PST | -28800 | -8 | 0
-(66 rows)
+ Sat Dec 31 17:32:01 2016 PST | -28800 | -8 | 0
+ Sun Jan 01 17:32:01 2017 PST | -28800 | -8 | 0
+(68 rows)
-- TO_CHAR()
SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
@@ -1165,7 +1189,9 @@ SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM M
| SATURDAY Saturday saturday SAT Sat sat JANUARY January january I JAN Jan jan
| SUNDAY Sunday sunday SUN Sun sun DECEMBER December december XII DEC Dec dec
| MONDAY Monday monday MON Mon mon JANUARY January january I JAN Jan jan
-(66 rows)
+ | SATURDAY Saturday saturday SAT Sat sat DECEMBER December december XII DEC Dec dec
+ | SUNDAY Sunday sunday SUN Sun sun JANUARY January january I JAN Jan jan
+(68 rows)
SELECT '' AS to_char_2, to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM')
FROM TIMESTAMPTZ_TBL;
@@ -1237,7 +1263,9 @@ SELECT '' AS to_char_2, to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth F
| SATURDAY Saturday saturday JANUARY January january I
| SUNDAY Sunday sunday DECEMBER December december XII
| MONDAY Monday monday JANUARY January january I
-(66 rows)
+ | SATURDAY Saturday saturday DECEMBER December december XII
+ | SUNDAY Sunday sunday JANUARY January january I
+(68 rows)
SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
FROM TIMESTAMPTZ_TBL;
@@ -1246,54 +1274,54 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
|
|
| 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
| 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
| 1,997 1997 997 97 7 20 1 01 01 002 02 5 2450451
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 2 06 24 161 10 3 2450610
| 2,001 2001 001 01 1 21 3 09 38 265 22 7 2452175
- | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
- | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
- | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
- | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
- | 2,000 2000 000 00 0 20 1 03 11 075 15 4 2451619
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
+ | 2,000 2000 000 00 0 20 1 03 12 075 15 4 2451619
+ | 2,000 2000 000 00 0 20 1 03 12 075 15 4 2451619
+ | 2,000 2000 000 00 0 20 1 03 12 075 15 4 2451619
+ | 2,000 2000 000 00 0 20 1 03 12 075 15 4 2451619
+ | 2,000 2000 000 00 0 20 1 03 12 075 15 4 2451619
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
| 1,997 1997 997 97 7 20 3 07 28 191 10 5 2450640
- | 1,997 1997 997 97 7 20 2 06 23 161 10 3 2450610
- | 1,997 1997 997 97 7 20 1 02 06 041 10 2 2450490
- | 1,997 1997 997 97 7 20 1 02 06 042 11 3 2450491
+ | 1,997 1997 997 97 7 20 2 06 24 161 10 3 2450610
+ | 1,997 1997 997 97 7 20 1 02 07 041 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 02 07 042 11 3 2450491
| 1,997 1997 997 97 7 20 1 02 07 043 12 4 2450492
| 1,997 1997 997 97 7 20 1 02 07 044 13 5 2450493
| 1,997 1997 997 97 7 20 1 02 07 045 14 6 2450494
| 1,997 1997 997 97 7 20 1 02 07 046 15 7 2450495
- | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
- | 0,097 0097 097 97 7 -01 1 02 07 047 16 3 1686042
+ | 1,997 1997 997 97 7 20 1 02 08 047 16 1 2450496
+ | 0,097 0097 097 97 7 -01 1 02 08 047 16 3 1686042
| 0,097 0097 097 97 7 01 1 02 07 047 16 7 1756536
| 0,597 0597 597 97 7 06 1 02 07 047 16 5 1939157
- | 1,097 1097 097 97 7 11 1 02 07 047 16 3 2121778
+ | 1,097 1097 097 97 7 11 1 02 08 047 16 3 2121778
| 1,697 1697 697 97 7 17 1 02 07 047 16 7 2340924
| 1,797 1797 797 97 7 18 1 02 07 047 16 5 2377448
- | 1,897 1897 897 97 7 19 1 02 07 047 16 3 2413972
- | 1,997 1997 997 97 7 20 1 02 07 047 16 1 2450496
+ | 1,897 1897 897 97 7 19 1 02 08 047 16 3 2413972
+ | 1,997 1997 997 97 7 20 1 02 08 047 16 1 2450496
| 2,097 2097 097 97 7 21 1 02 07 047 16 7 2487021
| 1,996 1996 996 96 6 20 1 02 09 059 28 4 2450142
| 1,996 1996 996 96 6 20 1 02 09 060 29 5 2450143
@@ -1303,13 +1331,15 @@ SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J')
| 1,997 1997 997 97 7 20 1 01 01 001 01 4 2450450
| 1,997 1997 997 97 7 20 1 02 09 059 28 6 2450508
| 1,997 1997 997 97 7 20 1 03 09 060 01 7 2450509
- | 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
+ | 1,997 1997 997 97 7 20 4 12 53 364 30 3 2450813
| 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
| 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
| 2,000 2000 000 00 0 20 1 01 01 001 01 7 2451545
- | 2,000 2000 000 00 0 20 4 12 53 366 31 1 2451910
+ | 2,000 2000 000 00 0 20 4 12 54 366 31 1 2451910
| 2,001 2001 001 01 1 21 1 01 01 001 01 2 2451911
-(66 rows)
+ | 2,016 2016 016 16 6 21 4 12 53 366 31 7 2457754
+ | 2,017 2017 017 17 7 21 1 01 01 001 01 1 2457755
+(68 rows)
SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ')
FROM TIMESTAMPTZ_TBL;
@@ -1318,54 +1348,54 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
|
|
| 1,969 1969 969 69 9 20 4 12 53 365 31 4 2440587
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
| 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
| 1,997 1997 997 97 7 20 1 1 1 2 2 5 2450451
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 2 6 24 161 10 3 2450610
| 2,001 2001 1 1 1 21 3 9 38 265 22 7 2452175
- | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
- | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
- | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
- | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
- | 2,000 2000 0 0 0 20 1 3 11 75 15 4 2451619
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
+ | 2,000 2000 0 0 0 20 1 3 12 75 15 4 2451619
+ | 2,000 2000 0 0 0 20 1 3 12 75 15 4 2451619
+ | 2,000 2000 0 0 0 20 1 3 12 75 15 4 2451619
+ | 2,000 2000 0 0 0 20 1 3 12 75 15 4 2451619
+ | 2,000 2000 0 0 0 20 1 3 12 75 15 4 2451619
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
| 1,997 1997 997 97 7 20 3 7 28 191 10 5 2450640
- | 1,997 1997 997 97 7 20 2 6 23 161 10 3 2450610
- | 1,997 1997 997 97 7 20 1 2 6 41 10 2 2450490
- | 1,997 1997 997 97 7 20 1 2 6 42 11 3 2450491
+ | 1,997 1997 997 97 7 20 2 6 24 161 10 3 2450610
+ | 1,997 1997 997 97 7 20 1 2 7 41 10 2 2450490
+ | 1,997 1997 997 97 7 20 1 2 7 42 11 3 2450491
| 1,997 1997 997 97 7 20 1 2 7 43 12 4 2450492
| 1,997 1997 997 97 7 20 1 2 7 44 13 5 2450493
| 1,997 1997 997 97 7 20 1 2 7 45 14 6 2450494
| 1,997 1997 997 97 7 20 1 2 7 46 15 7 2450495
- | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
- | 0,097 97 97 97 7 -1 1 2 7 47 16 3 1686042
+ | 1,997 1997 997 97 7 20 1 2 8 47 16 1 2450496
+ | 0,097 97 97 97 7 -1 1 2 8 47 16 3 1686042
| 0,097 97 97 97 7 1 1 2 7 47 16 7 1756536
| 0,597 597 597 97 7 6 1 2 7 47 16 5 1939157
- | 1,097 1097 97 97 7 11 1 2 7 47 16 3 2121778
+ | 1,097 1097 97 97 7 11 1 2 8 47 16 3 2121778
| 1,697 1697 697 97 7 17 1 2 7 47 16 7 2340924
| 1,797 1797 797 97 7 18 1 2 7 47 16 5 2377448
- | 1,897 1897 897 97 7 19 1 2 7 47 16 3 2413972
- | 1,997 1997 997 97 7 20 1 2 7 47 16 1 2450496
+ | 1,897 1897 897 97 7 19 1 2 8 47 16 3 2413972
+ | 1,997 1997 997 97 7 20 1 2 8 47 16 1 2450496
| 2,097 2097 97 97 7 21 1 2 7 47 16 7 2487021
| 1,996 1996 996 96 6 20 1 2 9 59 28 4 2450142
| 1,996 1996 996 96 6 20 1 2 9 60 29 5 2450143
@@ -1375,13 +1405,15 @@ SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM
| 1,997 1997 997 97 7 20 1 1 1 1 1 4 2450450
| 1,997 1997 997 97 7 20 1 2 9 59 28 6 2450508
| 1,997 1997 997 97 7 20 1 3 9 60 1 7 2450509
- | 1,997 1997 997 97 7 20 4 12 52 364 30 3 2450813
+ | 1,997 1997 997 97 7 20 4 12 53 364 30 3 2450813
| 1,997 1997 997 97 7 20 4 12 53 365 31 4 2450814
| 1,999 1999 999 99 9 20 4 12 53 365 31 6 2451544
| 2,000 2000 0 0 0 20 1 1 1 1 1 7 2451545
- | 2,000 2000 0 0 0 20 4 12 53 366 31 1 2451910
+ | 2,000 2000 0 0 0 20 4 12 54 366 31 1 2451910
| 2,001 2001 1 1 1 21 1 1 1 1 1 2 2451911
-(66 rows)
+ | 2,016 2016 16 16 6 21 4 12 53 366 31 7 2457754
+ | 2,017 2017 17 17 7 21 1 1 1 1 1 1 2457755
+(68 rows)
SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
FROM TIMESTAMPTZ_TBL;
@@ -1453,7 +1485,9 @@ SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS')
| 05 05 17 32 01 63121
| 05 05 17 32 01 63121
| 05 05 17 32 01 63121
-(66 rows)
+ | 05 05 17 32 01 63121
+ | 05 05 17 32 01 63121
+(68 rows)
SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""')
FROM TIMESTAMPTZ_TBL;
@@ -1525,7 +1559,9 @@ SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between qu
| HH:MI:SS is 05:32:01 "text between quote marks"
| HH:MI:SS is 05:32:01 "text between quote marks"
| HH:MI:SS is 05:32:01 "text between quote marks"
-(66 rows)
+ | HH:MI:SS is 05:32:01 "text between quote marks"
+ | HH:MI:SS is 05:32:01 "text between quote marks"
+(68 rows)
SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
FROM TIMESTAMPTZ_TBL;
@@ -1597,7 +1633,9 @@ SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS')
| 17--text--32--text--01
| 17--text--32--text--01
| 17--text--32--text--01
-(66 rows)
+ | 17--text--32--text--01
+ | 17--text--32--text--01
+(68 rows)
SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth')
FROM TIMESTAMPTZ_TBL;
@@ -1669,7 +1707,9 @@ SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth')
| 2000TH 2000th 2451545th
| 2000TH 2000th 2451910th
| 2001ST 2001st 2451911th
-(66 rows)
+ | 2016TH 2016th 2457754th
+ | 2017TH 2017th 2457755th
+(68 rows)
SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. HH:MI:SS p.m. HH:MI:SS pm')
FROM TIMESTAMPTZ_TBL;
@@ -1741,7 +1781,9 @@ SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. H
| 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
| 2000 A.D. 2000 a.d. 2000 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
| 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
-(66 rows)
+ | 2016 A.D. 2016 a.d. 2016 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
+ | 2017 A.D. 2017 a.d. 2017 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
+(68 rows)
SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
FROM TIMESTAMPTZ_TBL;
@@ -1813,7 +1855,9 @@ SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
| 1999 999 99 9 52 363 6
| 2000 000 00 0 52 364 7
| 2001 001 01 1 01 001 1
-(66 rows)
+ | 2016 016 16 6 52 363 6
+ | 2016 016 16 6 52 364 7
+(68 rows)
SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
FROM TIMESTAMPTZ_TBL;
@@ -1885,7 +1929,9 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
| 1999 999 99 9 52 363 6
| 2000 0 0 0 52 364 7
| 2001 1 1 1 1 1 1
-(66 rows)
+ | 2016 16 16 6 52 363 6
+ | 2016 16 16 6 52 364 7
+(68 rows)
SELECT '' AS to_char_12, to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 MS US')
FROM (VALUES
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 0d4d465..2e39f96 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -132,6 +132,8 @@ INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1999');
INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2000');
INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 2000');
INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2001');
+INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 2016');
+INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2017');
-- Currently unsupported syntax and ranges
INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 -0097');
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 67b4a7d..81c9103 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -143,6 +143,8 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 1999');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2000');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 2000');
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2001');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('Dec 31 17:32:01 2016');
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('Jan 01 17:32:01 2017');
-- Currently unsupported syntax and ranges
INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 16 17:32:01 -0097');
--
2.20.1
