to_char() issue in PostgreSQL

Question

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

Hang on, did you say that `effective_date` has data type `integer`? Not `date`? Not `timestamp`? Was that a mistake in your question? If it's an integer how do you expect to get a date out of it? Is it an epoch date (seconds since a certain date)? — Craig Ringer, Aug 27 '12 at 05:46
Well, then what would `to_char` do? Did you try it in `psql`? Let's see: `select to_char( extract(epoch from current_timestamp), 'Mon')` just outputs the string `Mon`, which will never equal the string `Jul`. So there's your problem, you were calling the integer version of `to_char` and expecting it to just know you wanted to treat the integer as a date. See `\df to_char` in psql to see all the `to_char` variants. Only the `timestamp` ones work with date formatting as per the [documentation](http://www.postgresql.org/docs/current/static/functions-formatting.html) — Craig Ringer, Aug 27 '12 at 05:58
@CraigRinger Thank you it worked. You explained it pretty well. :) — Les_Salantes, Aug 27 '12 at 06:12

Craig Ringer · Accepted Answer · 2012-08-27T06:02:05.580

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.

to_char() issue in PostgreSQL

1 Answers1