CODEWITHSUNDEEP
phpmysql
RedDevil
RedDevil

Reputation: 29

Apply a circular shift to an ORDER BY query?

I have a weekly timeline on mysql wherein I sort activities by time using the built-in ORDER BY function: ORDER BY time.

Actual result

00:01
07:00
08:00
11:00
23:00

Expected result

07:00
08:00
11:00
23:00
00:01
01:00
04:00

etc., 'til 07:00 (e.g., modulus 7 a.m.).

How can I change the format?

Upvotes: 0

Views: 90

Answers (2)

DRapp
DRapp

Reputation: 48169

What it appears is you want the rotation based on 7:00am - 6:59am. You could just do an order by based on if the time is BEFORE 7am, force a pre-sort to 2... if 7am or AFTER, pre-sort is 1, then sort by the natural time...

order by
   case when hour( columnTime ) < 7 then 2 else 1 end,
   columntime

this way if your time samples were

00:01a
02:27a
05:42a
06:59a
07:00a
10:29a
02:27p
11:59p

The presort would result like...

PRESORT  Time
1        07:00a
1        10:29a
1        02:27p
1        11:59p
2        00:01a
2        02:27a
2        05:42a
2        06:59a

updated via SQLFiddle adjusted. The function HOUR() returns the numeric our of a date/time column. You had HOURS(). Now, the parameter to the function is the name of the column that holds the date/time field. I didn't have your table structure to KNOW the column name so I just called it "columnTime". Per the SQLFiddle sample you had, I changed it to...

HOUR( hours ) <-- HOUR() function, using your column "hours" from the table

Upvotes: 1

echo_Me
echo_Me

Reputation: 37253

you maybe looking for this ?

  ORDER BY str_to_date(columntime,'%H:%i')

or this

 ORDER BY  MINUTE(columntime) , SECOND(columntime)

DEMO HERE

EDIT:

SELECT * 
FROM tableName
ORDER BY CASE WHEN HOUR(date_time) BETWEEN 7 AND  12
   THEN 2
    else 1
end desc ,date_time asc

DEMO

Upvotes: 0

Related Questions