CODEWITHSUNDEEP
sqlpostgresqldatedatetimetimestamp
Ostap Brehin
Ostap Brehin

Reputation: 4002

PostgreSQL - where datetime <= YEAR-MONTH-DAY, ignore time

date column - datetime type.

My query is:

select * from car_db.car_parts where date::text LIKE '2018-07-06%'

How i select where date <= 'YEAR-MONTH-DAY', and ignore time?

I will be grateful...

Upvotes: 3

Views: 6014

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

First, Postgres doesn't offer a datetime type. The type is called timestamp. Also, Postgres has the very convenient function date_trunc(), so you can use that:

select *
from car_db.car_parts
where date_trunc('day', date) = '2018-07-06'::date;

However, this method -- or any method with a functional call or type conversion -- can affect index usage. I strongly recommend:

where date >= '2018-07-06'::date and
      date < '2018-07-07'::date

Upvotes: 6

Ankur Patel
Ankur Patel

Reputation: 1433

Try the following. Also, you should not name your column as date.

select * from car_db.car_parts where cast("date" as date) < '2018-07-06'

Upvotes: 3

Related Questions