horology.out

来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 1,456 行 · 第 1/5 页

--
-- HOROLOGY
--
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";