Re: [GENERAL] select date between - PostgreSQL 9.5

[Adrian Klaver]

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.kla...@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