pgsql named time zone detection is incorrect

The actual query is incorrect too. I'm attempting to come up with a proper SQL query to do what is needed. Postgres seems to always want to convert everything to its current timezone, so converting between two random timezones in one SQL query is problematic, unless I'm totally missing the boat which is quite possible.

The correct SQL for PostgreSQL is:

SELECT '2008-02-15 12:00:00 UTC' AT TIME ZONE 'US/Central';

You can also use PostgreSQL built-in funtion:
SELECT timezone('US/Central', '2008-02-15 12:00:00 UTC')

Both queries return:
2008-02-15 06:00:00

Patch:

Index: date_api_sql.inc
===================================================================
--- date_api_sql.inc	(révision 89)
+++ date_api_sql.inc	(copie de travail)
@@ -76,7 +76,7 @@
           }
           break;
         case 'pgsql':
-          $test = "TIMESTAMP WITH TIME ZONE '2008-02-15 12:00:00' AT TIME ZONE 'US/Central'";
+          $test = db_result(db_query("SELECT timezone('US/Central', '2008-02-15 12:00:00 UTC')"));
           if ($test == '2008-02-15 06:00:00') {
             $has_support = TRUE;
           }
@@ -240,9 +240,7 @@
         case 'mysqli':
           return "CONVERT_TZ($field, $db_zone, $localzone)";
         case 'pgsql':
-          // WITH TIME ZONE assumes the date is using the system
-          // timezone, which should have been set to UTC.
-          return "TIMESTAMP WITH TIME ZONE $field AT TIME ZONE $localzone";
+          return "timezone('$localzone', '$field $db_zone')";
       }
     }
   }
@@ -730,4 +728,4 @@
       return array($min_date, $max_date);
     }
   }
-}
\ No newline at end of file
+}

I tested only the SQL.

Hope this helps.
JMP

Status: patch needs review.

Thanks for that SQL, it is exactly what was needed.

I found a potential problem involving embedded quotes in your change to sql_tz(), but in the interests of tackling one thing per issue I think we should just address the problem in db_tz_support() right now. Patch attached, with a very minor formatting tweak. I'll open another issue for the sql_tz() problem.

Edit: and I decided not to pending a little more research.

I committed the first part, the db_tz_support(). I'm unclear about the status of the second fix. Also, if it's correct, is it OK to capitalize TIMEZONE for consistency in the code?

jmpoure, can you elaborate on the change to timezone? It seems to me like the original should be fine but my experience with Postgres is not terribly extensive.

No response on this. If the remaining issue is just another valid way to structure the postgres code, there's no reason to change anything.