Hi Barrett, The dc.description.provenance field is automatically filled in by dspace, not by your catalogers or item submitters. You could safely rely on this field to know the bitstream submit date.
Cheers, Allen Lam. HKU Hub Administrator, http://hub.hku.hk Vanessa Barrett wrote: > Thanks to Allen and George for their suggestions. > > Unfortunately it does not solve my problem and I am beginning to suspect I > want to report something that it simply not possible in DSpace. > > George's suggestion helps if I want to report on the date an item was > accessioned and Allen's helps only if every time someone added a bitstream > they manually updated the dc.provenance element. > > My situation is that we have a large number of metadata only records loaded > directly from our research data system and we are now retrospectively adding > full text where we are able. So the item's date of accession could be late > last year and then in Jan or Feb this year we add the full text bitstream. > So I want to track and report on adding the bitstream only, not the item. > In some cases we could have items that were added 18 months to two years ago > as metadata only and we are now able to obtain an appropriate full text > version. > > How do I distinguish between date item added and date bitstream was added? > > Surely there is a date/timestamp for this? > > And don't suggest that I use item.last_modified as this is updated anytime > you hit the Update button whether you have changed anything or not! > > Cheers, > > Vanessa Barrett > Digital Services Librarian > The University of Adelaide, AUSTRALIA 5005 > Ph : +61 8 8303 4625 > e-mail: [email protected] > > CRICOS Provider Number 00123M > ----------------------------------------------------------- > IMPORTANT: This message may contain confidential or legally privileged > information. If you think it was sent to you by mistake, please delete all > copies and advise the sender. For the purposes of the SPAM Act 2003, this > email is authorised by The University of Adelaide. > Think green: read on the screen. > > > -----Original Message----- > From: Allen Lam [mailto:[email protected]] > Sent: Tuesday, 23 March 2010 5:21 PM > To: [email protected] > Subject: Re: [Dspace-general] How to report on date added for bitstreams > > Hi Barrett, > > I noticed a potential err in your given sql. Should the last line be > ... and item.item_id = handle.item_id ? > > OK, back to your question. The bitstream submit date is written in > metadatavalue. > > Try this one: > > SELECT bitstream.bitstream_id, bitstream.name, bitstream.source, > handle.handle, metadatavalue.text_value > FROM bitstream, handle, bundle, item2bundle, item, metadatavalue > WHERE bitstream.bitstream_id = bundle.primary_bitstream_id > and bundle.bundle_id =item2bundle.bundle_id > and item2bundle.item_id = item.item_id > and item.item_id = handle.resource_id > and item.item_id = metadatavalue.item_id > and metadatavalue.metadata_field_id=XXX; > > The XXX is an id you can find out from your metadatafieldregistry table > where dc.description.provenance belongs. > > Add more conditions to the sql to filter out unwanted rows. > > Cheers, > Allen Lam. > HKU Hub Administrator, http://hub.hku.hk > > > > Vanessa Barrett wrote: > >> Can anyone help with a fairly basic SQL statement to report on the >> date bitstreams were added to the database? >> >> The following is the statement I am using which works fine and gives >> me all bitstreams in the database >> >> SELECT bitstream.bitstream_id, bitstream.name, bitstream.source, >> handle.handle >> >> FROM bitstream, handle, bundle, item2bundle, item >> >> WHERE bitstream.bitstream_id = bundle.primary_bitstream_id >> >> and bundle.bundle_id =item2bundle.bundle_id >> >> and item2bundle.item_id = item.item_id >> >> and item.item_id = handle.handle_id >> >> However I can’t seem to find anything in the db_schema that would >> appear to be the date a bitstream was added to the database. I did try >> >> AND itemsbydateaccessioned.date_accessioned > ? >> >> but this table seems to no longer exist and anyway it would be the >> date the item was added not the date the bitstream was added. >> >> I am using DSpace 1.5.2. >> >> Cheers, >> >> Vanessa Barrett >> Digital Services Librarian >> The University of Adelaide, AUSTRALIA 5005 >> Ph : +61 8 8303 4625 >> e-mail: [email protected] >> >> CRICOS Provider Number 00123M >> ----------------------------------------------------------- >> IMPORTANT: This message may contain confidential or legally privileged >> information. If you think it was sent to you by mistake, please delete >> all copies and advise the sender. For the purposes of the SPAM Act >> 2003, this email is authorised by The University of Adelaide. >> >> Think green: read on the screen. >> >> ------------------------------------------------------------------------ >> >> >> > ---------------------------------------------------------------------------- > -- > >> Download Intel® Parallel Studio Eval >> Try the new software tools for yourself. Speed compiling, find bugs >> proactively, and fine-tune applications for parallel performance. >> See why Intel Parallel Studio got high marks during beta. >> http://p.sf.net/sfu/intel-sw-dev >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> Dspace-general mailing list >> [email protected] >> https://lists.sourceforge.net/lists/listinfo/dspace-general >> >> > > > ---------------------------------------------------------------------------- > -- > Download Intel® Parallel Studio Eval > Try the new software tools for yourself. Speed compiling, find bugs > proactively, and fine-tune applications for parallel performance. > See why Intel Parallel Studio got high marks during beta. > http://p.sf.net/sfu/intel-sw-dev > _______________________________________________ > Dspace-general mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/dspace-general > > > ------------------------------------------------------------------------------ > Download Intel® Parallel Studio Eval > Try the new software tools for yourself. Speed compiling, find bugs > proactively, and fine-tune applications for parallel performance. > See why Intel Parallel Studio got high marks during beta. > http://p.sf.net/sfu/intel-sw-dev > _______________________________________________ > Dspace-general mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/dspace-general > ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Dspace-general mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-general
