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.klaver@aklaver.com