summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
authorBruce Momjian2007-02-16 03:39:46 +0000
committerBruce Momjian2007-02-16 03:39:46 +0000
commit4ebb0cf9c30c1e477d5e2dfcc1f2c016c3f8bbcf (patch)
treefd6ce21ac074478e57b9f55ff378a4ae1a04d14f /src/test
parentc7b08050d9a2b68b27045b36ff4c9a3db85a55e4 (diff)
Add two new format fields for use with to_char(), to_date() and
to_timestamp(): - ID for day-of-week - IDDD for day-of-year This makes it possible to convert ISO week dates to and from text fully represented in either week ('IYYY-IW-ID') or day-of-year ('IYYY-IDDD') format. I have also added an 'isoyear' field for use with extract / date_part. Brendan Jurd
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/timestamp.out247
-rw-r--r--src/test/regress/expected/timestamptz.out309
-rw-r--r--src/test/regress/sql/timestamp.sql25
-rw-r--r--src/test/regress/sql/timestamptz.sql25
4 files changed, 529 insertions, 77 deletions
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index c26cb9ec99..9f585b68f6 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -703,6 +703,69 @@ SELECT '' AS "54", d1 as "timestamp",
| Mon Jan 01 17:32:01 2001 | 1 | 1000 | 1000000
(55 rows)
+SELECT '' AS "54", d1 as "timestamp",
+ date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
+ date_part( 'dow', d1) AS dow
+ FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+ 54 | timestamp | isoyear | week | dow
+----+-----------------------------+---------+------+-----
+ | Thu Jan 01 00:00:00 1970 | 1970 | 1 | 4
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:02 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01.40 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01.50 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01.60 1997 | 1997 | 7 | 1
+ | Thu Jan 02 00:00:00 1997 | 1997 | 1 | 4
+ | Thu Jan 02 03:04:05 1997 | 1997 | 1 | 4
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Tue Jun 10 17:32:01 1997 | 1997 | 24 | 2
+ | Sat Sep 22 18:19:20 2001 | 2001 | 38 | 6
+ | Wed Mar 15 08:14:01 2000 | 2000 | 11 | 3
+ | Wed Mar 15 13:14:02 2000 | 2000 | 11 | 3
+ | Wed Mar 15 12:14:03 2000 | 2000 | 11 | 3
+ | Wed Mar 15 03:14:04 2000 | 2000 | 11 | 3
+ | Wed Mar 15 02:14:05 2000 | 2000 | 11 | 3
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:00 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Tue Jun 10 18:32:01 1997 | 1997 | 24 | 2
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Tue Feb 11 17:32:01 1997 | 1997 | 7 | 2
+ | Wed Feb 12 17:32:01 1997 | 1997 | 7 | 3
+ | Thu Feb 13 17:32:01 1997 | 1997 | 7 | 4
+ | Fri Feb 14 17:32:01 1997 | 1997 | 7 | 5
+ | Sat Feb 15 17:32:01 1997 | 1997 | 7 | 6
+ | Sun Feb 16 17:32:01 1997 | 1997 | 7 | 0
+ | Sun Feb 16 17:32:01 1997 | 1997 | 7 | 0
+ | Wed Feb 28 17:32:01 1996 | 1996 | 9 | 3
+ | Thu Feb 29 17:32:01 1996 | 1996 | 9 | 4
+ | Fri Mar 01 17:32:01 1996 | 1996 | 9 | 5
+ | Mon Dec 30 17:32:01 1996 | 1997 | 1 | 1
+ | Tue Dec 31 17:32:01 1996 | 1997 | 1 | 2
+ | Wed Jan 01 17:32:01 1997 | 1997 | 1 | 3
+ | Fri Feb 28 17:32:01 1997 | 1997 | 9 | 5
+ | Sat Mar 01 17:32:01 1997 | 1997 | 9 | 6
+ | Tue Dec 30 17:32:01 1997 | 1998 | 1 | 2
+ | Wed Dec 31 17:32:01 1997 | 1998 | 1 | 3
+ | Fri Dec 31 17:32:01 1999 | 1999 | 52 | 5
+ | Sat Jan 01 17:32:01 2000 | 1999 | 52 | 6
+ | Sun Dec 31 17:32:01 2000 | 2000 | 52 | 0
+ | Mon Jan 01 17:32:01 2001 | 2001 | 1 | 1
+(55 rows)
+
-- TO_CHAR()
SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
FROM TIMESTAMP_TBL;
@@ -1344,6 +1407,148 @@ SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. H
| 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
(65 rows)
+SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
+ FROM TIMESTAMP_TBL;
+ to_char_10 | to_char
+------------+------------------------
+ |
+ |
+ | 1970 970 70 0 01 004 4
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 01 004 4
+ | 1997 997 97 7 01 004 4
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 24 163 2
+ | 2001 001 01 1 38 265 6
+ | 2000 000 00 0 11 073 3
+ | 2000 000 00 0 11 073 3
+ | 2000 000 00 0 11 073 3
+ | 2000 000 00 0 11 073 3
+ | 2000 000 00 0 11 073 3
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 24 163 2
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 044 2
+ | 1997 997 97 7 07 045 3
+ | 1997 997 97 7 07 046 4
+ | 1997 997 97 7 07 047 5
+ | 1997 997 97 7 07 048 6
+ | 1997 997 97 7 07 049 7
+ | 0097 097 97 7 07 044 2
+ | 0097 097 97 7 07 048 6
+ | 0597 597 97 7 07 046 4
+ | 1097 097 97 7 07 044 2
+ | 1697 697 97 7 07 048 6
+ | 1797 797 97 7 07 046 4
+ | 1897 897 97 7 07 044 2
+ | 1997 997 97 7 07 049 7
+ | 2097 097 97 7 07 048 6
+ | 1996 996 96 6 09 059 3
+ | 1996 996 96 6 09 060 4
+ | 1996 996 96 6 09 061 5
+ | 1997 997 97 7 01 001 1
+ | 1997 997 97 7 01 002 2
+ | 1997 997 97 7 01 003 3
+ | 1997 997 97 7 09 061 5
+ | 1997 997 97 7 09 062 6
+ | 1998 998 98 8 01 002 2
+ | 1998 998 98 8 01 003 3
+ | 1999 999 99 9 52 362 5
+ | 1999 999 99 9 52 363 6
+ | 2000 000 00 0 52 364 7
+ | 2001 001 01 1 01 001 1
+(65 rows)
+
+SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
+ FROM TIMESTAMP_TBL;
+ to_char_11 | to_char
+------------+------------------------
+ |
+ |
+ | 1970 970 70 0 1 4 4
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 1 4 4
+ | 1997 997 97 7 1 4 4
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 24 163 2
+ | 2001 001 01 1 38 265 6
+ | 2000 000 00 0 11 73 3
+ | 2000 000 00 0 11 73 3
+ | 2000 000 00 0 11 73 3
+ | 2000 000 00 0 11 73 3
+ | 2000 000 00 0 11 73 3
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 24 163 2
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 44 2
+ | 1997 997 97 7 7 45 3
+ | 1997 997 97 7 7 46 4
+ | 1997 997 97 7 7 47 5
+ | 1997 997 97 7 7 48 6
+ | 1997 997 97 7 7 49 7
+ | 97 097 97 7 7 44 2
+ | 97 097 97 7 7 48 6
+ | 597 597 97 7 7 46 4
+ | 1097 097 97 7 7 44 2
+ | 1697 697 97 7 7 48 6
+ | 1797 797 97 7 7 46 4
+ | 1897 897 97 7 7 44 2
+ | 1997 997 97 7 7 49 7
+ | 2097 097 97 7 7 48 6
+ | 1996 996 96 6 9 59 3
+ | 1996 996 96 6 9 60 4
+ | 1996 996 96 6 9 61 5
+ | 1997 997 97 7 1 1 1
+ | 1997 997 97 7 1 2 2
+ | 1997 997 97 7 1 3 3
+ | 1997 997 97 7 9 61 5
+ | 1997 997 97 7 9 62 6
+ | 1998 998 98 8 1 2 2
+ | 1998 998 98 8 1 3 3
+ | 1999 999 99 9 52 362 5
+ | 1999 999 99 9 52 363 6
+ | 2000 000 00 0 52 364 7
+ | 2001 001 01 1 1 1 1
+(65 rows)
+
-- TO_TIMESTAMP()
SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
to_timestamp_1 | to_timestamp
@@ -1428,4 +1633,46 @@ SELECT '' AS to_timestamp_14, to_timestamp('995-1116', 'YYY-MMDD');
| Thu Nov 16 00:00:00 1995 PST
(1 row)
+SELECT '' AS to_timestamp_15, to_timestamp('2005426', 'YYYYWWD');
+ to_timestamp_15 | to_timestamp
+-----------------+------------------------------
+ | Sat Oct 15 00:00:00 2005 PDT
+(1 row)
+
+SELECT '' AS to_timestamp_16, to_timestamp('2005300', 'YYYYDDD');
+ to_timestamp_16 | to_timestamp
+-----------------+------------------------------
+ | Thu Oct 27 00:00:00 2005 PDT
+(1 row)
+
+SELECT '' AS to_timestamp_17, to_timestamp('2005527', 'IYYYIWID');
+ to_timestamp_17 | to_timestamp
+-----------------+------------------------------
+ | Sun Jan 01 00:00:00 2006 PST
+(1 row)
+
+SELECT '' AS to_timestamp_18, to_timestamp('005527', 'IYYIWID');
+ to_timestamp_18 | to_timestamp
+-----------------+------------------------------
+ | Sun Jan 01 00:00:00 2006 PST
+(1 row)
+
+SELECT '' AS to_timestamp_19, to_timestamp('05527', 'IYIWID');
+ to_timestamp_19 | to_timestamp
+-----------------+------------------------------
+ | Sun Jan 01 00:00:00 2006 PST
+(1 row)
+
+SELECT '' AS to_timestamp_20, to_timestamp('5527', 'IIWID');
+ to_timestamp_20 | to_timestamp
+-----------------+------------------------------
+ | Sun Jan 01 00:00:00 2006 PST
+(1 row)
+
+SELECT '' AS to_timestamp_21, to_timestamp('2005364', 'IYYYIDDD');
+ to_timestamp_21 | to_timestamp
+-----------------+------------------------------
+ | Sun Jan 01 00:00:00 2006 PST
+(1 row)
+
SET DateStyle TO DEFAULT;
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index be3d7bed5d..e875c90d5d 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -720,6 +720,69 @@ SELECT '' AS "54", d1 as timestamptz,
| Mon Jan 01 17:32:01 2001 PST | 1 | 1000 | 1000000
(56 rows)
+SELECT '' AS "54", d1 as "timestamp",
+ date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
+ date_part( 'dow', d1) AS dow
+ FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+ 54 | timestamp | isoyear | week | dow
+----+-----------------------------+---------+------+-----
+ | Thu Jan 01 00:00:00 1970 | 1970 | 1 | 4
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:02 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01.40 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01.50 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01.60 1997 | 1997 | 7 | 1
+ | Thu Jan 02 00:00:00 1997 | 1997 | 1 | 4
+ | Thu Jan 02 03:04:05 1997 | 1997 | 1 | 4
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Tue Jun 10 17:32:01 1997 | 1997 | 24 | 2
+ | Sat Sep 22 18:19:20 2001 | 2001 | 38 | 6
+ | Wed Mar 15 08:14:01 2000 | 2000 | 11 | 3
+ | Wed Mar 15 13:14:02 2000 | 2000 | 11 | 3
+ | Wed Mar 15 12:14:03 2000 | 2000 | 11 | 3
+ | Wed Mar 15 03:14:04 2000 | 2000 | 11 | 3
+ | Wed Mar 15 02:14:05 2000 | 2000 | 11 | 3
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:00 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Tue Jun 10 18:32:01 1997 | 1997 | 24 | 2
+ | Mon Feb 10 17:32:01 1997 | 1997 | 7 | 1
+ | Tue Feb 11 17:32:01 1997 | 1997 | 7 | 2
+ | Wed Feb 12 17:32:01 1997 | 1997 | 7 | 3
+ | Thu Feb 13 17:32:01 1997 | 1997 | 7 | 4
+ | Fri Feb 14 17:32:01 1997 | 1997 | 7 | 5
+ | Sat Feb 15 17:32:01 1997 | 1997 | 7 | 6
+ | Sun Feb 16 17:32:01 1997 | 1997 | 7 | 0
+ | Sun Feb 16 17:32:01 1997 | 1997 | 7 | 0
+ | Wed Feb 28 17:32:01 1996 | 1996 | 9 | 3
+ | Thu Feb 29 17:32:01 1996 | 1996 | 9 | 4
+ | Fri Mar 01 17:32:01 1996 | 1996 | 9 | 5
+ | Mon Dec 30 17:32:01 1996 | 1997 | 1 | 1
+ | Tue Dec 31 17:32:01 1996 | 1997 | 1 | 2
+ | Wed Jan 01 17:32:01 1997 | 1997 | 1 | 3
+ | Fri Feb 28 17:32:01 1997 | 1997 | 9 | 5
+ | Sat Mar 01 17:32:01 1997 | 1997 | 9 | 6
+ | Tue Dec 30 17:32:01 1997 | 1998 | 1 | 2
+ | Wed Dec 31 17:32:01 1997 | 1998 | 1 | 3
+ | Fri Dec 31 17:32:01 1999 | 1999 | 52 | 5
+ | Sat Jan 01 17:32:01 2000 | 1999 | 52 | 6
+ | Sun Dec 31 17:32:01 2000 | 2000 | 52 | 0
+ | Mon Jan 01 17:32:01 2001 | 2001 | 1 | 1
+(55 rows)
+
-- TO_CHAR()
SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
FROM TIMESTAMPTZ_TBL;
@@ -1374,77 +1437,147 @@ SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. H
| 2001 A.D. 2001 a.d. 2001 ad 05:32:01 P.M. 05:32:01 p.m. 05:32:01 pm
(66 rows)
-SELECT '' AS to_char_10, to_char(d1, 'YYYY WW IYYY IYY IY I IW')
- FROM TIMESTAMPTZ_TBL;
- to_char_10 | to_char
-------------+--------------------------
+SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
+ FROM TIMESTAMP_TBL;
+ to_char_10 | to_char
+------------+------------------------
|
|
- | 1969 53 1970 970 70 0 01
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 01 1997 997 97 7 01
- | 1997 01 1997 997 97 7 01
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 23 1997 997 97 7 24
- | 2001 38 2001 001 01 1 38
- | 2000 11 2000 000 00 0 11
- | 2000 11 2000 000 00 0 11
- | 2000 11 2000 000 00 0 11
- | 2000 11 2000 000 00 0 11
- | 2000 11 2000 000 00 0 11
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 28 1997 997 97 7 28
- | 1997 23 1997 997 97 7 24
- | 1997 06 1997 997 97 7 07
- | 1997 06 1997 997 97 7 07
- | 1997 07 1997 997 97 7 07
- | 1997 07 1997 997 97 7 07
- | 1997 07 1997 997 97 7 07
- | 1997 07 1997 997 97 7 07
- | 1997 07 1997 997 97 7 07
- | 0097 07 0097 097 97 7 07
- | 0097 07 0097 097 97 7 07
- | 0597 07 0597 597 97 7 07
- | 1097 07 1097 097 97 7 07
- | 1697 07 1697 697 97 7 07
- | 1797 07 1797 797 97 7 07
- | 1897 07 1897 897 97 7 07
- | 1997 07 1997 997 97 7 07
- | 2097 07 2097 097 97 7 07
- | 1996 09 1996 996 96 6 09
- | 1996 09 1996 996 96 6 09
- | 1996 09 1996 996 96 6 09
- | 1996 53 1997 997 97 7 01
- | 1996 53 1997 997 97 7 01
- | 1997 01 1997 997 97 7 01
- | 1997 09 1997 997 97 7 09
- | 1997 09 1997 997 97 7 09
- | 1997 52 1998 998 98 8 01
- | 1997 53 1998 998 98 8 01
- | 1999 53 1999 999 99 9 52
- | 2000 01 1999 999 99 9 52
- | 2000 53 2000 000 00 0 52
- | 2001 01 2001 001 01 1 01
-(66 rows)
+ | 1970 970 70 0 01 004 4
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 01 004 4
+ | 1997 997 97 7 01 004 4
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 24 163 2
+ | 2001 001 01 1 38 265 6
+ | 2000 000 00 0 11 073 3
+ | 2000 000 00 0 11 073 3
+ | 2000 000 00 0 11 073 3
+ | 2000 000 00 0 11 073 3
+ | 2000 000 00 0 11 073 3
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 24 163 2
+ | 1997 997 97 7 07 043 1
+ | 1997 997 97 7 07 044 2
+ | 1997 997 97 7 07 045 3
+ | 1997 997 97 7 07 046 4
+ | 1997 997 97 7 07 047 5
+ | 1997 997 97 7 07 048 6
+ | 1997 997 97 7 07 049 7
+ | 0097 097 97 7 07 044 2
+ | 0097 097 97 7 07 048 6
+ | 0597 597 97 7 07 046 4
+ | 1097 097 97 7 07 044 2
+ | 1697 697 97 7 07 048 6
+ | 1797 797 97 7 07 046 4
+ | 1897 897 97 7 07 044 2
+ | 1997 997 97 7 07 049 7
+ | 2097 097 97 7 07 048 6
+ | 1996 996 96 6 09 059 3
+ | 1996 996 96 6 09 060 4
+ | 1996 996 96 6 09 061 5
+ | 1997 997 97 7 01 001 1
+ | 1997 997 97 7 01 002 2
+ | 1997 997 97 7 01 003 3
+ | 1997 997 97 7 09 061 5
+ | 1997 997 97 7 09 062 6
+ | 1998 998 98 8 01 002 2
+ | 1998 998 98 8 01 003 3
+ | 1999 999 99 9 52 362 5
+ | 1999 999 99 9 52 363 6
+ | 2000 000 00 0 52 364 7
+ | 2001 001 01 1 01 001 1
+(65 rows)
+
+SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
+ FROM TIMESTAMP_TBL;
+ to_char_11 | to_char
+------------+------------------------
+ |
+ |
+ | 1970 970 70 0 1 4 4
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 1 4 4
+ | 1997 997 97 7 1 4 4
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 24 163 2
+ | 2001 001 01 1 38 265 6
+ | 2000 000 00 0 11 73 3
+ | 2000 000 00 0 11 73 3
+ | 2000 000 00 0 11 73 3
+ | 2000 000 00 0 11 73 3
+ | 2000 000 00 0 11 73 3
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 24 163 2
+ | 1997 997 97 7 7 43 1
+ | 1997 997 97 7 7 44 2
+ | 1997 997 97 7 7 45 3
+ | 1997 997 97 7 7 46 4
+ | 1997 997 97 7 7 47 5
+ | 1997 997 97 7 7 48 6
+ | 1997 997 97 7 7 49 7
+ | 97 097 97 7 7 44 2
+ | 97 097 97 7 7 48 6
+ | 597 597 97 7 7 46 4
+ | 1097 097 97 7 7 44 2
+ | 1697 697 97 7 7 48 6
+ | 1797 797 97 7 7 46 4
+ | 1897 897 97 7 7 44 2
+ | 1997 997 97 7 7 49 7
+ | 2097 097 97 7 7 48 6
+ | 1996 996 96 6 9 59 3
+ | 1996 996 96 6 9 60 4
+ | 1996 996 96 6 9 61 5
+ | 1997 997 97 7 1 1 1
+ | 1997 997 97 7 1 2 2
+ | 1997 997 97 7 1 3 3
+ | 1997 997 97 7 9 61 5
+ | 1997 997 97 7 9 62 6
+ | 1998 998 98 8 1 2 2
+ | 1998 998 98 8 1 3 3
+ | 1999 999 99 9 52 362 5
+ | 1999 999 99 9 52 363 6
+ | 2000 000 00 0 52 364 7
+ | 2001 001 01 1 1 1 1
+(65 rows)
-- TO_TIMESTAMP()
SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
@@ -1531,16 +1664,46 @@ SELECT '' AS to_timestamp_14, to_timestamp('995-1116', 'YYY-MMDD');
| Thu Nov 16 00:00:00 1995 PST
(1 row)
-SELECT '' AS to_timestamp_15, to_timestamp('200401', 'IYYYIW');
+SELECT '' AS to_timestamp_15, to_timestamp('2005426', 'YYYYWWD');
to_timestamp_15 | to_timestamp
-----------------+------------------------------
- | Mon Dec 29 00:00:00 2003 PST
+ | Sat Oct 15 00:00:00 2005 PDT
(1 row)
-SELECT '' AS to_timestamp_16, to_timestamp('200401', 'YYYYWW');
+SELECT '' AS to_timestamp_16, to_timestamp('2005300', 'YYYYDDD');
to_timestamp_16 | to_timestamp
-----------------+------------------------------
- | Thu Jan 01 00:00:00 2004 PST
+ | Thu Oct 27 00:00:00 2005 PDT
+(1 row)
+
+SELECT '' AS to_timestamp_17, to_timestamp('2005527', 'IYYYIWID');
+ to_timestamp_17 | to_timestamp
+-----------------+------------------------------
+ | Sun Jan 01 00:00:00 2006 PST
+(1 row)
+
+SELECT '' AS to_timestamp_18, to_timestamp('005527', 'IYYIWID');
+ to_timestamp_18 | to_timestamp
+-----------------+------------------------------
+ | Sun Jan 01 00:00:00 2006 PST
+(1 row)
+
+SELECT '' AS to_timestamp_19, to_timestamp('05527', 'IYIWID');
+ to_timestamp_19 | to_timestamp
+-----------------+------------------------------
+ | Sun Jan 01 00:00:00 2006 PST
+(1 row)
+
+SELECT '' AS to_timestamp_20, to_timestamp('5527', 'IIWID');
+ to_timestamp_20 | to_timestamp
+-----------------+------------------------------
+ | Sun Jan 01 00:00:00 2006 PST
+(1 row)
+
+SELECT '' AS to_timestamp_21, to_timestamp('2005364', 'IYYYIDDD');
+ to_timestamp_21 | to_timestamp
+-----------------+------------------------------
+ | Sun Jan 01 00:00:00 2006 PST
(1 row)
SET DateStyle TO DEFAULT;
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index 34689564da..81be5b8f33 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -171,6 +171,11 @@ SELECT '' AS "54", d1 as "timestamp",
date_part( 'usec', d1) AS usec
FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+SELECT '' AS "54", d1 as "timestamp",
+ date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
+ date_part( 'dow', d1) AS dow
+ FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+
-- TO_CHAR()
SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
FROM TIMESTAMP_TBL;
@@ -199,6 +204,12 @@ SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth')
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;
+SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
+ FROM TIMESTAMP_TBL;
+
+SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
+ FROM TIMESTAMP_TBL;
+
-- TO_TIMESTAMP()
SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
@@ -230,4 +241,18 @@ SELECT '' AS to_timestamp_13, to_timestamp('95-1116', 'YY-MMDD');
SELECT '' AS to_timestamp_14, to_timestamp('995-1116', 'YYY-MMDD');
+SELECT '' AS to_timestamp_15, to_timestamp('2005426', 'YYYYWWD');
+
+SELECT '' AS to_timestamp_16, to_timestamp('2005300', 'YYYYDDD');
+
+SELECT '' AS to_timestamp_17, to_timestamp('2005527', 'IYYYIWID');
+
+SELECT '' AS to_timestamp_18, to_timestamp('005527', 'IYYIWID');
+
+SELECT '' AS to_timestamp_19, to_timestamp('05527', 'IYIWID');
+
+SELECT '' AS to_timestamp_20, to_timestamp('5527', 'IIWID');
+
+SELECT '' AS to_timestamp_21, to_timestamp('2005364', 'IYYYIDDD');
+
SET DateStyle TO DEFAULT;
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index fc597a6b2c..6ef216a53a 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -169,6 +169,11 @@ SELECT '' AS "54", d1 as timestamptz,
date_part( 'usec', d1) AS usec
FROM TIMESTAMPTZ_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+SELECT '' AS "54", d1 as "timestamp",
+ date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week,
+ date_part( 'dow', d1) AS dow
+ FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
+
-- TO_CHAR()
SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon')
FROM TIMESTAMPTZ_TBL;
@@ -197,8 +202,11 @@ SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth')
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;
-SELECT '' AS to_char_10, to_char(d1, 'YYYY WW IYYY IYY IY I IW')
- FROM TIMESTAMPTZ_TBL;
+SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID')
+ FROM TIMESTAMP_TBL;
+
+SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID')
+ FROM TIMESTAMP_TBL;
-- TO_TIMESTAMP()
SELECT '' AS to_timestamp_1, to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
@@ -231,9 +239,18 @@ SELECT '' AS to_timestamp_13, to_timestamp('95-1116', 'YY-MMDD');
SELECT '' AS to_timestamp_14, to_timestamp('995-1116', 'YYY-MMDD');
-SELECT '' AS to_timestamp_15, to_timestamp('200401', 'IYYYIW');
+SELECT '' AS to_timestamp_15, to_timestamp('2005426', 'YYYYWWD');
+
+SELECT '' AS to_timestamp_16, to_timestamp('2005300', 'YYYYDDD');
+
+SELECT '' AS to_timestamp_17, to_timestamp('2005527', 'IYYYIWID');
+
+SELECT '' AS to_timestamp_18, to_timestamp('005527', 'IYYIWID');
+
+SELECT '' AS to_timestamp_19, to_timestamp('05527', 'IYIWID');
-SELECT '' AS to_timestamp_16, to_timestamp('200401', 'YYYYWW');
+SELECT '' AS to_timestamp_20, to_timestamp('5527', 'IIWID');
+SELECT '' AS to_timestamp_21, to_timestamp('2005364', 'IYYYIDDD');
SET DateStyle TO DEFAULT;
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-12 23:07:13 +0000