horology.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

--
-- HOROLOGY
--
-- needed so tests pass even in Australia
SET australian_timezones = 'off';
SET DateStyle = 'Postgres, MDY';
--
-- Test various input formats
--
SELECT timestamp with time zone '20011227 040506+08';
         timestamptz          
------------------------------
 Wed Dec 26 12:05:06 2001 PST
(1 row)

SELECT timestamp with time zone '20011227 040506-08';
         timestamptz          
------------------------------
 Thu Dec 27 04:05:06 2001 PST
(1 row)

SELECT timestamp with time zone '20011227 040506.789+08';
           timestamptz            
----------------------------------
 Wed Dec 26 12:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone '20011227 040506.789-08';
           timestamptz            
----------------------------------
 Thu Dec 27 04:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone '20011227T040506+08';
         timestamptz          
------------------------------
 Wed Dec 26 12:05:06 2001 PST
(1 row)

SELECT timestamp with time zone '20011227T040506-08';
         timestamptz          
------------------------------
 Thu Dec 27 04:05:06 2001 PST
(1 row)

SELECT timestamp with time zone '20011227T040506.789+08';
           timestamptz            
----------------------------------
 Wed Dec 26 12:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone '20011227T040506.789-08';
           timestamptz            
----------------------------------
 Thu Dec 27 04:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone '2001-12-27 04:05:06.789-08';
           timestamptz            
----------------------------------
 Thu Dec 27 04:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone '2001.12.27 04:05:06.789-08';
           timestamptz            
----------------------------------
 Thu Dec 27 04:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone '2001/12/27 04:05:06.789-08';
           timestamptz            
----------------------------------
 Thu Dec 27 04:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone '12/27/2001 04:05:06.789-08';
           timestamptz            
----------------------------------
 Thu Dec 27 04:05:06.789 2001 PST
(1 row)

-- should fail in mdy mode:
SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
ERROR:  date/time field value out of range: "27/12/2001 04:05:06.789-08"
HINT:  Perhaps you need a different "datestyle" setting.
set datestyle to dmy;
SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
           timestamptz            
----------------------------------
 Thu 27 Dec 04:05:06.789 2001 PST
(1 row)

reset datestyle;
SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789+08';
           timestamptz            
----------------------------------
 Wed Dec 26 12:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789-08';
           timestamptz            
----------------------------------
 Thu Dec 27 04:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789+08';
           timestamptz            
----------------------------------
 Wed Dec 26 12:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789-08';
           timestamptz            
----------------------------------
 Thu Dec 27 04:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone 'J2452271+08';
         timestamptz          
------------------------------
 Wed Dec 26 08:00:00 2001 PST
(1 row)

SELECT timestamp with time zone 'J2452271-08';
         timestamptz          
------------------------------
 Thu Dec 27 00:00:00 2001 PST
(1 row)

SELECT timestamp with time zone 'J2452271.5+08';
         timestamptz          
------------------------------
 Wed Dec 26 20:00:00 2001 PST
(1 row)

SELECT timestamp with time zone 'J2452271.5-08';
         timestamptz          
------------------------------
 Thu Dec 27 12:00:00 2001 PST
(1 row)

SELECT timestamp with time zone 'J2452271 04:05:06+08';
         timestamptz          
------------------------------
 Wed Dec 26 12:05:06 2001 PST
(1 row)

SELECT timestamp with time zone 'J2452271 04:05:06-08';
         timestamptz          
------------------------------
 Thu Dec 27 04:05:06 2001 PST
(1 row)

SELECT timestamp with time zone 'J2452271T040506+08';
         timestamptz          
------------------------------
 Wed Dec 26 12:05:06 2001 PST
(1 row)

SELECT timestamp with time zone 'J2452271T040506-08';
         timestamptz          
------------------------------
 Thu Dec 27 04:05:06 2001 PST
(1 row)

SELECT timestamp with time zone 'J2452271T040506.789+08';
           timestamptz            
----------------------------------
 Wed Dec 26 12:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone 'J2452271T040506.789-08';
           timestamptz            
----------------------------------
 Thu Dec 27 04:05:06.789 2001 PST
(1 row)

-- German/European-style dates with periods as delimiters
SELECT timestamp with time zone '12.27.2001 04:05:06.789+08';
           timestamptz            
----------------------------------
 Wed Dec 26 12:05:06.789 2001 PST
(1 row)

SELECT timestamp with time zone '12.27.2001 04:05:06.789-08';
           timestamptz            
----------------------------------
 Thu Dec 27 04:05:06.789 2001 PST
(1 row)

SET DateStyle = 'German';
SELECT timestamp with time zone '27.12.2001 04:05:06.789+08';
         timestamptz         
