-- -- TIMESTAMP -- --Testcase 1: CREATE EXTENSION sqlite_fdw; --Testcase 2: CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/tmp/sqlite_fdw_test/core.db'); --Testcase 3: CREATE FOREIGN TABLE dates1 ( name varchar(20), date_as_text timestamp without time zone, date_as_number timestamp without time zone OPTIONS (column_type 'INT')) SERVER sqlite_svr OPTIONS (table 'dates'); --Testcase 4: CREATE FOREIGN TABLE dates2 ( name varchar(20), date_as_text timestamp without time zone, date_as_number double precision) SERVER sqlite_svr OPTIONS (table 'dates'); -- Showing timestamp column from SQLite value as TEXT and as INTEGER/FLOAT has same value --Testcase 5: SELECT name, to_char(date_as_text, 'YYYY-MM-DD HH24:MI:SS.MS') as date_as_text, to_char(date_as_number, 'YYYY-MM-DD HH24:MI:SS.MS') as date_as_number FROM dates1; name | date_as_text | date_as_number ------------+-------------------------+------------------------- FirstDate | 2020-05-12 11:45:31.000 | 2020-05-12 11:45:31.000 SecondDate | 2020-05-12 13:32:31.000 | 2020-05-12 13:32:31.000 ThirdDate | 2020-05-10 10:45:29.000 | 2020-05-10 10:45:29.000 (3 rows) --Testcase 6: SELECT * FROM dates2; name | date_as_text | date_as_number ------------+--------------------------+---------------- FirstDate | Tue May 12 11:45:31 2020 | 1589283931 SecondDate | Tue May 12 13:32:31 2020 | 1589290351 ThirdDate | Sun May 10 10:45:29 2020 | 1589107529 (3 rows) -- Comparing exact values showing same results even comparing to a text source sqlite column or numerical source sqlite column --Testcase 7: SELECT * FROM dates1 WHERE date_as_text = to_timestamp('2020-05-10 10:45:29.000', 'YYYY-MM-DD HH24:MI:SS.MS'); name | date_as_text | date_as_number -----------+--------------------------+-------------------------- ThirdDate | Sun May 10 10:45:29 2020 | Sun May 10 10:45:29 2020 (1 row) --Testcase 8: SELECT * FROM dates1 WHERE date_as_number = to_timestamp('2020-05-10 10:45:29.000', 'YYYY-MM-DD HH24:MI:SS.MS'); name | date_as_text | date_as_number -----------+--------------------------+-------------------------- ThirdDate | Sun May 10 10:45:29 2020 | Sun May 10 10:45:29 2020 (1 row) --Testcase 9: SELECT * FROM dates1 WHERE date_as_text = to_timestamp('2020-05-10 10:45:29', 'YYYY-MM-DD HH24:MI:SS.MS'); name | date_as_text | date_as_number -----------+--------------------------+-------------------------- ThirdDate | Sun May 10 10:45:29 2020 | Sun May 10 10:45:29 2020 (1 row) --Testcase 10: SELECT * FROM dates1 WHERE date_as_number = to_timestamp('2020-05-10 10:45:29', 'YYYY-MM-DD HH24:MI:SS.MS'); name | date_as_text | date_as_number -----------+--------------------------+-------------------------- ThirdDate | Sun May 10 10:45:29 2020 | Sun May 10 10:45:29 2020 (1 row) -- Comparing greater values showing same results even comparing to a text source sqlite column or numerical source sqlite column --Testcase 11: SELECT * FROM dates1 WHERE date_as_text > to_timestamp('2020-05-10 10:45:29.000', 'YYYY-MM-DD HH24:MI:SS.MS'); name | date_as_text | date_as_number ------------+--------------------------+-------------------------- FirstDate | Tue May 12 11:45:31 2020 | Tue May 12 11:45:31 2020 SecondDate | Tue May 12 13:32:31 2020 | Tue May 12 13:32:31 2020 (2 rows) --Testcase 12: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_text > to_timestamp('2020-05-10 10:45:29.000', 'YYYY-MM-DD HH24:MI:SS.MS'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number Filter: (dates1.date_as_text > to_timestamp('2020-05-10 10:45:29.000'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text)) SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" (4 rows) --Testcase 13: SELECT * FROM dates1 WHERE date_as_number > to_timestamp('2020-05-10 10:45:29.000', 'YYYY-MM-DD HH24:MI:SS.MS'); name | date_as_text | date_as_number ------------+--------------------------+-------------------------- FirstDate | Tue May 12 11:45:31 2020 | Tue May 12 11:45:31 2020 SecondDate | Tue May 12 13:32:31 2020 | Tue May 12 13:32:31 2020 (2 rows) --Testcase 14: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_number > to_timestamp('2020-05-10 10:45:29.000', 'YYYY-MM-DD HH24:MI:SS.MS'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number Filter: (dates1.date_as_number > to_timestamp('2020-05-10 10:45:29.000'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text)) SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" (4 rows) --Testcase 15: SELECT * FROM dates1 WHERE date_as_text > to_timestamp('2020-05-10 10:45:29', 'YYYY-MM-DD HH24:MI:SS.MS'); name | date_as_text | date_as_number ------------+--------------------------+-------------------------- FirstDate | Tue May 12 11:45:31 2020 | Tue May 12 11:45:31 2020 SecondDate | Tue May 12 13:32:31 2020 | Tue May 12 13:32:31 2020 (2 rows) --Testcase 16: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_text > to_timestamp('2020-05-10 10:45:29', 'YYYY-MM-DD HH24:MI:SS.MS'); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number Filter: (dates1.date_as_text > to_timestamp('2020-05-10 10:45:29'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text)) SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" (4 rows) --Testcase 17: SELECT * FROM dates1 WHERE date_as_number > to_timestamp('2020-05-10 10:45:29', 'YYYY-MM-DD HH24:MI:SS.MS'); name | date_as_text | date_as_number ------------+--------------------------+-------------------------- FirstDate | Tue May 12 11:45:31 2020 | Tue May 12 11:45:31 2020 SecondDate | Tue May 12 13:32:31 2020 | Tue May 12 13:32:31 2020 (2 rows) --Testcase 18: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_number > to_timestamp('2020-05-10 10:45:29', 'YYYY-MM-DD HH24:MI:SS.MS'); QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number Filter: (dates1.date_as_number > to_timestamp('2020-05-10 10:45:29'::text, 'YYYY-MM-DD HH24:MI:SS.MS'::text)) SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" (4 rows) --- Comparing without using to_timestamp --Testcase 19: SELECT * FROM dates1 WHERE date_as_text = (('2020-05-10 10:45:29.000')::timestamp); name | date_as_text | date_as_number -----------+--------------------------+-------------------------- ThirdDate | Sun May 10 10:45:29 2020 | Sun May 10 10:45:29 2020 (1 row) --Testcase 20: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_text = (('2020-05-10 10:45:29.000')::timestamp); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" WHERE ((`date_as_text` = '2020-05-10 10:45:29')) (3 rows) --Testcase 21: SELECT * FROM dates1 WHERE date_as_number = (('2020-05-10 10:45:29.000')::timestamp); name | date_as_text | date_as_number -----------+--------------------------+-------------------------- ThirdDate | Sun May 10 10:45:29 2020 | Sun May 10 10:45:29 2020 (1 row) --Testcase 22: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_number = (('2020-05-10 10:45:29.000')::timestamp); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" WHERE ((`date_as_number` = strftime('%s', '2020-05-10 10:45:29'))) (3 rows) --Testcase 23: SELECT * FROM dates1 WHERE date_as_text = (('2020-05-10 10:45:29')::timestamp); name | date_as_text | date_as_number -----------+--------------------------+-------------------------- ThirdDate | Sun May 10 10:45:29 2020 | Sun May 10 10:45:29 2020 (1 row) --Testcase 24: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_text = (('2020-05-10 10:45:29')::timestamp); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" WHERE ((`date_as_text` = '2020-05-10 10:45:29')) (3 rows) --Testcase 25: SELECT * FROM dates1 WHERE date_as_number = (('2020-05-10 10:45:29')::timestamp); name | date_as_text | date_as_number -----------+--------------------------+-------------------------- ThirdDate | Sun May 10 10:45:29 2020 | Sun May 10 10:45:29 2020 (1 row) --Testcase 26: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_number = (('2020-05-10 10:45:29')::timestamp); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" WHERE ((`date_as_number` = strftime('%s', '2020-05-10 10:45:29'))) (3 rows) -- Comparing greater values without using to_timestamp --Testcase 27: SELECT * FROM dates1 WHERE date_as_text > (('2020-05-10 10:45:29.000')::timestamp); name | date_as_text | date_as_number ------------+--------------------------+-------------------------- FirstDate | Tue May 12 11:45:31 2020 | Tue May 12 11:45:31 2020 SecondDate | Tue May 12 13:32:31 2020 | Tue May 12 13:32:31 2020 (2 rows) --Testcase 28: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_text > (('2020-05-10 10:45:29.000')::timestamp); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" WHERE ((`date_as_text` > '2020-05-10 10:45:29')) (3 rows) --Testcase 29: SELECT * FROM dates1 WHERE date_as_number > (('2020-05-10 10:45:29.000')::timestamp); name | date_as_text | date_as_number ------------+--------------------------+-------------------------- FirstDate | Tue May 12 11:45:31 2020 | Tue May 12 11:45:31 2020 SecondDate | Tue May 12 13:32:31 2020 | Tue May 12 13:32:31 2020 (2 rows) --Testcase 30: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_number > (('2020-05-10 10:45:29.000')::timestamp); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" WHERE ((`date_as_number` > strftime('%s', '2020-05-10 10:45:29'))) (3 rows) --Testcase 31: SELECT * FROM dates1 WHERE date_as_text > (('2020-05-10 10:45:29')::timestamp); name | date_as_text | date_as_number ------------+--------------------------+-------------------------- FirstDate | Tue May 12 11:45:31 2020 | Tue May 12 11:45:31 2020 SecondDate | Tue May 12 13:32:31 2020 | Tue May 12 13:32:31 2020 (2 rows) --Testcase 32: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_text > (('2020-05-10 10:45:29')::timestamp); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" WHERE ((`date_as_text` > '2020-05-10 10:45:29')) (3 rows) --Testcase 33: SELECT * FROM dates1 WHERE date_as_number > (('2020-05-10 10:45:29')::timestamp); name | date_as_text | date_as_number ------------+--------------------------+-------------------------- FirstDate | Tue May 12 11:45:31 2020 | Tue May 12 11:45:31 2020 SecondDate | Tue May 12 13:32:31 2020 | Tue May 12 13:32:31 2020 (2 rows) --Testcase 34: explain (verbose, costs off) SELECT * FROM dates1 WHERE date_as_number > (('2020-05-10 10:45:29')::timestamp); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.dates1 Output: name, date_as_text, date_as_number SQLite query: SELECT `name`, `date_as_text`, `date_as_number` FROM main."dates" WHERE ((`date_as_number` > strftime('%s', '2020-05-10 10:45:29'))) (3 rows) -- test arithmetic with infinite timestamps --Testcase 39: CREATE FOREIGN TABLE inf_timestamp ( t1 TIMESTAMP , t2 TIMESTAMP, id int OPTIONS (key 'true')) SERVER sqlite_svr OPTIONS (table 'infinite_timestamp');; --Testcase 40: INSERT INTO inf_timestamp VALUES ('infinity'::timestamp, 'infinity'::timestamp); --Testcase 41: SELECT t1 - t2 FROM inf_timestamp; ERROR: interval out of range --Testcase 42: DELETE FROM inf_timestamp; --Testcase 43: INSERT INTO inf_timestamp VALUES ('infinity'::timestamp, '-infinity'::timestamp); --Testcase 44: SELECT t1 - t2 FROM inf_timestamp; ?column? ---------- infinity (1 row) --Testcase 45: DELETE FROM inf_timestamp; --Testcase 46: INSERT INTO inf_timestamp VALUES ('-infinity'::timestamp, 'infinity'::timestamp); --Testcase 47: SELECT t1 - t2 FROM inf_timestamp; ?column? ----------- -infinity (1 row) --Testcase 48: DELETE FROM inf_timestamp; --Testcase 49: INSERT INTO inf_timestamp VALUES ('-infinity'::timestamp, '-infinity'::timestamp); --Testcase 50: SELECT t1 - t2 FROM inf_timestamp; ERROR: interval out of range --Testcase 51: DELETE FROM inf_timestamp; --Testcase 52: INSERT INTO inf_timestamp VALUES ('infinity'::timestamp, '1995-08-06 12:12:12'::timestamp); --Testcase 53: SELECT t1 - t2 FROM inf_timestamp; ?column? ---------- infinity (1 row) --Testcase 54: DELETE FROM inf_timestamp; --Testcase 55: INSERT INTO inf_timestamp VALUES ('-infinity'::timestamp, '1995-08-06 12:12:12'::timestamp); --Testcase 56: SELECT t1 - t2 FROM inf_timestamp; ?column? ----------- -infinity (1 row) -- test age() with infinite timestamps --Testcase 58: DELETE FROM inf_timestamp; --Testcase 59: INSERT INTO inf_timestamp(t1) VALUES ('infinity'::timestamp); --Testcase 60: SELECT age(t1) FROM inf_timestamp; age ----------- -infinity (1 row) --Testcase 61: DELETE FROM inf_timestamp; --Testcase 62: INSERT INTO inf_timestamp(t1) VALUES ('-infinity'::timestamp); --Testcase 63: SELECT age(t1) FROM inf_timestamp; age ---------- infinity (1 row) --Testcase 64: DELETE FROM inf_timestamp; --Testcase 65: INSERT INTO inf_timestamp VALUES ('infinity'::timestamp, 'infinity':: timestamp); --Testcase 66: SELECT age(t1, t2) FROM inf_timestamp; ERROR: interval out of range --Testcase 67: DELETE FROM inf_timestamp; --Testcase 68: INSERT INTO inf_timestamp VALUES ('infinity'::timestamp, '-infinity':: timestamp); --Testcase 69: SELECT age(t1, t2) FROM inf_timestamp; age ---------- infinity (1 row) --Testcase 70: DELETE FROM inf_timestamp; --Testcase 71: INSERT INTO inf_timestamp VALUES ('-infinity'::timestamp, 'infinity':: timestamp); --Testcase 72: SELECT age(t1, t2) FROM inf_timestamp; age ----------- -infinity (1 row) --Testcase 73: DELETE FROM inf_timestamp; --Testcase 74: INSERT INTO inf_timestamp VALUES ('-infinity'::timestamp, '-infinity':: timestamp); --Testcase 75: SELECT age(t1, t2) FROM inf_timestamp; ERROR: interval out of range --Testcase 35: DROP FOREIGN TABLE dates1; --Testcase 36: DROP FOREIGN TABLE dates2; --Testcase 57: DROP FOREIGN TABLE inf_timestamp; --Testcase 37: DROP SERVER sqlite_svr; --Testcase 38: DROP EXTENSION sqlite_fdw CASCADE;