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]<mailto:[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++
------------------------------------------------------------------------------
All of the data generated in your IT infrastructure is seriously valuable.
Why? It contains a definitive record of application performance, security
threats, fraudulent activity, and more. Splunk takes this data and makes
sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-d2dcopy2
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
