sorting by date [#172306]

I was trying to get my biblio sorted in reverse chronological order.
I have mainly journal and conference publications.
Journals only get a mm/yyyy date while conferences get dd/mm/yyyy date. For unpublished stuff i only put the yyyy.
Now the default sorting looks at the string as a whole and sorts:
31/01/2007
2007
12/2007
08/04/2007
01/12/2007
which is not reverse chronological.

I want it sorted by month and then day (if the day is given).
I messed with the biblio_db_search() function and changed
$sortby = "ORDER BY b.biblio_year %s, b.biblio_date DESC, SUBSTRING(n.title,1,1) ASC, b.biblio_type ASC ";
to
$sortby = "ORDER BY b.biblio_year %s, SUBSTRING(LPAD(b.biblio_date,10,'0'),4,2) DESC ,SUBSTRING(LPAD(b.biblio_date,10,'0'),1,2) DESC, SUBSTRING(n.title,1,1) ASC, b.biblio_type ASC ";
Padding everything to 10 characters (dd/mm/yyyy, 000mm/yyyy, 000000yyyy) and then sorting by month, then by day.

I tried SUBSTRING(b.biblio_date,pos,len) with a negative pos counting from the end of the string but that doesn't work on all mysql versions.

The above works for me. I don't know about other DBs.
And its only good for dd/mm/yyy format, which seems to be the default. Maybe its possible for biblio to recognise the date format set in drupal.

Comment	File	Size	Author
#4	adaptdate_openurl.patch	1.39 KB	schuyler1d

Comments

Comment #1

schuyler1d commented 1 September 2009 at 22:25

I have a better suggestion, since:
* biblio_date is sometimes assumed to be just month
* there's no standard way to send dd/mm/yyyy out of the box
* journal info often needs dd/mm/yyyy and this field should be used over biblio_year in many places.
* ISO8601 seems like a better format, since dc:date is supposed to use it, and this string would be the best candidate to end up downstream in rdf, etc. (e.g. coins -> zotero -> export rdf). Also, it would sort correctly (without modifying sortby).

I'm working on a patch to do the following. Would it be well received?
1. make (and migrate) field to iso8601 format
2. biblio_coins sends date=$biblio_date if available instead of $biblio_year
3. biblio_date Hint is (yyyy-mm-dd)
4. import into biblio_date correctly
5. biblio_date is not assumed to be month.

Alternatively, you could migrate the field to be a database date field, but this would require more work than I'm prepared for.

Comment #2

rjerome commented 2 September 2009 at 01:57

Sounds like a good idea, I'm guessing that parsing and correctly formatting date strings from file import will be the biggest headache, but give it a go and I would be happy to include it if it all works out.

Funny you should bring this up now, as it's the second time the date handling has come up in as many days. I was trying to remember why I didn't use a date field in the database from the get go, and I think it had to do with parsing of dates imported from files.

Ron.

Comment #3

schuyler1d commented 2 September 2009 at 14:27

great! I will start work.

Looking around at other bug reports, enough people seem to want to use it as a text field, putting 'unknown,' 'no date' etc in it. These have different meanings that cannot all collapse into NULL, so there are plus sides as well. If you make a date field in the future, you might consider just adding a column rather than replacing this one. Then we could have our cake and eat it too (db redundancy be damned! :-)

Comment #4

schuyler1d commented 3 September 2009 at 19:09

Assigned:

Unassigned

» schuyler1d

Status	File	Size
new	adaptdate_openurl.patch	1.39 KB

OK, my first pass is just using the new adapt function for openURL output:
(That's #2 on my list above)
I'm also making some changes so the openURL format works better for types magazine/newspaper articles.
See my progress at:
http://github.com/ccnmtl/biblio-drupal/commits/master/

I can file a new issue on that (assuming you're interested in the patches) if you like.

Comment #5

rjerome commented 4 September 2009 at 16:43

Great, thanks! I haven't had a chance to look at it in detail yet (because I'm working on a few other issues with 6.x) but I will, keep those patches coming...

Ron.