Postgresql - select something where date = "01/01/11"

Question

I have a datetime field in my Postgresql, named "dt". I'd like to do something like

SELECT * FROM myTable WHERE extract (date from dt) = '01/01/11' 

What is the right syntax to do that?

Thanks!

Answer 1

I think you want to cast your dt to a date and fix the format of your date literal:

SELECT * FROM table WHERE dt::date = '2011-01-01' -- This should be ISO-8601 format, YYYY-MM-DD 

Or the standard version:

SELECT * FROM table WHERE CAST(dt AS DATE) = '2011-01-01' -- This should be ISO-8601 format, YYYY-MM-DD 

The extract function doesn't understand "date" and it returns a number.

Answer 2

With PostgreSQL there are a number of date/time functions available, see here.

In your example, you could use:

SELECT * FROM myTable WHERE date_trunc('day', dt) = 'YYYY-MM-DD'; 

If you are running this query regularly, it is possible to create an index using the date_trunc function as well:

CREATE INDEX date_trunc_dt_idx ON myTable ( date_trunc('day', dt) ); 

One advantage of this is there is some more flexibility with timezones if required, for example:

CREATE INDEX date_trunc_dt_idx ON myTable ( date_trunc('day', dt at time zone 'Australia/Sydney') ); SELECT * FROM myTable WHERE date_trunc('day', dt at time zone 'Australia/Sydney') = 'YYYY-MM-DD';