CODEWITHSUNDEEP
databasepostgresql
pepersview
pepersview

Reputation: 35

Error using to_char // to_timestamp

I have a database in PostgreSQL and I'm developing an application in PHP using this database. The problem is that when I execute the following query I get a nice result in phpPgAdmin but in my PHP application I get an error.

The query: 

SELECT t.t_name, t.t_firstname
   FROM teachers AS t
   WHERE t.id_teacher IN    ( 
      SELECT id_teacher FROM teacher_course AS tcourse
          JOIN course_timetable AS coursetime
          ON tcourse.course = coursetime.course
          AND to_char(to_timestamp('2010-4-12', 'YYYY-MM-DD'),'FMD') = (coursetime.day +1)
      )
   AND t.id_teacher NOT IN (
      SELECT id_teacher FROM teachers_fill WHERE date = '2010-4-12'
   )
ORDER BY t.t_name ASC

And this is the error in PHP

operator does not exist: text = integer (to_timestamp('', 'YYYY-MM-DD'),'FMD') =
 (courset... ^ HINT: No operator matches the given name and argument type(s). 
You might need to add explicit type casts.

The purpose to solve this error is to use the ORIGINAL query in php with :

$date = "2010"."-".$selected_month."-".$selected_day;

SELECT ...
  AND to_char(to_timestamp('$date', 'YYYY-MM-DD'),'FMD') = (coursetime.day +1)
)
AND t.id_teacher NOT IN (
      SELECT id_teacher FROM teachers_fill WHERE date = '$date'
)

Upvotes: 0

Views: 1595

Answers (1)

leonbloy
leonbloy

Reputation: 75926

The error message seems quite clear to me. You are mixing strings and numbers. More precisely, you are converting a string ('2010-4-12') to a timestamp, then to a string, then comparing to an int. This is a type mess, and postgresql is quite strict with typing (for good reasons). What are you trying to do here ?

to_char(to_timestamp('2010-4-12', 'YYYY-MM-DD'),'FMD') = (coursetime.day +1))

Further, you should use a TIMESTAMP, just a DATE.

If (I'm not sure) you are tring to compare the day of week from a date formated as 'YYYY-MM-DD' to a given value (as an integer), you should better use date_part. For example (not tested):

date_part('dow' , to_date('2010-4-12', 'YYYY-MM-DD') ) = coursetime.day + 1

Upvotes: 1

Related Questions