From: Rob Biedenharn <Rob@...>
Date: 2007-03-20T02:10:00+09:00
Subject: Re: Sorting Dates and Times in an array


On Mar 19, 2007, at 12:25 PM, Paul wrote:

> Hi there.  I am having a bit of trouble trying to solve a particular
> sorting problem with an array of data.
>
> Please bear with me for a moment regarding the setup of this problem.
> I don't currently have any control over the input data.  I am just
> trying to format the report of this data as per the requirement.
>
> I have the following sample array data to work with:
> @date_array = [['2/22/07','1:03 pm'],['3/9/07','10:45 pm'],
> ['3/1/07','1:52 pm'],['3/13/07','2:00 pm'],['2/28/07','10:45 am'],
> ['3/5/07','4:00 pm'],['2/14/07','5:00 pm'],['3/9/07','10:18 am'],
> ['3/13/07','11:15 am']]
>
> (The actual array contains more data, but this is good enough to work
> on this problem.  BTW, date format = mm/dd/yy.)
>
> Requirement:
> 1) Sort in descending order by Date (i.e. @date_array[x][0] )
> 2) Sort in ascending order by Time (i.e. @date_array[x][1] )
>
> Elsewhere in the script, I use the following line to sort by a
> particular element in a row:
>> @fields.sort! { |a,b| b[ x ] <=> a[ x ] }     # i.e. descending sort
>
> This works quite nicely for string and numeric fields, but *not* for
> Date or Time string fields.  As an example, when I try it with the
> above data I get the following:
> ----
> puts 'Descending Sort - By Date:'
> @date_array.sort! { |a,b| b[ 0 ] <=> a[ 0 ] }
> @date_array.each_index do |row|
> 	puts "row # #{row} = " + @date_array[row].join(" : ")
> end
> ----
> Descending Sort - By Date:
> row # 0 = 3/9/07 : 10:18 am
> row # 1 = 3/9/07 : 10:45 pm
> row # 2 = 3/5/07 : 4:00 pm
> row # 3 = 3/13/07 : 2:00 pm
> row # 4 = 3/13/07 : 11:15 am
> row # 5 = 3/1/07 : 1:52 pm
> row # 6 = 2/28/07 : 10:45 am
> row # 7 = 2/22/07 : 1:03 pm
> row # 8 = 2/14/07 : 5:00 pm
> ----
>
> --> Which is a nice string sort again, but not a date sort.
>
> I know I'll have to write a method to deal with these two columns of
> data, but I'm not sure where to start.  I've tried googling solutions
> in both this discussion group and the internet but so far haven't
> turned up anything I can use.
>
> Suggestions?

 >> epoch = Time.at(0).utc
=> Thu Jan 01 00:00:00 UTC 1970
 >> @date_array.sort_by {|d,t| dp=Date.parse(d,true); tp=Time.parse 
(t,epoch); [ Date.today - dp, tp ] }.each {|d,t| puts("%8s %8s" %  
[d,t]) }
  3/13/07 11:15 am
  3/13/07  2:00 pm
   3/9/07 10:18 am
   3/9/07 10:45 pm
   3/5/07  4:00 pm
   3/1/07  1:52 pm
  2/28/07 10:45 am
  2/22/07  1:03 pm
  2/14/07  5:00 pm
=> [["3/13/07", "11:15 am"], ["3/13/07", "2:00 pm"], ["3/9/07",  
"10:18 am"], ["3/9/07", "10:45 pm"], ["3/5/07", "4:00 pm"],  
["3/1/07", "1:52 pm"], ["2/28/07", "10:45 am"], ["2/22/07", "1:03  
pm"], ["2/14/07", "5:00 pm"]]

Parse the date with 2-digit year semantics (1969-2068 from the 'true'  
arg) and parse the time based on the beginning of the epoch (really  
any date would do).  The sort is then reversed by date and forward by  
time when dates are equal.

The use of .sort_by causes the keys to be processed once and is  
better for large Arrays than .sort (and if you need .sort!, just do  
@date_array = @date_array.sort_by {...} instead).

-Rob

Rob Biedenharn		http://agileconsultingllc.com
Rob@AgileConsultingLLC.com