nosqlapache-phoenix

Sort order not being broken down

I'm trying to do a simple query where I want to order by Date, Hour, and score where the date and hour are coming from the same field (epoch_seconds). I have accomplished this so far by the following:

SELECT cast(epoch_seconds as date) as d, hour(cast(epoch_seconds as time)) as t, score as s
FROM MyTable
order by cast(epoch_seconds as date), hour(cast(epoch_seconds as time)) desc, s asc
limit 10;

The issue I am running into is that when the date is there, it SEEMS to order by date and time, making the other sorts meaningless (since its down to the second) I tested this by trying the following, and it works appropriately.

SELECT TO_DATE('1986-12-11') d, hour(cast(epoch_seconds as time)) as t, score as s
FROM MyTable
order by d, hour(cast(epoch_seconds as time)) desc, s asc
limit 10;

is there a way to sort by Date while ignoring the time?

Thanks

EDIT:

to prove my theory, I tried the the following

TO_CHAR(cast(epoch_seconds as date))

and it displays

2020-09-02 14:53:30.313
Solution

  • So I ended up figuring it out shortly after posting.

    it ain't pretty, but this does the trick.

    select subStr(TO_CHAR(cast(epoch_seconds as date)),0,10) as d,
       cast(epoch_seconds  as time) as t
From table
order by d, t