Select to sort by hour [closed]

Question

Select to sort by hour [closed]

Navigation

#1 by (3 votes)

0

Good afternoon Personal, I need to do a select that organizes by date and time. By date is already working, however if I put an "and" "time" after the date, it messes up all the records. See my select:

$dados = Connection::select("Select *,id, data, mes,date_format('data','%d/%m/%Y') as 'data_formatada' FROM (SELECT Day(data) AS dia, year(data) AS ano,    
                    (CASE month(data) 
                       when 1 then 'Janeiro'
                       when 2 then 'Fevereiro'
                       when 3 then 'Março'
                       when 4 then 'Abril'
                       when 5 then 'Maio'
                       when 6 then 'Junho'
                       when 7 then 'Julho'
                       when 8 then 'Agosto'
                       when 9 then 'Setembro'
                       when 10 then 'Outubro'
                       when 11 then 'Novembro'
                       when 12 then 'Dezembro'
                       END) AS mes,
                    (CASE WEEKDAY(data) 
                       when 0 then 'Segunda-feira'
                       when 1 then 'Terça-feira'
                       when 2 then 'Quarta-feira'
                       when 3 then 'Quinta-feira'
                       when 4 then 'Sexta-feira'
                       when 5 then 'Sábado'
                       when 6 then 'Domingo'                 
                       END) AS DiaDaSemana,
                       id, 
                       data,hora,compromisso,pessoa,local FROM agenda WHERE not (data is null)) as agenda order by data asc"

mysql

asked by anonymous 19.02.2016 / 19:01

1 answer

Query in MySQL with undesirable items - PHP How to adjust bootstrap image height? [closed]

score 3 · Accepted Answer

The ORDER BY command accepts multiple parameters:

ORDER BY campo1 ASC, campo2 ASC

Then your query would look like:

$dados = Connection::select("
        Select 
            *,
            date_format('data','%d/%m/%Y') as 'data_formatada' 
        FROM (
            SELECT 
                Day(data) AS dia, 
                year(data) AS ano,
                (
                    CASE month(data) 
                       when 1 then 'Janeiro'
                       when 2 then 'Fevereiro'
                       when 3 then 'Março'
                       when 4 then 'Abril'
                       when 5 then 'Maio'
                       when 6 then 'Junho'
                       when 7 then 'Julho'
                       when 8 then 'Agosto'
                       when 9 then 'Setembro'
                       when 10 then 'Outubro'
                       when 11 then 'Novembro'
                       when 12 then 'Dezembro'
                   END) AS mes,
                (
                    CASE WEEKDAY(data) 
                       when 0 then 'Segunda-feira'
                       when 1 then 'Terça-feira'
                       when 2 then 'Quarta-feira'
                       when 3 then 'Quinta-feira'
                       when 4 then 'Sexta-feira'
                       when 5 then 'Sábado'
                       when 6 then 'Domingo'                 
                       END
                    ) AS DiaDaSemana,
                id, 
                data,
                hora,
                compromisso,
                pessoa,
                local 
            FROM 
                agenda
            WHERE 
                not (data is null)
        ) as agenda

        ORDER BY
            data ASC, 
            hora ASC");