Skip to content

Commit 78454c2

Browse files
scrappyscrappy
committed
*shrug*
1 parent df77071 commit 78454c2

File tree

4 files changed

+752
-0
lines changed

4 files changed

+752
-0
lines changed

‎src/test/regress/sql/datetime.sql

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

0 commit comments

Comments
 (0)