-- -- TIMESTAMP -- CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without time zone); -- Test shorthand input values -- We can't just "select" the results since they aren't constants; test for -- equality instead. We can do that by running the test inside a transaction -- block, within which the value of 'now' shouldn't change, and so these -- related values shouldn't either. BEGIN; INSERT INTO TIMESTAMP_TBL VALUES ('today'); INSERT INTO TIMESTAMP_TBL VALUES ('yesterday'); INSERT INTO TIMESTAMP_TBL VALUES ('tomorrow'); SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'today'; one ----- 1 (1 row) SELECT count(*) AS One FROM TIMESTAMP_TBL WHERE d1 = timestamp without time zone 'yesterday'; one ----- 1 (1 row) COMMIT; -- Verify that 'now' *does* change over a reasonable interval such as 100 msec, -- and that it doesn't change over the same interval within a transaction block INSERT INTO TIMESTAMP_TBL VALUES ('now'); SELECT pg_sleep(0.1); pg_sleep ---------- (1 row) BEGIN; INSERT INTO TIMESTAMP_TBL VALUES ('now'); SELECT pg_sleep(0.1); pg_sleep ---------- (1 row) INSERT INTO TIMESTAMP_TBL VALUES ('now'); SELECT pg_sleep(0.1); pg_sleep ---------- (1 row) COMMIT; -- Special values INSERT INTO TIMESTAMP_TBL VALUES ('-infinity'); INSERT INTO TIMESTAMP_TBL VALUES ('infinity'); INSERT INTO TIMESTAMP_TBL VALUES ('epoch'); -- ISO 8601 format INSERT INTO TIMESTAMP_TBL VALUES ('1997-01-02'); INSERT INTO TIMESTAMP_TBL VALUES ('1997-01-02 03:04:05'); INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01-08'); INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01-0800'); INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 -08:00'); INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 -0800'); INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 17:32:01 -07:00'); INSERT INTO TIMESTAMP_TBL VALUES ('2001-09-22T18:19:20'); -- POSIX format (note that the timezone abbrev is just decoration here) INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 08:14:01 GMT+8'); INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 13:14:02 GMT-1'); INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 12:14:03 GMT-2'); INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 03:14:04 PST+8'); INSERT INTO TIMESTAMP_TBL VALUES ('2000-03-15 02:14:05 MST+7:00'); -- Variations for acceptable input formats INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997 -0800'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 5:32PM 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('1997/02/10 17:32:01-0800'); set datestyle to ymd; reset datestyle; -- this fails (even though TZ is a no-op, we still look it up) INSERT INTO TIMESTAMP_TBL VALUES ('19970710 173201 America/Does_not_exist'); ERROR: time zone "america/does_not_exist" not recognized LINE 1: INSERT INTO TIMESTAMP_TBL VALUES ('19970710 173201 America/D... ^ INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 11 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 12 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 13 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 14 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 15 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0097 BC'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0097'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 0597'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1097'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1697'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1797'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1897'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 2097'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 28 17:32:01 1996'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1996'); INSERT INTO TIMESTAMP_TBL VALUES ('Mar 01 17:32:01 1996'); INSERT INTO TIMESTAMP_TBL VALUES ('Dec 30 17:32:01 1996'); INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1996'); INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 28 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1997'); ERROR: date/time field value out of range: "Feb 29 17:32:01 1997" LINE 1: INSERT INTO TIMESTAMP_TBL VALUES ('Feb 29 17:32:01 1997'); ^ INSERT INTO TIMESTAMP_TBL VALUES ('Mar 01 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Dec 30 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1997'); INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 1999'); INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2000'); INSERT INTO TIMESTAMP_TBL VALUES ('Dec 31 17:32:01 2000'); INSERT INTO TIMESTAMP_TBL VALUES ('Jan 01 17:32:01 2001'); -- Currently unsupported syntax and ranges INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 -0097'); ERROR: time zone displacement out of range: "Feb 16 17:32:01 -0097" LINE 1: INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 -0097'); ^ INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 5097 BC'); ERROR: timestamp out of range: "Feb 16 17:32:01 5097 BC" LINE 1: INSERT INTO TIMESTAMP_TBL VALUES ('Feb 16 17:32:01 5097 BC')... ^ SELECT '4714-11-23 23:59:59 BC'::timestamp; -- out of range ERROR: timestamp out of range: "4714-11-23 23:59:59 BC" LINE 1: SELECT '4714-11-23 23:59:59 BC'::timestamp; ^ SELECT '294277-01-01 00:00:00'::timestamp; -- out of range ERROR: timestamp out of range: "294277-01-01 00:00:00" LINE 1: SELECT '294277-01-01 00:00:00'::timestamp; ^ -- verify date_bin behaves the same as date_trunc for relevant intervals -- case 1: AD dates, origin < input SELECT str, interval, date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2001-01-01') AS equal FROM ( VALUES ('week', '7 d'), ('day', '1 d'), ('hour', '1 h'), ('minute', '1 m'), ('second', '1 s'), ('millisecond', '1 ms'), ('microsecond', '1 us') ) intervals (str, interval), (VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts); str | interval | equal -------------+----------+------- week | 7 d | t day | 1 d | t hour | 1 h | t minute | 1 m | t second | 1 s | t millisecond | 1 ms | t microsecond | 1 us | t (7 rows) -- case 2: BC dates, origin < input SELECT str, interval, date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2000-01-01 BC') AS equal FROM ( VALUES ('week', '7 d'), ('day', '1 d'), ('hour', '1 h'), ('minute', '1 m'), ('second', '1 s'), ('millisecond', '1 ms'), ('microsecond', '1 us') ) intervals (str, interval), (VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts); str | interval | equal -------------+----------+------- week | 7 d | t day | 1 d | t hour | 1 h | t minute | 1 m | t second | 1 s | t millisecond | 1 ms | t microsecond | 1 us | t (7 rows) -- case 3: AD dates, origin > input SELECT str, interval, date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '2020-03-02') AS equal FROM ( VALUES ('week', '7 d'), ('day', '1 d'), ('hour', '1 h'), ('minute', '1 m'), ('second', '1 s'), ('millisecond', '1 ms'), ('microsecond', '1 us') ) intervals (str, interval), (VALUES (timestamp '2020-02-29 15:44:17.71393')) ts (ts); str | interval | equal -------------+----------+------- week | 7 d | t day | 1 d | t hour | 1 h | t minute | 1 m | t second | 1 s | t millisecond | 1 ms | t microsecond | 1 us | t (7 rows) -- case 4: BC dates, origin > input SELECT str, interval, date_trunc(str, ts) = date_bin(interval::interval, ts, timestamp '0055-06-17 BC') AS equal FROM ( VALUES ('week', '7 d'), ('day', '1 d'), ('hour', '1 h'), ('minute', '1 m'), ('second', '1 s'), ('millisecond', '1 ms'), ('microsecond', '1 us') ) intervals (str, interval), (VALUES (timestamp '0055-6-10 15:44:17.71393 BC')) ts (ts); str | interval | equal -------------+----------+------- week | 7 d | t day | 1 d | t hour | 1 h | t minute | 1 m | t second | 1 s | t millisecond | 1 ms | t microsecond | 1 us | t (7 rows) -- disallow intervals with months or years SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01'); ERROR: timestamps cannot be binned into intervals containing months or years SELECT date_bin('5 years'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01'); ERROR: timestamps cannot be binned into intervals containing months or years -- disallow zero intervals SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00'); ERROR: stride must be greater than zero -- disallow negative intervals SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00'); ERROR: stride must be greater than zero -- value near upper bound uses special case in code SELECT date_part('epoch', '294270-01-01 00:00:00'::timestamp); date_part --------------- 9224097091200 (1 row) SELECT extract(epoch from '294270-01-01 00:00:00'::timestamp); extract ---------------------- 9224097091200.000000 (1 row) -- another internal overflow test case SELECT extract(epoch from '5000-01-01 00:00:00'::timestamp); extract -------------------- 95617584000.000000 (1 row) SELECT to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff5 ff6 MS US') FROM (VALUES ('2018-11-02 12:34:56'::timestamp), ('2018-11-02 12:34:56.78'), ('2018-11-02 12:34:56.78901'), ('2018-11-02 12:34:56.78901234') ) d(d); to_char -------------------------------------------------------------------- 0 00 000 0000 00000 000000 0 00 000 0000 00000 000000 000 000000 7 78 780 7800 78000 780000 7 78 780 7800 78000 780000 780 780000 7 78 789 7890 78901 789010 7 78 789 7890 78901 789010 789 789010 7 78 789 7890 78901 789012 7 78 789 7890 78901 789012 789 789012 (4 rows) -- should fail select make_timestamp(0, 7, 15, 12, 30, 15); ERROR: date field value out of range: 0-07-15 -- errors select * from generate_series('2020-01-01 00:00'::timestamp, '2020-01-02 03:00'::timestamp, '0 hour'::interval); ERROR: step size cannot equal zero