Jose, Perfect, that was a big help. I'm not very familiar with DSpace's schema, but I was able to hack together some SQL to find all of our offending items:
select text_value from metadatavalue where metadata_field_id=25 and item_id in (select item_id from metadatavalue where metadata_field_id=11 and text_value = '25/07/2005' or text_value= '13/02/2008'); Where "25/07/2005" and "13/02/2008" were commonly-occurring, incorrect values for the dc.date.accessioned field. I saw them frequently in the postgresql logs as causing the dspace-stat scripts to fail. The above SQL spits out a list of handle URLs, where our editors will be able to correct the data. For anyone who's also having this problem and trying to follow my SQL, "25" is the metadata_field_id for URI, and 11 is for the date.accessioned. Adios! Alan On Fri, Oct 7, 2011 at 6:40 PM, Blanco, Jose <[email protected]> wrote: > Alan, > > > > I’d query metadtafieldregistry to find out the metadata_field_id for > date.accissioned: > > > > dspace-prod=> select metadata_field_id from metadatafieldregistry where > element='date' and qualifier='accessioned'; > > metadata_field_id > > ------------------- > > 11 > > > > ( so for us it happens to by 11 ) > > Then, once you have that number, do this query without specifying the > datestyle. You might have to play around a bit with the query, maybe not > include the text_value and see all the values to see if any one looks > suspicious, or use the wild card %. > > > > dspace-prod=> select text_value from metadatavalue where metadata_field_id > =11 and text_value > '2011-10-01' AND text_value < '2011-10-31'; > > > > > > Hope this helps, > > Jose > > > > > > From: Alan Orth [mailto:[email protected]] > Sent: Friday, October 07, 2011 11:16 AM > To: [email protected] > Subject: Re: [Dspace-tech] Stats on 1.5.1, ERROR: invalid input syntax for > type timestamp: "2007" > > > > Hi, > > We just noticed that our stats haven't run for the past month, so I went to > run `/home/dspace/bin/dspace stat-monthly` manually. I'm getting a similar > problem (but on DSpace 1.7.2 with Postgres 8.3): > > 2011-10-07 03:08:22 PDT ERROR: date/time field value out of range: > "25/07/2005" > 2011-10-07 03:08:22 PDT HINT: Perhaps you need a different "datestyle" > setting. > 2011-10-07 03:08:22 PDT STATEMENT: SELECT COUNT(*) AS num FROM item WHERE > in_archive = true AND withdrawn = false AND item_id IN ( SELECT item_id FROM > metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM > metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned') > AND text_value::timestamp > '2011-10-01'::timestamp AND > text_value::timestamp < '2011-10-31'::timestamp ) > > It's quite possible that a recent batch import of several thousand items had > an incorrect date format... I'm not sure how to go about finding the > offending item via SQL. > > Thanks, > > Alan > > On 09/15/2011 02:23 AM, Andrea Schweer wrote: > > Hi Evelio, > > > > On 15/09/11 01:23, Evelio Martínez wrote: > > When we invoke the following scripts > > > > [bin]# ./stat-initial > > [bin]# ./stat-monthly > > > > We have the same result > > > > Exception in thread "main" org.postgresql.util.PSQLException: ERROR: > > invalid input syntax for type timestamp: "2007" > > > > This is caused by an item for which date.accessioned is just "2007". > > > > You should be able to get the item id with something like the following > > query (just a slight modification of the one you posted): > > > > SELECT item_id FROM item WHERE in_archive = true AND withdrawn = false > > AND item_id IN (SELECT item_id FROM metadatavalue WHERE text_value = > > "2007" AND metadata_field_id = (SELECT metadata_field_id FROM > > metadatafieldregistry WHERE element = 'date' AND qualifier = > > 'accessioned')); > > > > DSpace expects the value of this field to be in the same format you got > > in your query for last_modified (eg 2008-12-15 09:08:10.66+01). > > > > Once you have fixed the date.accessioned value for this item, your > > statistics script should go through fine. > > > > cheers, > > Andrea > > > > > -- > > Alan Orth > > [email protected] > > http://alaninkenya.org > > "I have always wished for my computer to be as easy to use as my telephone; > my wish has come true because I can no longer figure out how to use my > telephone." -Bjarne Stroustrup, inventor of C++ -- Alan Orth [email protected] http://alaninkenya.org http://mjanja.co.ke "In heaven all the interesting people are missing." -Friedrich Nietzsche ------------------------------------------------------------------------------ All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 _______________________________________________ DSpace-tech mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-tech