-----------------------------
 26.12.2001 12:05:06.789 PST
(1 row)

SELECT timestamp with time zone '27.12.2001 04:05:06.789-08';
         timestamptz         
-----------------------------
 27.12.2001 04:05:06.789 PST
(1 row)

SET DateStyle = 'ISO';
-- As of 7.4, allow time without time zone having a time zone specified
SELECT time without time zone '040506.789+08';
     time     
--------------
 04:05:06.789
(1 row)

SELECT time without time zone '040506.789-08';
     time     
--------------
 04:05:06.789
(1 row)

SELECT time without time zone 'T040506.789+08';
     time     
--------------
 04:05:06.789
(1 row)

SELECT time without time zone 'T040506.789-08';
     time     
--------------
 04:05:06.789
(1 row)

SELECT time with time zone '040506.789+08';
     timetz      
-----------------
 04:05:06.789+08
(1 row)

SELECT time with time zone '040506.789-08';
     timetz      
-----------------
 04:05:06.789-08
(1 row)

SELECT time with time zone 'T040506.789+08';
     timetz      
-----------------
 04:05:06.789+08
(1 row)

SELECT time with time zone 'T040506.789-08';
     timetz      
-----------------
 04:05:06.789-08
(1 row)

SELECT time with time zone 'T040506.789 +08';
     timetz      
-----------------
 04:05:06.789+08
(1 row)

SELECT time with time zone 'T040506.789 -08';
     timetz      
-----------------
 04:05:06.789-08
(1 row)

SET DateStyle = 'Postgres, MDY';
--
-- date, time arithmetic
--
SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";
       Date + Time        
--------------------------
 Tue Feb 03 04:05:06 1981
(1 row)

SELECT date '1991-02-03' + time with time zone '04:05:06 PST' AS "Date + Time PST";
       Date + Time PST        
------------------------------
 Sun Feb 03 04:05:06 1991 PST
(1 row)

SELECT date '2001-02-03' + time with time zone '04:05:06 UTC' AS "Date + Time UTC";
       Date + Time UTC        
------------------------------
 Fri Feb 02 20:05:06 2001 PST
(1 row)

SELECT date '1991-02-03' + interval '2 years' AS "Add Two Years";
      Add Two Years       
--------------------------
 Wed Feb 03 00:00:00 1993
(1 row)

SELECT date '2001-12-13' - interval '2 years' AS "Subtract Two Years";
    Subtract Two Years    
--------------------------
 Mon Dec 13 00:00:00 1999
(1 row)

-- subtract time from date should not make sense; use interval instead
SELECT date '1991-02-03' - time '04:05:06' AS "Subtract Time";
      Subtract Time       
--------------------------
 Sat Feb 02 19:54:54 1991
(1 row)

SELECT date '1991-02-03' - time with time zone '04:05:06 UTC' AS "Subtract Time UTC";
ERROR:  operator does not exist: date - time with time zone
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
--
-- timestamp, interval arithmetic
--
SELECT timestamp without time zone '1996-03-01' - interval '1 second' AS "Feb 29";
          Feb 29          
--------------------------
 Thu Feb 29 23:59:59 1996
(1 row)

SELECT timestamp without time zone '1999-03-01' - interval '1 second' AS "Feb 28";
          Feb 28          
--------------------------
 Sun Feb 28 23:59:59 1999
(1 row)

SELECT timestamp without time zone '2000-03-01' - interval '1 second' AS "Feb 29";
          Feb 29          
--------------------------
 Tue Feb 29 23:59:59 2000
(1 row)

SELECT timestamp without time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31";
          Dec 31          
--------------------------
 Fri Dec 31 23:59:59 1999
(1 row)

SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' AS "Feb 23, 285506";
       Feb 23, 285506       
----------------------------
 Fri Feb 23 00:00:00 285506
(1 row)

SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244";
       Jan 20, 288244       
----------------------------
 Sat Jan 20 00:00:00 288244
(1 row)

SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276";
       Dec 31, 294276       
----------------------------
 Sun Dec 31 00:00:00 294276
(1 row)

SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days";
  106751991 Days  
------------------
 @ 106751991 days
(1 row)

-- Shorthand values
-- Not directly usable for regression testing since these are not constants.
-- So, just try to test parser and hope for the best - thomas 97/04/26
SELECT (timestamp without time zone 'today' = (timestamp without time zone 'yesterday' + interval '1 day')) as "True";
 True 
------
 t
(1 row)

SELECT (timestamp without time zone 'today' = (timestamp without time zone 'tomorrow' - interval '1 day')) as "True";
 True 
------
 t
(1 row)

SELECT (timestamp without time zone 'tomorrow' = (timestamp without time zone 'yesterday' + interval '2 days')) as "True";