Last modified: 2012-08-04 20:48:38 UTC
When querying the API with the parameters: action=feedwatchlist as given in the example, the following is output: === XML Parsing Error: junk after document element Location: http://www.thedarkcitadel.com/w/api.php?action=feedwatchlist Line Number 2, Column 1:<b>Warning</b>: pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR: invalid input syntax for type timestamp with time zone: "20071215162455" in <b>/home/wiki/includes/DatabasePostgres.php</b> on line <b>531</b><br /> === Viewing the raw output of the page will include this snippet: === <item> <title>Error (internal_api_error)</title> <link></link> <description>A database error has occurred Query: SELECT rc_cur_id,rc_this_oldid,rc_namespace,rc_title,rc_timestamp,rc_user,rc_user_text,rc_comment FROM watchlist,page,recentchanges WHERE (wl_namespace = rc_namespace) AND (wl_title = rc_title) AND (rc_cur_id = page_id) AND wl_user = '1' AND rc_deleted = '0' AND (rc_timestamp>='20071215162455') AND (rc_this_oldid=page_latest) ORDER BY rc_timestamp DESC LIMIT 51 Function: ApiQueryWatchlist::run Error: 1 ERROR: invalid input syntax for type timestamp with time zone: "20071215162455" </description> </item> === Looking at that query it looks like we have yyyymmddhhmmss but no timezone. The RC table is defined as timestamp *with* timezone.
Actually I stand corrected, I think the only problem is there needs to be a space between the date and the time.
Actually a T, to conform to ISO8601 so it should work with both pgsql and mysql: PG: ==== SELECT TIMESTAMP '20071215T162455'; timestamp 2007-12-15 16:24:55 ==== MySQL: ==== SELECT TIMESTAMP( '20071215T162455' ) ; TIMESTAMP('20071215T162455') 2007-12-15 16:24:55 ====
Ok my last post till a dev looks at it. Looks like MySQL stores it as a BINARY/VARBINARY O_o, so the easy fix I was envisioning isn't going to be quite so easy, so unfortunately no patch from me :-(
I think the problem lies on line 63 of includes/ApiFeedWatchlist.php: === $endTime = wfTimestamp(TS_MW, time() - intval($params['hours'] * 60 * 60)); === Unfortunately TS_MW formatted timestamps are incompatable with Postgres, would need either TS_POSTGRES or TS_ISO_8601.
(In reply to comment #4) > I think the problem lies on line 63 of includes/ApiFeedWatchlist.php: > > === > $endTime = wfTimestamp(TS_MW, time() - intval($params['hours'] * 60 * 60)); > === > > Unfortunately TS_MW formatted timestamps are incompatable with Postgres, would > need either TS_POSTGRES or TS_ISO_8601. > Unfortunately, we store timestamps as a VARCHAR(14) in MySQL. Either the Postgres scheme should be modified to use VARCHAR(14) timestamps as well, or wfTimestamp() should provide a transparent way to convert a timestamp to whatever is needed (TS_MW for MySQL, TS_POSTGRES for Postgres).
Created attachment 4567 [details] Proposed patch $wgDBtype? I quickly hacked this patch up with no regard for how efficient it would actually be. Feel free to criticize and/or suggest a better method. :)
Done in r30030.
