I have a feild that store crontab ,now I want to sort the tablee accordring to the crontab hour,how to write the sql? For example,the crontab field is :
7 9 9 */1 * ?,
0 9 8 */1 * ?,
0 9 19 */1 * ?;
I want the result is
0 9 8 */1 * ?,
7 9 9 */1 * ?,
0 9 19 */1 * ?;
Now I can only write the sql like this:
select * from tb_student order by crontab desc
CodePudding user response：
Because Seconds is not Mandatory for crontab expression and there might be some Special characters as below tables.
| Field name | Mandatory? | Allowed values | Special characters |
|---|---|---|---|
| Seconds | No | 0-59 | * / , - |
| Minutes | Yes | 0-59 | * / , - |
| Hours | Yes | 0-23 | * / , - |
| Day of month | Yes | 1-31 | * / , - L W |
| Month | Yes | 1-12 or JAN-DEC | * / , - |
| Day of week | Yes | 0-6 or SUN-SAT | * / , - L # |
| Year | No | 1970–2099 | * / , - |
if we make sure the hour will be number and as same format.
0 9 19 */1 * ?
We can try to use SUBSTRING_INDEX to get part of your expect order number.
SELECT *
FROM T
ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(Crontab, ' ', 3), ' ', -1) AS SIGNED)
IMHO, I would create a column to store a value which can create an index and improve the performance if your want to use that be order or filter condition instead of order by crontab column.