1-- 2-- TIMESTAMP 3-- 4 5CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without time zone); 6 7-- Test shorthand input values 8-- We can't just "select" the results since they aren't constants; test for 9-- equality instead. We can do that by running the test inside a transaction 10-- block, within which the value of 'now' shouldn't change. We also check 11-- that 'now' *does* change over a reasonable interval such as 100 msec. 12-- NOTE: it is possible for this part of the test to fail if the transaction 13-- block is entered exactly at local midnight; then 'now' and 'today' have 14-- the same values and the counts will come out different. 15 16INSERT INTO TIMESTAMP_TBL VALUES ('now'); 17SELECT pg_sleep(0.1); 18 19BEGIN; 20 21INSERT INTO TIMESTAMP_TBL VALUES ('now'); 22INSERT INTO TIMESTAMP_TBL VALUES ('today'); 23INSERT INTO TIMESTAMP_TBL VALUES ('yesterday'); 24INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow'); 25-- time zone should be ignored by this data type 26INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow EST'); 27INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow zulu'); 28 29SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'today'; 30SELECT count(*) AS Three FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'tomorrow'; 31SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'yesterday'; 32SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now'; 33 34COMMIT; 35 36DELETE FROM TIMESTAMP_TBL; 37 38-- verify uniform transaction time within transaction block 39BEGIN; 40INSERT INTO TIMESTAMP_TBL VALUES ('now'); 41SELECT pg_sleep(0.1); 42INSERT INTO TIMESTAMP_TBL VALUES ('now'); 43SELECT pg_sleep(0.1); 44SELECT count(*) AS two FROM TIMESTAMP_TBL WHERE d1 = timestamp(2) without time zone 'now'; 45COMMIT; 46 47TRUNCATE TIMESTAMP_TBL; 48 49-- Special values 50INSERT INTO TIMESTAMP_TBL VALUES ('-infinity'); 51INSERT INTO TIMESTAMP_TBL VALUES ('infinity'); 52INSERT INTO TIMESTAMP_TBL VALUES ('epoch'); 53-- Obsolete special values 54INSERT INTO TIMESTAMP_TBL VALUES ('invalid'); 55INSERT INTO TIMESTAMP_TBL VALUES ('undefined'); 56INSERT INTO TIMESTAMP_TBL VALUES ('current'); 57 58-- Postgres v6.0 standard output format 59INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01 1997 PST'); 60 61-- Variations on Postgres v6.1 standard output format 62INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.000001 1997 PST'); 63INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.999999 1997 PST'); 64INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.4 1997 PST'); 65INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.5 1997 PST'); 66INSERT INTO TIMESTAMP_TBL VALUES ('Mon Feb 10 17:32:01.6 1997 PST'); 67 68-- ISO 8601 format 69INSERT INTO TIMESTAMP_TBL VALUES ('1997-01-02'); 70INSERT INTO TIMESTAMP_TBL VALUES ('1997-01-02 03:04:05'); 71INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01-08'); 72INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01-0800'); 73INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 -08:00'); 74INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 -0800'); 75INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 17:32:01 -07:00'); 76INSERT INTO TIMESTAMP_TBL VALUES ('2001-09-22T18:19:20'); 77 78-- POSIX format (note that the timezone abbrev is just decoration here) 79INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 08:14:01 GMT+8'); 80INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 13:14:02 GMT-1'); 81INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT-2'); 82INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 PST+8'); 83INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 MST+7:00'); 84 85-- Variations for acceptable input formats 86INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997 -0800'); 87INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997'); 88INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 5:32PM 1997'); 89INSERT INTO TIMESTAMP_TBL VALUES ('1997/02/10 17:32:01-0800'); 90INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 PST'); 91INSERT INTO TIMESTAMP_TBL VALUES ('Feb-10-1997 17:32:01 PST'); 92INSERT INTO TIMESTAMP_TBL VALUES ('02-10-1997 17:32:01 PST'); 93INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 PST'); 94set datestyle to ymd; 95INSERT INTO TIMESTAMP_TBL VALUES ('97FEB10 5:32:01PM UTC'); 96INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC'); 97reset datestyle; 98INSERT INTO TIMESTAMP_TBL VALUES ('1997.041 17:32:01 UTC'); 99INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 America/New_York'); 100-- this fails (even though TZ is a no-op, we still look it up) 101INSERT INTO TIMESTAMP_TBL VALUES ('19970710 173201 America/Does_not_exist'); 102 103-- Check date conversion and date arithmetic 104INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 18:32:01 PDT'); 105 106INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997'); 107INSERT INTO TIMESTAMP_TBL VALUES ('Feb 11 17:32:01 1997'); 108INSERT INTO TIMESTAMP_TBL VALUES ('Feb 12 17:32:01 1997'); 109INSERT INTO TIMESTAMP_TBL VALUES ('Feb 13 17:32:01 1997'); 110INSERT INTO TIMESTAMP_TBL VALUES ('Feb 14 17:32:01 1997'); 111INSERT INTO TIMESTAMP_TBL VALUES ('Feb 15 17:32:01 1997'); 112INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1997'); 113 114INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0097 BC'); 115INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0097'); 116INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0597'); 117INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1097'); 118INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1697'); 119INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1797'); 120INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1897'); 121INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1997'); 122INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 2097'); 123 124INSERT INTO TIMESTAMP_TBL VALUES ('Feb 28 17:32:01 1996'); 125INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1996'); 126INSERT INTO TIMESTAMP_TBL VALUES ('Mar 01 17:32:01 1996'); 127INSERT INTO TIMESTAMP_TBL VALUES ('Dec 30 17:32:01 1996'); 128INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1996'); 129INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 1997'); 130INSERT INTO TIMESTAMP_TBL VALUES ('Feb 28 17:32:01 1997'); 131INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1997'); 132INSERT INTO TIMESTAMP_TBL VALUES ('Mar 01 17:32:01 1997'); 133INSERT INTO TIMESTAMP_TBL VALUES ('Dec 30 17:32:01 1997'); 134INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1997'); 135INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1999'); 136INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2000'); 137INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 2000'); 138INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2001'); 139 140-- Currently unsupported syntax and ranges 141INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 -0097'); 142INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 5097 BC'); 143 144SELECT '' AS "64", d1 FROM TIMESTAMP_TBL; 145 146-- Check behavior at the lower boundary of the timestamp range 147SELECT '4714-11-24 00:00:00 BC'::timestamp; 148SELECT '4714-11-23 23:59:59 BC'::timestamp; -- out of range 149-- The upper boundary differs between integer and float timestamps, so no check 150 151-- Demonstrate functions and operators 152SELECT '' AS "48", d1 FROM TIMESTAMP_TBL 153 WHERE d1 > timestamp without time zone '1997-01-02'; 154 155SELECT '' AS "15", d1 FROM TIMESTAMP_TBL 156 WHERE d1 < timestamp without time zone '1997-01-02'; 157 158SELECT '' AS one, d1 FROM TIMESTAMP_TBL 159 WHERE d1 = timestamp without time zone '1997-01-02'; 160 161SELECT '' AS "63", d1 FROM TIMESTAMP_TBL 162 WHERE d1 != timestamp without time zone '1997-01-02'; 163 164SELECT '' AS "16", d1 FROM TIMESTAMP_TBL 165 WHERE d1 <= timestamp without time zone '1997-01-02'; 166 167SELECT '' AS "49", d1 FROM TIMESTAMP_TBL 168 WHERE d1 >= timestamp without time zone '1997-01-02'; 169 170SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff 171 FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; 172 173SELECT '' AS date_trunc_week, date_trunc( 'week', timestamp '2004-02-29 15:44:17.71393' ) AS week_trunc; 174 175-- Test casting within a BETWEEN qualifier 176SELECT '' AS "54", d1 - timestamp without time zone '1997-01-02' AS diff 177 FROM TIMESTAMP_TBL 178 WHERE d1 BETWEEN timestamp without time zone '1902-01-01' 179 AND timestamp without time zone '2038-01-01'; 180 181SELECT '' AS "54", d1 as "timestamp", 182 date_part( 'year', d1) AS year, date_part( 'month', d1) AS month, 183 date_part( 'day', d1) AS day, date_part( 'hour', d1) AS hour, 184 date_part( 'minute', d1) AS minute, date_part( 'second', d1) AS second 185 FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; 186 187SELECT '' AS "54", d1 as "timestamp", 188 date_part( 'quarter', d1) AS quarter, date_part( 'msec', d1) AS msec, 189 date_part( 'usec', d1) AS usec 190 FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; 191 192SELECT '' AS "54", d1 as "timestamp", 193 date_part( 'isoyear', d1) AS isoyear, date_part( 'week', d1) AS week, 194 date_part( 'dow', d1) AS dow 195 FROM TIMESTAMP_TBL WHERE d1 BETWEEN '1902-01-01' AND '2038-01-01'; 196 197-- TO_CHAR() 198SELECT '' AS to_char_1, to_char(d1, 'DAY Day day DY Dy dy MONTH Month month RM MON Mon mon') 199 FROM TIMESTAMP_TBL; 200 201SELECT '' AS to_char_2, to_char(d1, 'FMDAY FMDay FMday FMMONTH FMMonth FMmonth FMRM') 202 FROM TIMESTAMP_TBL; 203 204SELECT '' AS to_char_3, to_char(d1, 'Y,YYY YYYY YYY YY Y CC Q MM WW DDD DD D J') 205 FROM TIMESTAMP_TBL; 206 207SELECT '' AS to_char_4, to_char(d1, 'FMY,YYY FMYYYY FMYYY FMYY FMY FMCC FMQ FMMM FMWW FMDDD FMDD FMD FMJ') 208 FROM TIMESTAMP_TBL; 209 210SELECT '' AS to_char_5, to_char(d1, 'HH HH12 HH24 MI SS SSSS') 211 FROM TIMESTAMP_TBL; 212 213SELECT '' AS to_char_6, to_char(d1, E'"HH:MI:SS is" HH:MI:SS "\\"text between quote marks\\""') 214 FROM TIMESTAMP_TBL; 215 216SELECT '' AS to_char_7, to_char(d1, 'HH24--text--MI--text--SS') 217 FROM TIMESTAMP_TBL; 218 219SELECT '' AS to_char_8, to_char(d1, 'YYYYTH YYYYth Jth') 220 FROM TIMESTAMP_TBL; 221 222SELECT '' AS to_char_9, to_char(d1, 'YYYY A.D. YYYY a.d. YYYY bc HH:MI:SS P.M. HH:MI:SS p.m. HH:MI:SS pm') 223 FROM TIMESTAMP_TBL; 224 225SELECT '' AS to_char_10, to_char(d1, 'IYYY IYY IY I IW IDDD ID') 226 FROM TIMESTAMP_TBL; 227 228SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') 229 FROM TIMESTAMP_TBL; 230 231-- timestamp numeric fields constructor 232SELECT make_timestamp(2014,12,28,6,30,45.887); 233