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

User · Answer

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';

User · Answer

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

[sql] Postgresql - select something where date = "01/01/11"

Examples related to sql

Examples related to postgresql

Examples related to datetime