CODEWITHSUNDEEP
mysql
Allerion
Allerion

Reputation: 405

MySQL sort before select

I have a MySQL table that stores birth and death events for objects as individual rows. I'm trying to write a query that will get all "Living Objects" at a given time. The kicker is that the birth and death timestamps are stored as strings.

The system has a historical aspect and requires dates to start from 0000-00-00 00:00, which neither MySQL or UNIX can handle.

I can sort the table successfully with the following:

ORDER BY CHAR_LENGTH(start_timestamp) ASC, start_timestamp ASC

But I'm looking for a query that would incorporate the rules of this ORDER BY when comparing the strings.

My current function is the following:

global $mysqli,$db_table_prefix;
$stmt = $mysqli->prepare("SELECT object_id FROM ".$db_table_prefix."user_events WHERE start_timestamp < ? AND event_id = ?");
$stmt->bind_param("ii",$time,$birth);
$stmt->execute();
$stmt->bind_result($object_id);
while ($stmt->fetch()){
    $row[] = $object_id;
}
$stmt->close(); 

    if(!empty($row)){
        $stmt = $mysqli->prepare("SELECT object_id FROM ".$db_table_prefix."user_events WHERE start_timestamp > ? AND event_id = ? AND object_id IN (".join(",",$row).")");
        $stmt->bind_param("ii",$time,$death);
        $stmt->execute();
        $stmt->bind_result($object_id);
        while ($stmt->fetch()){
            $row2[] = array("object_id" => $object_id);
        }
        $stmt->close();
        if(!empty($row2)){
            return $row2;
        }else{
            return FALSE;
        }

The problem here is that MySQL doesn't compare the strings in the way I want it to. So a string of "1000000000"(10) is smaller than "900000000"(9).

Is there a way to make this:

WHERE start_timestamp > ?

follow the same rules as this:

ORDER BY CHAR_LENGTH(start_timestamp) ASC, start_timestamp ASC

when querying?

Upvotes: 1

Views: 138

Answers (1)

Vatev
Vatev

Reputation: 7590

You can zeropad them and then compare:

LPAD(start_timestamp,20,'0') > LPAD(another_timestamp,20,'0')

The 20 character limit is arbitrary, you should change it as appropriate.

Also it might make your life easier to store padded values in the table.

Upvotes: 1

Related Questions