On 9/13/16, Patrick B <patrickbakerbr@xxxxxxxxx> wrote: > Hi guys, > > I got the following column: > > modified_date TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT >> "statement_timestamp"(), > > > I want to select all rows that have been modified from now to 4 months ago. > > I've used these queries: > > select >> modified_date, >> from >> clients >> WHERE >> modified_date BETWEEN '2016-06-13' AND '2016-09-13' Note that '2016-09-13' is not "now", it is converted to the data type of a column (expression): Your expression "modified_date BETWEEN '2016-06-13' AND '2016-09-13'" means a little different: The best way to understand it - to use explain: postgres=# EXPLAIN select modified_date from clients WHERE modified_date BETWEEN '2016-06-13' AND '2016-09-13'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on clients (cost=0.00..43.90 rows=11 width=8) Filter: ((modified_date >= '2016-06-13 00:00:00'::timestamp without time zone) AND (modified_date <= '2016-09-13 00:00:00'::timestamp without time zone)) (2 rows) It is not good to use BETWEEN with timestamps (not dates) because in your example only one exact value (exact to milliseconds) from the '2016-09-13' will be returned. 2016-09-12 23:59:59.999998 (yes) 2016-09-12 23:59:59.999999 (yes) 2016-09-13 00:00:00.000000 (yes) <<< the only value from this date 2016-09-13 00:00:00.000001 (no) 2016-09-13 00:00:00.000002 (no) etc. Note that even if you rewrite as "modified_date BETWEEN '2016-06-13'::date AND '2016-09-13'::date" you still get the same result because less accuracy type is converting to a type with bigger accuracy, i.e. to timestamp, not to date. When you work with timestamps the best way is to use direct "min_value <= column and column < max_value" (with open upper bound) rather than "between" statement. > and > > >> select >> modified_date, >> from >> clients >> WHERE >> modified_date >='2016-06-13' AND modified_date < '2016-09-13' > > > > But it didn't work... it returns 0 rows.... but there are rows to be shown: > > > select modified_date from clients ORDER BY modified_date ASC > > > modified_date >> ------------------- >> 2015-07-11 17:23:40 ^^^^^^^^ it is 2015 year, more than 1 year ago vvvvvv because expression is rewritten as "modified_date <= 2016-09-13 00:00:00", less than your values >> 2016-09-13 20:00:51 >> 2016-09-13 20:00:51 >> 2016-09-13 20:00:51 >> 2016-09-13 20:00:51 > > What am I doing wrong? > Cheers > Patrick -- Best regards, Vitaly Burovoy -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
