Sorting datetime field

[isometriq]

First of all, this work is one more godsend from the community. Thx

I get a weird behavior when sorting datetime fields ..have a look (below, just showing the field value, in the order it appears in the json response)

/api/users?_sort=created_at

"created_at": "2016-07-29 02:06:34"
"created_at": "2016-07-28 02:47:30"
"created_at": "2016-07-28 06:57:49"
"created_at": "2016-07-28 18:32:33"

/api/users?_sort=-created_at

"created_at": "2016-07-28 18:32:33"
"created_at": "2016-07-28 06:57:49"
"created_at": "2016-07-28 02:47:30"
"created_at": "2016-07-29 02:10:03"

So by looking at this, it seems that the sort is done on the date ..and the time, but the time sorting is reversed.

Below, my simple controller..

class UsersController extends RestfulController {
    public function index(Request $request) {
        return ApiHandler::parseMultiple(User::query(), array('name','email'))->getResponse();
    }
}

[isometriq]

Also, by using "_sort=created_at", I would expect the dates to be ascending. In my example, it is reversed.

[marcelgwerder]

I really only translate created_at to ORDER BY created_at ASC and -created_at to ORDER BY created_at DESC. From there the sorting depends on your system and the datatypes used. On my MySQL 5.6 instance this works as expected for both datetime and timestamp fields.

[isometriq]

That's odd, it seems that the entry that is not in the right order has actually a 0000-00-00 00:00:00 value, but it gets the current date and time (not exactly the same perhaps because of the locale).

By looking at this I would say that all is fine and that the problem is the date/time being incorrectly interpreted. Maybe it's my MariaDB 10.1.13 installation ..I've heard about potential problem with MySQL 5.7 and zero-timestamps.

[isometriq]

Ok found the culprit i think, in my base Model class...

protected function asDateTime($value)
{
    if ($value == "0000-00-00 00:00:00") {
        return \Carbon\Carbon::create();
    }
    return parent::asDateTime($value); // TODO: Change the autogenerated stub
}

public function getCreatedAtAttribute($value) {
    return $value == "-0001-11-30 00:00:00" ? "0000-00-00 00:00:00" : $value;
}

public function getUpdatedAtAttribute($value) {
    return $value == "-0001-11-30 00:00:00" ? "0000-00-00 00:00:00" : $value;
}

By commenting, the value becomes

"created_at": "-0001-11-30 00:00:00",
"updated_at": "-0001-11-30 00:00:00"

and is now sorted correctly... so all his good!