Hi! Is there any way to set up a search to search for results between a range of dates? So, say, all items between 1935 and 1960?
Also, if we have a folder that covers a date range (dcterms:date 1935/1960), is there any way to make it appear if we do a search for a date between that range - say, 1940? Right now it only appears if we search for either 1935 or 1960.
Thanks!
There is currently no way to do what you describe. We recognize the importance of searching dates and are looking into ways to make it possible.
A related issue: “Add numerical comparisons query type”
As a workaround, many installations use an external indexing, i.e. Solr (see Solr module).
The Numeric Data Types module allows users to search a range of dates, among other numeric-based searches.
Oh wow, I’m going to go play with this now! Thank you so much!!
This is awesome, but I have a few questions:
First, I’ve noticed that any dates that I already have attached to my items remain as text fields even if I change the date field to Numeric:Timestamp in its related resource template, and as such don’t show up in the new date search. I can see how this would be great for new items, but I don’t know what to do about the close to 7,000 items I already have catalogued. Would I have to go into each one individually and re-enter their dates?
Second, my items have either a specific date (1947-12-31), month (1947-12) year (1947), or range (1947/1953 or 1947-09/1947-12 or 1947-12-15/1947-12-17). I was very careful to keep everything ISO 8601 compliant. Looking at this module, it seems to only allow a single date (or month or year) to be used, but not a range. Is that right? Ideally, I’d like for a date search for 1950 to pull up a folder with a date range from 1947 to 1953.
Thank you so, so much. This is a great step forward!
Morgannis
Please refer to the module documentation if you haven’t already: https://omeka.org/s/docs/user-manual/modules/numericdatatypes/
Yes, you will have to change each one individually. The module does not assume existing date strings are in a valid format. Because of this, only new values of a numeric:timestamp property are parsed as a timestamp.
The module does not implement ISO 8601’s time interval format. Instead you search a range by selecting a “Date comes after” and a “Date comes before” against your respective time point properties. You can use the same property (e.g. Date) for the start and end time point.
Gotcha. Still awesome, thanks!
Morgannis
Oh, one more question - if I use the CSV import module to bulk import and put a compliant date (not a range) in the date field and declare a resource template that uses the numeric:timestamp definition for the date field, will it work? Thanks!
The CSV Import module is only capable of importing the native data types (literal, uri, and resource) even when declaring a resource template that uses a data type introduced by a module.
I was wondering if you came up with any creative solution to this issue, i.e., importing dates as strings and adjusting them to the timestamp data types.
Here’s how I implemented the Numeric Data Types module and applied it to 154,000 existing records with historical dates at the Thomas Edison Papers.
Note that this involves going directly into the SQL database and running queries - do this at your own risk / make sure to get IT support if you are going to try it.
Although I didn’t do these steps in the order I’m writing, I think this is the best way. After installing Numeric Data Types module…
First, you’ll have an existing date field in your item records, probably in dcterms:date. Or, you’ll run a CSV import that contains dates for new items or appends dates to existing items. In my case, all dates were before 1970-01-01 which is the "beginning of UNIX time ", so we are talking about how to calculate “negative time” here.
MySQL UNIX_TIMESTAMP() returns a Unix timestamp in seconds since ‘1970-01-01 00:00:00’ UTC as an unsigned integer if no arguments are passed with UNIX_TIMESTAMP().
To begin, I created a duplicate of my dcterms:date field in dcterms:temporal via this SQL query. There is the Bulk Edit module by Daniel Berthereau, but I’m not sure it can populate a new field in this way.
INSERT INTO value (resource_id, property_id, type, value, is_public)
SELECT resource_id, '20', 'numeric:timestamp', value, '1'
FROM value WHERE property_id=7
In this case, property_id =7 is dcterms:date, and property_id = 20 is dcterms:temporal. Notice that I am setting the “type” of the value as “numeric:timestamp”. This is what the Numeric Data Types module allows (among other things). Normally, your values are of type “literal”.
The main reason you want to create a clone of your date field is because all of your dates need to be valid YYYY-MM-DD format. It’s perfectly fine if they are stored in text format, and they don’t need the normal timestamp “00:00:00” at the end. But in our records, 10% (about 15,000) had partial dates such as 1889-01-00 (dated January 1889) or 1910-00-00 (dated 1910). These are not valid MySQL dates when it comes to a timestamp data type, so my solution was to convert them to “01” so that they would sort correctly. Now we have the original date displayed, e.g. “1910-00-00” but we can also search (and sort) by the timestamp. Queries looked like this:
UPDATE value SET value = REPLACE(value, "-00-00", "-01-01") WHERE property_id=20 AND value LIKE "%-00-00"
Once all of your dates are correct, you need to convert them to “negative UNIX time”. After referencing the articles below, I was able to write this query which did the trick.
SELECT resource_id, value,
TIMESTAMPDIFF(second,FROM_UNIXTIME(0),STR_TO_DATE(value,'%Y-%m-%d')) as `timestamp`
FROM value WHERE property_id=20
You can then populate the SQL table numeric_data_types_timestamp with the results of that query.
INSERT INTO numeric_data_types_timestamp (resource_id, property_id, value)
SELECT resource_id, '20', TIMESTAMPDIFF(second,FROM_UNIXTIME(0),STR_TO_DATE(value,'%Y-%m-%d')) as `timestamp`
FROM value WHERE property_id=20
This table is the reason why “only new values of a numeric:timestamp property are parsed as a timestamp”. Each value has to be registered with the module in order to enable searching, and due to the complexity of potential date issues/errors/values, the module authors did not attempt to build a way to convert/import existing values.
That’s it! You should then be able to utilize the extra fields that the module adds to the advanced search page (“Date comes after”, “Date comes before”) to search for records.
You basically need three things if you are going to adapt this to your own installation:
A couple things just because this is a fairly old thread so it has some older outdated information in the earlier posts:
This topic was automatically closed 250 days after the last reply. New replies are no longer allowed.