1 -- *** testing new built-in time types: datetime, timespan ***
4 -- Not directly usable for regression testing since these are not constants.
5 -- So, just try to test parser and hope for the best - tgl 97/04/26
7 SELECT ('today'::datetime = ('yesterday'::datetime + '1 day'::timespan)) as "True";
8 SELECT ('today'::datetime = ('tomorrow'::datetime - '1 day'::timespan)) as "True";
9 SELECT ('tomorrow'::datetime = ('yesterday'::datetime + '2 days'::timespan)) as "True";
10 SELECT ('current'::datetime = 'now'::datetime) as "True";
11 SELECT ('now'::datetime - 'current'::datetime) AS "ZeroSecs";
13 CREATE TABLE DATETIME_TBL( d1 datetime);
15 INSERT INTO DATETIME_TBL VALUES ('current');
16 INSERT INTO DATETIME_TBL VALUES ('today');
17 INSERT INTO DATETIME_TBL VALUES ('yesterday');
18 INSERT INTO DATETIME_TBL VALUES ('tomorrow');
19 INSERT INTO DATETIME_TBL VALUES ('tomorrow EST');
20 INSERT INTO DATETIME_TBL VALUES ('tomorrow zulu');
22 SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = 'today'::datetime;
23 SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = 'tomorrow'::datetime;
24 SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = 'yesterday'::datetime;
25 SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = 'today'::datetime + '1 day'::timespan;
26 SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = 'today'::datetime - '1 day'::timespan;
28 SELECT count(*) AS one FROM DATETIME_TBL WHERE d1 = 'now'::datetime;
30 DELETE FROM DATETIME_TBL;
32 -- verify uniform transaction time within transaction block
33 INSERT INTO DATETIME_TBL VALUES ('current');
35 INSERT INTO DATETIME_TBL VALUES ('now');
36 SELECT count(*) AS two FROM DATETIME_TBL WHERE d1 = 'now'::datetime;
38 DELETE FROM DATETIME_TBL;
41 INSERT INTO DATETIME_TBL VALUES ('invalid');
42 INSERT INTO DATETIME_TBL VALUES ('-infinity');
43 INSERT INTO DATETIME_TBL VALUES ('infinity');
44 INSERT INTO DATETIME_TBL VALUES ('epoch');
46 -- Postgres v6.0 standard output format
47 INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST');
48 INSERT INTO DATETIME_TBL VALUES ('Invalid Abstime');
49 INSERT INTO DATETIME_TBL VALUES ('Undefined Abstime');
51 -- Variations on Postgres v6.1 standard output format
52 INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST');
53 INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST');
54 INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST');
55 INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST');
56 INSERT INTO DATETIME_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST');
59 INSERT INTO DATETIME_TBL VALUES ('1997-01-02');
60 INSERT INTO DATETIME_TBL VALUES ('1997-01-02 03:04:05');
61 INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01-08');
62 INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01-0800');
63 INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01 -08:00');
64 INSERT INTO DATETIME_TBL VALUES ('19970210 173201 -0800');
65 INSERT INTO DATETIME_TBL VALUES ('1997-06-10 17:32:01 -07:00');
67 -- Variations for acceptable input formats
68 INSERT INTO DATETIME_TBL VALUES ('Feb 10 17:32:01 1997 -0800');
69 INSERT INTO DATETIME_TBL VALUES ('Feb 10 17:32:01 1997');
70 INSERT INTO DATETIME_TBL VALUES ('Feb 10 5:32PM 1997');
71 INSERT INTO DATETIME_TBL VALUES ('1997/02/10 17:32:01-0800');
72 INSERT INTO DATETIME_TBL VALUES ('1997-02-10 17:32:01 PST');
73 INSERT INTO DATETIME_TBL VALUES ('Feb-10-1997 17:32:01 PST');
74 INSERT INTO DATETIME_TBL VALUES ('02-10-1997 17:32:01 PST');
75 INSERT INTO DATETIME_TBL VALUES ('19970210 173201 PST');
76 INSERT INTO DATETIME_TBL VALUES ('97FEB10 5:32:01PM UTC');
77 INSERT INTO DATETIME_TBL VALUES ('97/02/10 17:32:01 UTC');
78 INSERT INTO DATETIME_TBL VALUES ('97.041 17:32:01 UTC');
80 -- Check date conversion and date arithmetic
81 INSERT INTO DATETIME_TBL VALUES ('1997-06-10 18:32:01 PDT');
83 INSERT INTO DATETIME_TBL VALUES ('Feb 10 17:32:01 1997');
84 INSERT INTO DATETIME_TBL VALUES ('Feb 11 17:32:01 1997');
85 INSERT INTO DATETIME_TBL VALUES ('Feb 12 17:32:01 1997');
86 INSERT INTO DATETIME_TBL VALUES ('Feb 13 17:32:01 1997');
87 INSERT INTO DATETIME_TBL VALUES ('Feb 14 17:32:01 1997');
88 INSERT INTO DATETIME_TBL VALUES ('Feb 15 17:32:01 1997');
89 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1997');
91 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 0097 BC');
92 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 0097');
93 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 0597');
94 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1097');
95 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1697');
96 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1797');
97 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1897');
98 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 1997');
99 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 2097');
101 INSERT INTO DATETIME_TBL VALUES ('Feb 28 17:32:01 1996');
102 INSERT INTO DATETIME_TBL VALUES ('Feb 29 17:32:01 1996');
103 INSERT INTO DATETIME_TBL VALUES ('Mar 01 17:32:01 1996');
104 INSERT INTO DATETIME_TBL VALUES ('Dec 30 17:32:01 1996');
105 INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 1996');
106 INSERT INTO DATETIME_TBL VALUES ('Jan 01 17:32:01 1997');
107 INSERT INTO DATETIME_TBL VALUES ('Feb 28 17:32:01 1997');
108 INSERT INTO DATETIME_TBL VALUES ('Feb 29 17:32:01 1997');
109 INSERT INTO DATETIME_TBL VALUES ('Mar 01 17:32:01 1997');
110 INSERT INTO DATETIME_TBL VALUES ('Dec 30 17:32:01 1997');
111 INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 1997');
112 INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 1999');
113 INSERT INTO DATETIME_TBL VALUES ('Jan 01 17:32:01 2000');
114 INSERT INTO DATETIME_TBL VALUES ('Dec 31 17:32:01 2000');
115 INSERT INTO DATETIME_TBL VALUES ('Jan 01 17:32:01 2001');
117 -- Currently unsupported syntax and ranges
118 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 -0097');
119 INSERT INTO DATETIME_TBL VALUES ('Feb 16 17:32:01 5097 BC');
121 SELECT '' AS sixtythree, d1 FROM DATETIME_TBL;
123 -- Demonstrate functions and operators
124 SELECT '' AS fortythree, d1 FROM DATETIME_TBL
125 WHERE d1 > '1997-01-02'::datetime and d1 != 'current'::datetime;
127 SELECT '' AS fifteen, d1 FROM DATETIME_TBL
128 WHERE d1 < '1997-01-02'::datetime and d1 != 'current'::datetime;
130 SELECT '' AS one, d1 FROM DATETIME_TBL
131 WHERE d1 = '1997-01-02'::datetime and d1 != 'current'::datetime;
133 SELECT '' AS fiftyeight, d1 FROM DATETIME_TBL
134 WHERE d1 != '1997-01-02'::datetime and d1 != 'current'::datetime;
136 SELECT '' AS sixteen, d1 FROM DATETIME_TBL
137 WHERE d1 <= '1997-01-02'::datetime and d1 != 'current'::datetime;
139 SELECT '' AS fortyfour, d1 FROM DATETIME_TBL
140 WHERE d1 >= '1997-01-02'::datetime and d1 != 'current'::datetime;
142 SELECT '' AS sixtythree, d1 + '1 year'::timespan AS one_year FROM DATETIME_TBL;
144 SELECT '' AS sixtythree, d1 - '1 year'::timespan AS one_year FROM DATETIME_TBL;
146 -- Casting within a BETWEEN qualifier should probably be allowed by the parser. - tgl 97/04/26
147 --SELECT '' AS fifty, d1 - '1997-01-02'::datetime AS diff
148 -- FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01'::datetime AND '2038-01-01'::datetime;
149 SELECT '' AS fifty, d1 - '1997-01-02'::datetime AS diff
150 FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
152 SELECT '' AS fortynine, date_part( 'year', d1) AS year, date_part( 'month', d1) AS month,
153 date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour,
154 date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second
155 FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';
157 SELECT '' AS fortynine, date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec,
158 date_part( 'usec', d1) AS usec
159 FROM DATETIME_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01';