Select all records passing date as January 20

I would like to be able to query records from my table by passing in January 20, January 21 as an example

I have found that the EXTRACT method can be used for cases such as this, but how could i use it when supplying a month and year. Would I need to format the string I'm providing into a date?

SELECT * FROM myTable WHERE EXTRACT(MONTH FROM kick_off) = 'January 20';

Thanks

CodePudding user response：

You need to use two extract calls and as extract() returns a number, you can't compare it with January:

SELECT * 
FROM myTable 
WHERE EXTRACT(MONTH FROM kick_off) = 1
  and EXTRACT(year FROM kick_off) = 2020;

If you have an index on kick_off the above query wouldn't use that index. It's usually more efficient to use a range query:

SELECT * 
FROM myTable 
WHERE kick_off >= date '2020-01-01'
  and kick_off  < date '2020-02-01'

CodePudding user response：

You can do that by using to_char on the kick_off column, but it's really bad design:

SELECT *
FROM myTable
WHERE to_char(kick_off, 'FMMonth YY') = 'January 20'

However, as @a_horse_with_no_name_correctly pointed out, it's most likely a bad idea because such a query will not use an index on the kick_off column, so a date range filter is preferable as it will perform much better in the presence of an index.

Also, the behaviour of the 'FMMonth' format is locale-dependent (relevant documentation here: https://www.postgresql.org/docs/current/functions-formatting.html).