--
-- TIMESTAMP
--
CREATE EXTENSION sqlite_fdw;
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlitefdw_test_core.db');
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');
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
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)

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
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)

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)

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)

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
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)

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)

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)

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)

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)

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)

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)

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
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)

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)

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)

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)

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)

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)

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)

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
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)

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)

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)

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)

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)

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)

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)

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)

DROP FOREIGN TABLE dates1;
DROP FOREIGN TABLE dates2;
DROP SERVER sqlite_svr;
DROP EXTENSION sqlite_fdw CASCADE;