Add test results for "SET DateStyle" alternative datetime formats.

1 files changed, 515 insertions, 0 deletions

diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 6b482d0b6d..967a03cb1f 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -1041,3 +1041,518 @@ eight|european_sql
 QUERY: RESET DateStyle;
 QUERY: SHOW DateStyle;
 NOTICE:DateStyle is Postgres with US (NonEuropean) conventions
+QUERY: SET DateStyle TO 'US,Postgres';
+QUERY: SHOW DateStyle;
+NOTICE:DateStyle is Postgres with US (NonEuropean) conventions
+QUERY: SELECT '' AS sixty_two, d1 AS us_postgres FROM DATETIME_TBL;
+sixty_two|us_postgres                    
+---------+-------------------------------
+         |invalid                        
+         |-infinity                      
+         |infinity                       
+         |epoch                          
+         |Mon Feb 10 17:32:01 1997 PST   
+         |invalid                        
+         |invalid                        
+         |Mon Feb 10 17:32:01.00 1997 PST
+         |Mon Feb 10 17:32:02.00 1997 PST
+         |Mon Feb 10 17:32:01.40 1997 PST
+         |Mon Feb 10 17:32:01.50 1997 PST
+         |Mon Feb 10 17:32:01.60 1997 PST
+         |Thu Jan 02 00:00:00 1997 PST   
+         |Thu Jan 02 03:04:05 1997 PST   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Tue Jun 10 17:32:01 1997 PDT   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Mon Feb 10 17:32:00 1997 PST   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Mon Feb 10 09:32:01 1997 PST   
+         |Mon Feb 10 09:32:01 1997 PST   
+         |Mon Feb 10 09:32:01 1997 PST   
+         |Tue Jun 10 18:32:01 1997 PDT   
+         |Mon Feb 10 17:32:01 1997 PST   
+         |Tue Feb 11 17:32:01 1997 PST   
+         |Wed Feb 12 17:32:01 1997 PST   
+         |Thu Feb 13 17:32:01 1997 PST   
+         |Fri Feb 14 17:32:01 1997 PST   
+         |Sat Feb 15 17:32:01 1997 PST   
+         |Sun Feb 16 17:32:01 1997 PST   
+         |Tue Feb 16 17:32 0097 BC       
+         |Sat Feb 16 17:32:01 0097       
+         |Thu Feb 16 17:32:01 0597       
+         |Tue Feb 16 17:32:01 1097       
+         |Sat Feb 16 17:32:01 1697       
+         |Thu Feb 16 17:32:01 1797       
+         |Tue Feb 16 17:32:01 1897       
+         |Sun Feb 16 17:32:01 1997 PST   
+         |Sat Feb 16 17:32:01 2097       
+         |Wed Feb 28 17:32:01 1996 PST   
+         |Thu Feb 29 17:32:01 1996 PST   
+         |Fri Mar 01 17:32:01 1996 PST   
+         |Mon Dec 30 17:32:01 1996 PST   
+         |Tue Dec 31 17:32:01 1996 PST   
+         |Wed Jan 01 17:32:01 1997 PST   
+         |Fri Feb 28 17:32:01 1997 PST   
+         |Sat Mar 01 17:32:01 1997 PST   
+         |Sat Mar 01 17:32:01 1997 PST   
+         |Tue Dec 30 17:32:01 1997 PST   
+         |Wed Dec 31 17:32:01 1997 PST   
+         |Fri Dec 31 17:32:01 1999 PST   
+         |Sat Jan 01 17:32:01 2000 PST   
+         |Sun Dec 31 17:32:01 2000 PST   
+         |Mon Jan 01 17:32:01 2001 PST   
+         |Thu Jun 13 00:00:00 1957 PDT   
+(63 rows)
+
+QUERY: SELECT '' AS eight, f1 AS us_postgres FROM ABSTIME_TBL;
+eight|us_postgres                 
+-----+----------------------------
+     |Sun Jan 14 03:14:21 1973 PST
+     |Mon May 01 00:30:30 1995 PDT
+     |epoch                       
+     |current                     
+     |infinity                    
+     |-infinity                   
+     |Sat May 10 23:59:12 1947 PST
+     |invalid                     
+(8 rows)
+
+QUERY: SET DateStyle TO 'US,ISO';
+QUERY: SELECT '' AS sixty_two, d1 AS us_iso FROM DATETIME_TBL;
+sixty_two|us_iso                   
+---------+-------------------------
+         |invalid                  
+         |-infinity                
+         |infinity                 
+         |epoch                    
+         |1997-02-10 17:32:01-08   
+         |invalid                  
+         |invalid                  
+         |1997-02-10 17:32:01.00-08
+         |1997-02-10 17:32:02.00-08
+         |1997-02-10 17:32:01.40-08
+         |1997-02-10 17:32:01.50-08
+         |1997-02-10 17:32:01.60-08
+         |1997-01-02 00:00:00-08   
+         |1997-01-02 03:04:05-08   
+         |1997-02-10 17:32:01-08   
+         |1997-02-10 17:32:01-08   
+         |1997-02-10 17:32:01-08   
+         |1997-02-10 17:32:01-08   
+         |1997-06-10 17:32:01-07   
+         |1997-02-10 17:32:01-08   
+         |1997-02-10 17:32:01-08   
+         |1997-02-10 17:32:00-08   
+         |1997-02-10 17:32:01-08   
+         |1997-02-10 17:32:01-08   
+         |1997-02-10 17:32:01-08   
+         |1997-02-10 17:32:01-08   
+         |1997-02-10 17:32:01-08   
+         |1997-02-10 09:32:01-08   
+         |1997-02-10 09:32:01-08   
+         |1997-02-10 09:32:01-08   
+         |1997-06-10 18:32:01-07   
+         |1997-02-10 17:32:01-08   
+         |1997-02-11 17:32:01-08   
+         |1997-02-12 17:32:01-08   
+         |1997-02-13 17:32:01-08   
+         |1997-02-14 17:32:01-08   
+         |1997-02-15 17:32:01-08   
+         |1997-02-16 17:32:01-08   
+         |0097-02-16 17:32 BC      
+         |0097-02-16 17:32:01      
+         |0597-02-16 17:32:01      
+         |1097-02-16 17:32:01      
+         |1697-02-16 17:32:01      
+         |1797-02-16 17:32:01      
+         |1897-02-16 17:32:01      
+         |1997-02-16 17:32:01-08   
+         |2097-02-16 17:32:01      
+         |1996-02-28 17:32:01-08   
+         |1996-02-29 17:32:01-08   
+         |1996-03-01 17:32:01-08   
+         |1996-12-30 17:32:01-08   
+         |1996-12-31 17:32:01-08   
+         |1997-01-01 17:32:01-08   
+         |1997-02-28 17:32:01-08   
+         |1997-03-01 17:32:01-08   
+         |1997-03-01 17:32:01-08   
+         |1997-12-30 17:32:01-08   
+         |1997-12-31 17:32:01-08   
+         |1999-12-31 17:32:01-08   
+         |2000-01-01 17:32:01-08   
+         |2000-12-31 17:32:01-08   
+         |2001-01-01 17:32:01-08   
+         |1957-06-13 00:00:00-07   
+(63 rows)
+
+QUERY: SELECT '' AS eight, f1 AS us_iso FROM ABSTIME_TBL;
+eight|us_iso                
+-----+----------------------
+     |1973-01-14 03:14:21-08
+     |1995-05-01 00:30:30-07
+     |epoch                 
+     |current               
+     |infinity              
+     |-infinity             
+     |1947-05-10 23:59:12-08
+     |invalid               
+(8 rows)
+
+QUERY: SET DateStyle TO 'US,SQL';
+QUERY: SHOW DateStyle;
+NOTICE:DateStyle is SQL with US (NonEuropean) conventions
+QUERY: SELECT '' AS sixty_two, d1 AS us_sql FROM DATETIME_TBL;
+sixty_two|us_sql                    
+---------+--------------------------
+         |invalid                   
+         |-infinity                 
+         |infinity                  
+         |epoch                     
+         |02/10/1997 17:32:01.00 PST
+         |invalid                   
+         |invalid                   
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 17:32:02.00 PST
+         |02/10/1997 17:32:01.40 PST
+         |02/10/1997 17:32:01.50 PST
+         |02/10/1997 17:32:01.60 PST
+         |01/02/1997 00:00:00.00 PST
+         |01/02/1997 03:04:05.00 PST
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 17:32:01.00 PST
+         |06/10/1997 17:32:01.00 PDT
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 17:32:00.00 PST
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 17:32:01.00 PST
+         |02/10/1997 09:32:01.00 PST
+         |02/10/1997 09:32:01.00 PST
+         |02/10/1997 09:32:01.00 PST
+         |06/10/1997 18:32:01.00 PDT
+         |02/10/1997 17:32:01.00 PST
+         |02/11/1997 17:32:01.00 PST
+         |02/12/1997 17:32:01.00 PST
+         |02/13/1997 17:32:01.00 PST
+         |02/14/1997 17:32:01.00 PST
+         |02/15/1997 17:32:01.00 PST
+         |02/16/1997 17:32:01.00 PST
+         |02/16/0097 17:32 BC       
+         |02/16/0097 17:32:01.00    
+         |02/16/0597 17:32:01.00    
+         |02/16/1097 17:32:01.00    
+         |02/16/1697 17:32:01.00    
+         |02/16/1797 17:32:01.00    
+         |02/16/1897 17:32:01.00    
+         |02/16/1997 17:32:01.00 PST
+         |02/16/2097 17:32:01.00    
+         |02/28/1996 17:32:01.00 PST
+         |02/29/1996 17:32:01.00 PST
+         |03/01/1996 17:32:01.00 PST
+         |12/30/1996 17:32:01.00 PST
+         |12/31/1996 17:32:01.00 PST
+         |01/01/1997 17:32:01.00 PST
+         |02/28/1997 17:32:01.00 PST
+         |03/01/1997 17:32:01.00 PST
+         |03/01/1997 17:32:01.00 PST
+         |12/30/1997 17:32:01.00 PST
+         |12/31/1997 17:32:01.00 PST
+         |12/31/1999 17:32:01.00 PST
+         |01/01/2000 17:32:01.00 PST
+         |12/31/2000 17:32:01.00 PST
+         |01/01/2001 17:32:01.00 PST
+         |06/13/1957 00:00:00.00 PDT
+(63 rows)
+
+QUERY: SELECT '' AS eight, f1 AS us_sql FROM ABSTIME_TBL;
+eight|us_sql                    
+-----+--------------------------
+     |01/14/1973 03:14:21.00 PST
+     |05/01/1995 00:30:30.00 PDT
+     |epoch                     
+     |current                   
+     |infinity                  
+     |-infinity                 
+     |05/10/1947 23:59:12.00 PST
+     |invalid                   
+(8 rows)
+
+QUERY: SET DateStyle TO 'European,Postgres';
+QUERY: SHOW DateStyle;
+NOTICE:DateStyle is Postgres with European conventions
+QUERY: INSERT INTO DATETIME_TBL VALUES('13/06/1957');
+QUERY: SELECT count(*) as one FROM DATETIME_TBL WHERE d1 = 'Jun 13 1957';
+one
+---
+  2
+(1 row)
+
+QUERY: SELECT '' AS sixty_three, d1 AS european_postgres FROM DATETIME_TBL;
+sixty_three|european_postgres              
+-----------+-------------------------------
+           |invalid                        
+           |-infinity                      
+           |infinity                       
+           |epoch                          
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |invalid                        
+           |invalid                        
+           |Mon 10 Feb 17:32:01.00 1997 PST
+           |Mon 10 Feb 17:32:02.00 1997 PST
+           |Mon 10 Feb 17:32:01.40 1997 PST
+           |Mon 10 Feb 17:32:01.50 1997 PST
+           |Mon 10 Feb 17:32:01.60 1997 PST
+           |Thu 02 Jan 00:00:00 1997 PST   
+           |Thu 02 Jan 03:04:05 1997 PST   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Tue 10 Jun 17:32:01 1997 PDT   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Mon 10 Feb 17:32:00 1997 PST   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Mon 10 Feb 09:32:01 1997 PST   
+           |Mon 10 Feb 09:32:01 1997 PST   
+           |Mon 10 Feb 09:32:01 1997 PST   
+           |Tue 10 Jun 18:32:01 1997 PDT   
+           |Mon 10 Feb 17:32:01 1997 PST   
+           |Tue 11 Feb 17:32:01 1997 PST   
+           |Wed 12 Feb 17:32:01 1997 PST   
+           |Thu 13 Feb 17:32:01 1997 PST   
+           |Fri 14 Feb 17:32:01 1997 PST   
+           |Sat 15 Feb 17:32:01 1997 PST   
+           |Sun 16 Feb 17:32:01 1997 PST   
+           |Tue 16 Feb 17:32 0097 BC       
+           |Sat 16 Feb 17:32:01 0097       
+           |Thu 16 Feb 17:32:01 0597       
+           |Tue 16 Feb 17:32:01 1097       
+           |Sat 16 Feb 17:32:01 1697       
+           |Thu 16 Feb 17:32:01 1797       
+           |Tue 16 Feb 17:32:01 1897       
+           |Sun 16 Feb 17:32:01 1997 PST   
+           |Sat 16 Feb 17:32:01 2097       
+           |Wed 28 Feb 17:32:01 1996 PST   
+           |Thu 29 Feb 17:32:01 1996 PST   
+           |Fri 01 Mar 17:32:01 1996 PST   
+           |Mon 30 Dec 17:32:01 1996 PST   
+           |Tue 31 Dec 17:32:01 1996 PST   
+           |Wed 01 Jan 17:32:01 1997 PST   
+           |Fri 28 Feb 17:32:01 1997 PST   
+           |Sat 01 Mar 17:32:01 1997 PST   
+           |Sat 01 Mar 17:32:01 1997 PST   
+           |Tue 30 Dec 17:32:01 1997 PST   
+           |Wed 31 Dec 17:32:01 1997 PST   
+           |Fri 31 Dec 17:32:01 1999 PST   
+           |Sat 01 Jan 17:32:01 2000 PST   
+           |Sun 31 Dec 17:32:01 2000 PST   
+           |Mon 01 Jan 17:32:01 2001 PST   
+           |Thu 13 Jun 00:00:00 1957 PDT   
+           |Thu 13 Jun 00:00:00 1957 PDT   
+(64 rows)
+
+QUERY: SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL;
+eight|european_postgres           
+-----+----------------------------
+     |Sun 14 Jan 03:14:21 1973 PST
+     |Mon 01 May 00:30:30 1995 PDT
+     |epoch                       
+     |current                     
+     |infinity                    
+     |-infinity                   
+     |Sat 10 May 23:59:12 1947 PST
+     |invalid                     
+(8 rows)
+
+QUERY: SET DateStyle TO 'European,ISO';
+QUERY: SHOW DateStyle;
+NOTICE:DateStyle is ISO with European conventions
+QUERY: SELECT '' AS sixty_three, d1 AS european_iso FROM DATETIME_TBL;
+sixty_three|european_iso             
+-----------+-------------------------
+           |invalid                  
+           |-infinity                
+           |infinity                 
+           |epoch                    
+           |1997-02-10 17:32:01-08   
+           |invalid                  
+           |invalid                  
+           |1997-02-10 17:32:01.00-08
+           |1997-02-10 17:32:02.00-08
+           |1997-02-10 17:32:01.40-08
+           |1997-02-10 17:32:01.50-08
+           |1997-02-10 17:32:01.60-08
+           |1997-01-02 00:00:00-08   
+           |1997-01-02 03:04:05-08   
+           |1997-02-10 17:32:01-08   
+           |1997-02-10 17:32:01-08   
+           |1997-02-10 17:32:01-08   
+           |1997-02-10 17:32:01-08   
+           |1997-06-10 17:32:01-07   
+           |1997-02-10 17:32:01-08   
+           |1997-02-10 17:32:01-08   
+           |1997-02-10 17:32:00-08   
+           |1997-02-10 17:32:01-08   
+           |1997-02-10 17:32:01-08   
+           |1997-02-10 17:32:01-08   
+           |1997-02-10 17:32:01-08   
+           |1997-02-10 17:32:01-08   
+           |1997-02-10 09:32:01-08   
+           |1997-02-10 09:32:01-08   
+           |1997-02-10 09:32:01-08   
+           |1997-06-10 18:32:01-07   
+           |1997-02-10 17:32:01-08   
+           |1997-02-11 17:32:01-08   
+           |1997-02-12 17:32:01-08   
+           |1997-02-13 17:32:01-08   
+           |1997-02-14 17:32:01-08   
+           |1997-02-15 17:32:01-08   
+           |1997-02-16 17:32:01-08   
+           |0097-02-16 17:32 BC      
+           |0097-02-16 17:32:01      
+           |0597-02-16 17:32:01      
+           |1097-02-16 17:32:01      
+           |1697-02-16 17:32:01      
+           |1797-02-16 17:32:01      
+           |1897-02-16 17:32:01      
+           |1997-02-16 17:32:01-08   
+           |2097-02-16 17:32:01      
+           |1996-02-28 17:32:01-08   
+           |1996-02-29 17:32:01-08   
+           |1996-03-01 17:32:01-08   
+           |1996-12-30 17:32:01-08   
+           |1996-12-31 17:32:01-08   
+           |1997-01-01 17:32:01-08   
+           |1997-02-28 17:32:01-08   
+           |1997-03-01 17:32:01-08   
+           |1997-03-01 17:32:01-08   
+           |1997-12-30 17:32:01-08   
+           |1997-12-31 17:32:01-08   
+           |1999-12-31 17:32:01-08   
+           |2000-01-01 17:32:01-08   
+           |2000-12-31 17:32:01-08   
+           |2001-01-01 17:32:01-08   
+           |1957-06-13 00:00:00-07   
+           |1957-06-13 00:00:00-07   
+(64 rows)
+
+QUERY: SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL;
+eight|european_iso          
+-----+----------------------
+     |1973-01-14 03:14:21-08
+     |1995-05-01 00:30:30-07
+     |epoch                 
+     |current               
+     |infinity              
+     |-infinity             
+     |1947-05-10 23:59:12-08
+     |invalid               
+(8 rows)
+
+QUERY: SET DateStyle TO 'European,SQL';
+QUERY: SHOW DateStyle;
+NOTICE:DateStyle is SQL with European conventions
+QUERY: SELECT '' AS sixty_three, d1 AS european_sql FROM DATETIME_TBL;
+sixty_three|european_sql              
+-----------+--------------------------
+           |invalid                   
+           |-infinity                 
+           |infinity                  
+           |epoch                     
+           |10/02/1997 17:32:01.00 PST
+           |invalid                   
+           |invalid                   
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 17:32:02.00 PST
+           |10/02/1997 17:32:01.40 PST
+           |10/02/1997 17:32:01.50 PST
+           |10/02/1997 17:32:01.60 PST
+           |02/01/1997 00:00:00.00 PST
+           |02/01/1997 03:04:05.00 PST
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 17:32:01.00 PST
+           |10/06/1997 17:32:01.00 PDT
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 17:32:00.00 PST
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 17:32:01.00 PST
+           |10/02/1997 09:32:01.00 PST
+           |10/02/1997 09:32:01.00 PST
+           |10/02/1997 09:32:01.00 PST
+           |10/06/1997 18:32:01.00 PDT
+           |10/02/1997 17:32:01.00 PST
+           |11/02/1997 17:32:01.00 PST
+           |12/02/1997 17:32:01.00 PST
+           |13/02/1997 17:32:01.00 PST
+           |14/02/1997 17:32:01.00 PST
+           |15/02/1997 17:32:01.00 PST
+           |16/02/1997 17:32:01.00 PST
+           |16/02/0097 17:32 BC       
+           |16/02/0097 17:32:01.00    
+           |16/02/0597 17:32:01.00    
+           |16/02/1097 17:32:01.00    
+           |16/02/1697 17:32:01.00    
+           |16/02/1797 17:32:01.00    
+           |16/02/1897 17:32:01.00    
+           |16/02/1997 17:32:01.00 PST
+           |16/02/2097 17:32:01.00    
+           |28/02/1996 17:32:01.00 PST
+           |29/02/1996 17:32:01.00 PST
+           |01/03/1996 17:32:01.00 PST
+           |30/12/1996 17:32:01.00 PST
+           |31/12/1996 17:32:01.00 PST
+           |01/01/1997 17:32:01.00 PST
+           |28/02/1997 17:32:01.00 PST
+           |01/03/1997 17:32:01.00 PST
+           |01/03/1997 17:32:01.00 PST
+           |30/12/1997 17:32:01.00 PST
+           |31/12/1997 17:32:01.00 PST
+           |31/12/1999 17:32:01.00 PST
+           |01/01/2000 17:32:01.00 PST
+           |31/12/2000 17:32:01.00 PST
+           |01/01/2001 17:32:01.00 PST
+           |13/06/1957 00:00:00.00 PDT
+           |13/06/1957 00:00:00.00 PDT
+(64 rows)
+
+QUERY: SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL;
+eight|european_sql              
+-----+--------------------------
+     |14/01/1973 03:14:21.00 PST
+     |01/05/1995 00:30:30.00 PDT
+     |epoch                     
+     |current                   
+     |infinity                  
+     |-infinity                 
+     |10/05/1947 23:59:12.00 PST
+     |invalid                   
+(8 rows)
+
+QUERY: RESET DateStyle;
+QUERY: SHOW DateStyle;
+NOTICE:DateStyle is Postgres with US (NonEuropean) conventions