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:
- Install the module
- Update the “value” table and set one of the properties (e.g. property_id=7) to have a “type” of “numeric:timestamp”. This will only work properly if the dates are valid YYYY-MM-DD format. As above, I suggest cloning the field into a new property, rather than working directly with your existing date field.
- Insert new records into the numeric_data_types_timestamp table that have the “negative UNIX time” as calculated by the SQL query.