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
– Craig Ringer, Commented Aug 27, 2012 at 5: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 — Craig Ringer
– Craig Ringer, Commented Aug 27, 2012 at 5:58
@CraigRinger Thank you it worked. You explained it pretty well. :) — Les_Salantes
– Les_Salantes, Commented Aug 27, 2012 at 6:12

Craig Ringer · Accepted Answer · 2012-08-27 06:02:05Z

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.

Collectives™ on Stack Overflow

to_char() issue in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related