Re: select date between - PostgreSQL 9.5

On 9/13/16, Patrick B <patrickbakerbr(at)gmail(dot)com> 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