Обсуждение: select date between - PostgreSQL 9.5

On 09/13/2016 05:20 PM, Patrick B 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'
>
>
> 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
>     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?

test=> select '2016-09-13'::timestamp;


       timestamp


---------------------


  2016-09-13 00:00:00


So either:

test=> select '2016-09-13 20:00:51'::date between '09/11/2016' and
'09/13/2016';
  ?column?
----------
  t
(1 row)


or

test=> select '2016-09-13 20:00:51' between '09/11/2016'::timestamp and
'09/14/2016'::timestamp;
  ?column?
----------
  t



> Cheers
> Patrick


--
Adrian Klaver
adrian.klaver@aklaver.com

On 9/13/16, Patrick B <patrickbakerbr@gmail.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

On 14 September 2016 at 12:20, Patrick B <patrickbakerbr@gmail.com> wrote:
> 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'
>

Going by my clock here 2016-06-13 was just over 3 months ago, not 4.


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

None of those dates are between your specified date range. If you want
to include all of 2016-09-13 timestamps, then you'd better do <
'2016-09-14' since < '2016-09-13' will only cover timestamps on the
12th or before.


--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services