horology.out

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

    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
LINE 1: SELECT date '1991-02-03' - time with time zone '04:05:06 UTC...
                                 ^
HINT:  No operator matches the given name and argument type(s). You might 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";
 True 
------
 t
(1 row)

SELECT (timestamp without time zone 'tomorrow' > 'now') as "True";
 True 
------
 t
(1 row)

-- Convert from date and time to timestamp
-- This test used to be timestamp(date,time) but no longer allowed by grammar
-- to enable support for SQL99 timestamp type syntax.
SELECT date '1994-01-01' + time '11:00' AS "Jan_01_1994_11am";
     Jan_01_1994_11am     
--------------------------
 Sat Jan 01 11:00:00 1994
(1 row)

SELECT date '1994-01-01' + time '10:00' AS "Jan_01_1994_10am";
     Jan_01_1994_10am     
--------------------------
 Sat Jan 01 10:00:00 1994
(1 row)

SELECT date '1994-01-01' + timetz '11:00-5' AS "Jan_01_1994_8am";
       Jan_01_1994_8am        
------------------------------
 Sat Jan 01 08:00:00 1994 PST
(1 row)

SELECT timestamptz(date '1994-01-01', time with time zone '11:00-5') AS "Jan_01_1994_8am";
       Jan_01_1994_8am        
------------------------------
 Sat Jan 01 08:00:00 1994 PST
(1 row)

SELECT '' AS "64", d1 + interval '1 year' AS one_year FROM TIMESTAMP_TBL;
 64 |          one_year           
----+-----------------------------
    | -infinity
    | infinity
    | Fri Jan 01 00:00:00 1971
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:02 1998
    | Tue Feb 10 17:32:01.40 1998
    | Tue Feb 10 17:32:01.50 1998
    | Tue Feb 10 17:32:01.60 1998
    | Fri Jan 02 00:00:00 1998
    | Fri Jan 02 03:04:05 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Wed Jun 10 17:32:01 1998
    | Sun Sep 22 18:19:20 2002
    | Thu Mar 15 08:14:01 2001
    | Thu Mar 15 13:14:02 2001
    | Thu Mar 15 12:14:03 2001
    | Thu Mar 15 03:14:04 2001
    | Thu Mar 15 02:14:05 2001
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:00 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Wed Jun 10 18:32:01 1998
    | Tue Feb 10 17:32:01 1998
    | Wed Feb 11 17:32:01 1998
    | Thu Feb 12 17:32:01 1998
    | Fri Feb 13 17:32:01 1998
    | Sat Feb 14 17:32:01 1998
    | Sun Feb 15 17:32:01 1998
    | Mon Feb 16 17:32:01 1998
    | Thu Feb 16 17:32:01 0096 BC
    | Sun Feb 16 17:32:01 0098
    | Fri Feb 16 17:32:01 0598
    | Wed Feb 16 17:32:01 1098
    | Sun Feb 16 17:32:01 1698
    | Fri Feb 16 17:32:01 1798
    | Wed Feb 16 17:32:01 1898
    | Mon Feb 16 17:32:01 1998
    | Sun Feb 16 17:32:01 2098
    | Fri Feb 28 17:32:01 1997
    | Fri Feb 28 17:32:01 1997
    | Sat Mar 01 17:32:01 1997
    | Tue Dec 30 17:32:01 1997
    | Wed Dec 31 17:32:01 1997
    | Thu Jan 01 17:32:01 1998
    | Sat Feb 28 17:32:01 1998
    | Sun Mar 01 17:32:01 1998
    | Wed Dec 30 17:32:01 1998
    | Thu Dec 31 17:32:01 1998
    | Sun Dec 31 17:32:01 2000
    | Mon Jan 01 17:32:01 2001
    | Mon Dec 31 17:32:01 2001
    | Tue Jan 01 17:32:01 2002
(65 rows)

SELECT '' AS "64", d1 - interval '1 year' AS one_year FROM TIMESTAMP_TBL;
 64 |          one_year           
----+-----------------------------
    | -infinity
    | infinity
    | Wed Jan 01 00:00:00 1969
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:02 1996
    | Sat Feb 10 17:32:01.40 1996
    | Sat Feb 10 17:32:01.50 1996
    | Sat Feb 10 17:32:01.60 1996
    | Tue Jan 02 00:00:00 1996
    | Tue Jan 02 03:04:05 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Mon Jun 10 17:32:01 1996
    | Fri Sep 22 18:19:20 2000
    | Mon Mar 15 08:14:01 1999
    | Mon Mar 15 13:14:02 1999
    | Mon Mar 15 12:14:03 1999
    | Mon Mar 15 03:14:04 1999
    | Mon Mar 15 02:14:05 1999
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:00 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Mon Jun 10 18:32:01 1996
    | Sat Feb 10 17:32:01 1996
    | Sun Feb 11 17:32:01 1996
    | Mon Feb 12 17:32:01 1996
    | Tue Feb 13 17:32:01 1996
    | Wed Feb 14 17:32:01 1996
    | Thu Feb 15 17:32:01 1996
    | Fri Feb 16 17:32:01 1996
    | Mon Feb 16 17:32:01 0098 BC
    | Thu Feb 16 17:32:01 0096
    | Tue Feb 16 17:32:01 0596
    | Sun Feb 16 17:32:01 1096
    | Thu Feb 16 17:32:01 1696
    | Tue Feb 16 17:32:01 1796
    | Sun Feb 16 17:32:01 1896
    | Fri Feb 16 17:32:01 1996
    | Thu Feb 16 17:32:01 2096
    | Tue Feb 28 17:32:01 1995
    | Tue Feb 28 17:32:01 1995
    | Wed Mar 01 17:32:01 1995
    | Sat Dec 30 17:32:01 1995
    | Sun Dec 31 17:32:01 1995
    | Mon Jan 01 17:32:01 1996
    | Wed Feb 28 17:32:01 1996
    | Fri Mar 01 17:32:01 1996
    | Mon Dec 30 17:32:01 1996
    | Tue Dec 31 17:32:01 1996
    | Thu Dec 31 17:32:01 1998
    | Fri Jan 01 17:32:01 1999
    | Fri Dec 31 17:32:01 1999
    | Sat Jan 01 17:32:01 2000
(65 rows)

SELECT timestamp with time zone '1996-03-01' - interval '1 second' AS "Feb 29";
            Feb 29            
------------------------------
 Thu Feb 29 23:59:59 1996 PST
(1 row)

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

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

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

SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True";
 True 
------
 t
(1 row)