Re: 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(dot)klaver(at)aklaver(dot)com