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 · Accepted 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

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

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

Examples related to sql

Examples related to postgresql

Examples related to datetime