Last modified: 2012-08-04 20:48:38 UTC
When querying the API with the parameters:
as given in the example, the following is output:
XML Parsing Error: junk after document element
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:
<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
Error: 1 ERROR: invalid input syntax for type timestamp with time zone: "20071215162455"
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:
SELECT TIMESTAMP '20071215T162455';
SELECT TIMESTAMP( '20071215T162455' ) ;
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]
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.