[Israel.pm] Sorting By MySQL DATETIME Fields?

[Shlomi Fish]

On Wednesday 16 June 2004 12:14, Yuval Yaari wrote:
> Hi,
>
> I had a query that used JOIN and took too much time (4 seconds).
> I figured Perl could handle it much faster, and so it did.
>
> I am using 2 queries now.
> At the end, I have an array of $sth->fetchrow_hashref() which I would
> like to sort by date (since it's in 2 queries, I can't ORDER BY Date).
>

Note that if you perform ORDER BY on the two queries separately, then you can 
then merge both arrays by using the "merge" algorithm:

http://www.maths.abdn.ac.uk/~igc/tch/mx4002/notes/node40.html

It is possible there is a fast (hard-coded) implementation of this algorithm 
in CPAN, which accepts a comparison routine similar to sort's.

The overall complexity of both choices is the same ( O(Nlog(N)) ).

As for your question, you should probably use the Schwartzian transform (IIRC 
how it's called) to map the DATETIME field to a scalar that is easily 
sortable (unless it's already easily sortable), and then sort the items based 
on this scalar. This will look something like this:

my @items_with_time_stamp = map { +{ 'i' => $_, 't' => get_time_stamp($_) } } 
(@items1, @items2);
my @sorted_items_with_time_stamp = sort { $a->{t} cmp $b->{t} } 
@items_with_time_stamp; # Or <=> if appropriate.
my @sorted_items = map { $_->{i} } @sorted_items_with_time_stamp;

You can do this in one command, but I wrote it in several statements to make 
it clearer. (note that I did not test it.

Regards,

	Shlomi Fish

-- 

---------------------------------------------------------------------
Shlomi Fish      shlomif at iglu.org.il
Homepage:        http://shlomif.il.eu.org/

Quidquid latine dictum sit, altum viditur.
        [Whatever is said in Latin sounds profound.]