CODEWITHSUNDEEP
postgresql
ndscubed
ndscubed

Reputation: 65

How to get only those that satisify date-only part of timestamp, not time

The problem is timestamps are like Year-Month-Day Hour:Minute:Second and my WHERE condition needs to ONLY care for the year-month-day portion.

I have tried conditioning for DATE(). I have tried truncate() and have also just simply tried only WHERE created_on = '2019-01-01' and have not have any results show up.

My table columns are:

id|event_status|created_on

created_on being the timestamp field.

[57014] ERROR: canceling statement due to statement timeout

and no results showing up at all.

Upvotes: 0

Views: 36

Answers (2)

user330315
user330315

Reputation:

You need to cast the timestamp to a date:

WHERE created_on::date = date '2019-01-01'`

However that will not be able to use an index on created_on which might be the reason for the timeout.

Another way of writing this that can make use of an index on created_on is to use a range condition:

WHERE created_on >= date '2019-01-01' 
  and created_on < date '2019-01-02'

Upvotes: 2

S-Man
S-Man

Reputation: 23666

You can cast a timestamp into a date. This truncates the time part

WHERE created_on::date = '2019-01-01'::date

Upvotes: 0

Related Questions