to_char() issue in PostgreSQL

postgresql, to-char

Solution

Use `extract(month from the_date)` instead of `to_char`. See datetime functions in the Pg docs.

With `to_char` you'll suffer from all sorts of issues with case, localisation, and more.

Assuming you meant that the data type of `effective_date` was `timestamp` or `date`, you'd write:

$query = "select    *
      from ". $this->getTable() ."
      where pay_stub_entry_name_id = 43
      AND extract(month from effective_date) = 7
      AND deleted = 0";

If it's `integer` then - assuming it's an epoch date - you have to convert it to a timestamp with `to_timestamp`, then use `extract` on it. See the `epoch` section in the documentation linked to above, eg:

$query = "select    *
      from ". $this->getTable() ."
      where pay_stub_entry_name_id = 43
      AND extract(month from to_timestamp(effective_date)) = 7
      AND deleted = 0";

The immediate cause of your problem was that you were calling `to_char(integer,text)` with an integer epoch date. Only the `timestamp` versions of `to_char` do date formatting; `Mon` isn't special for the others, so it was simply output as a literal string `Mon`. Compare:

regress=# SELECT to_char(current_timestamp, 'Mon');
 to_char 
---------
 Aug
(1 row)

regress=# select to_char( extract(epoch from current_timestamp), 'Mon');
 to_char 
---------
 Mon
(1 row)

Remember to parameterise your real-world versions of these queries to help avoid SQL injection.

Problem

I have a PostgreSQL table with a field named `effective_date` and data type is integer(epoch date). What I want to do is to select only the entries that have an effective_date of my choice (I only want to query by the month). My query is below and the problem is, it is not returning anything although the table do have many entries that match the selection criteria. ``` $query = "select * from ". $this->getTable() ." where pay_stub_entry_name_id = 43 AND to_char(effective_date, 'Mon') = 'Jul' AND deleted = 0"; ```

Original source