Postgresql - Select Something Where Date = "01/01/11"

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

Solution 1:

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

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

Or the standard version:

SELECT*FROMtableWHERECAST(dt ASDATE) ='2011-01-01'-- This should be ISO-8601 format, YYYY-MM-DD

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

Solution 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 attime zone 'Australia/Sydney') );
SELECT*FROM myTable WHERE date_trunc('day', dt attime zone 'Australia/Sydney') ='YYYY-MM-DD';