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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2016-09-14 13:17 GMT+12:00 David Rowley <david.rowley@2ndquadrant.com>:

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

Thanks guys...

I've used < and >

not sure why wasn't working before :(

Thanks!
Patrick

On Wed, Sep 14, 2016 at 4:49 AM, Patrick B <patrickbakerbr@gmail.com> wrote:

2016-09-14 13:17 GMT+12:00 David Rowley <david.rowley@2ndquadrant.com>:

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

Thanks guys...

I've used < and >

not sure why wasn't working before :(

Thanks!
Patrick

It didn't work before because, as excellently pointed out by Vitaly
Burovoy, because

modified_date BETWEEN '2016-06-13' AND '2016-09-13'

is evaluated as

modified_date >= '2016-06-13 00:00:00' AND modified_date <= '2016-09-13
00:00:00'

None of your timestamps falls in that range. '2016-09-13 20:00:51' is 20
hours and 51 seconds after the end of this range, and '2015-07-11 17:23:40'
is more than a year before it.

Similar logic applies to modified_date >= '2016-06-13 00:00:00' AND
modified_date
< '2016-09-13 00:00:00'

Now, the reason it is working for you now, is probably because you're in a
timezone where it is already 2016-09-14, and your WHERE clause now reads:

modified_date >= '2016-06-14 00:00:00' AND modified_date < '2016-09-14
00:00:00'

with the effect that the timestamp '2016-09-13 20:00:51' now falls within
the range of your new WHERE clause.

At least, that's my suspicion.

Kind regards,
Na-iem Dollie