Select date only without the hour on postgresql

Question

I have date column on my postgres table as timestamp format i.e "2017-01-01 22:00:00". When I wrote in the queries

select date from table where date = '2017-01-01' it did not give me any result.

Should I always include the time information on those queries? Can I just put the yyyy-mm-dd only on my queries to search the date column?

regards

Tim Biegeleisen · Answer 1 · 2017-02-03T07:46:16.947

22

Cast to date:

select "date" from table where "date"::date = '2017-01-01'

Note that I enclosed references to the date column in double quotes, because date is a Postgres keyword. You should avoid naming your columns, tables, or schemas using keywords.

edited Feb 03 '17 at 07:46

answered Feb 03 '17 at 07:41

Tim Biegeleisen

502,043
27
286
360

thanks @Tim Biegeleisen it works now.. – reyalino Feb 05 '17 at 01:36
@reyalino Is mark correct possible? – Tim Biegeleisen Feb 05 '17 at 02:36

Mayank Pandeyz · Accepted Answer · 2019-11-22T06:31:37.110

18

Use date() function, it will extract date from datetime like:

select DATE(my_field) from my_table;

edited Nov 22 '19 at 06:31

answered Feb 03 '17 at 07:41

Mayank Pandeyz

25,704
4
40
59

score 1 · Answer 3 · answered Feb 03 '17 at 08:12

1

Try this way:

select date("Date") from yourTable

answered Feb 03 '17 at 08:12

Vijunav Vastivch

4,153
1
16
30

another choices to select the date column..many thanks!! – reyalino Feb 05 '17 at 01:37

Select date only without the hour on postgresql

3 Answers3