Catch the hours of the day

Asked

Viewed 75 times

1

I have the following select to pick up the hours of the day

SELECT '00:00' AS A UNION 
SELECT '01:00' UNION
SELECT '02:00' UNION
SELECT '03:00' UNION
SELECT '04:00' UNION
SELECT '05:00' UNION
SELECT '06:00' UNION
SELECT '07:00' UNION
SELECT '08:00' UNION
SELECT '09:00' UNION
SELECT '10:00' UNION
SELECT '11:00' UNION
SELECT '12:00' UNION
SELECT '13:00' UNION
SELECT '14:00' UNION
SELECT '15:00' UNION
SELECT '16:00' UNION
SELECT '17:00' UNION
SELECT '18:00' UNION
SELECT '19:00' UNION
SELECT '20:00' UNION
SELECT '01:00' UNION
SELECT '22:00' UNION
SELECT '23:00'

Is there any other simpler to do this?

'Cause it seems that by putting this query together as what I want to do gets a little slow.

For example:

         SELECT * FROM( 
                SELECT * FROM (
                    SELECT '00:00' AS A UNION 
                    SELECT '01:00' UNION
                    SELECT '02:00' UNION
                    SELECT '03:00' UNION
                    SELECT '04:00' UNION
                    SELECT '05:00' UNION
                    SELECT '06:00' UNION
                    SELECT '07:00' UNION
                    SELECT '08:00' UNION
                    SELECT '09:00' UNION
                    SELECT '10:00' UNION
                    SELECT '11:00' UNION
                    SELECT '12:00' UNION
                    SELECT '13:00' UNION
                    SELECT '14:00' UNION
                    SELECT '15:00' UNION
                    SELECT '16:00' UNION
                    SELECT '17:00' UNION
                    SELECT '18:00' UNION
                    SELECT '19:00' UNION
                    SELECT '20:00' UNION
                    SELECT '01:00' UNION
                    SELECT '22:00' UNION
                    SELECT '23:00' 
                ) A
             )A
             LEFT JOIN( SELECT DATE_FORMAT( date, '%H:%i' ) HORA
                       ,TRUNCATE( MAX( humidade ),2 ) MAX_HUMIDADE
                       ,TRUNCATE( MIN( humidade ),2 ) MIN_HUMIDADE
                   FROM medicao
                  WHERE DATE(date) = ?
                  GROUP BY HOUR(date)
                 ) B ON B.HORA = A.A

Like a last_day to get the last date of the month

mysql sql
  • 1

    cannot put values in a table?

  • I don’t know, I’m trying to optimize my consultation

  • Couldn’t you treat this in PHP for example? Take the time of each line and ignore the seconds? Just for the record, you’re using the as A more than once, I mean, even if implicit this using, it would probably cause ambiguity.

  • @Guilhermenascimento So you only need to use the table that is in Join? In php I would do a for?

  • So, your JOIN seems exactly to group for hours, until I understand the need from above, but I believe that yes, you would use a for/while to iterate and save the hours in a group way, but it depends on whether this is really what I understood, if it is something else could clarify me? I’m pretty sure you can simplify your code enough.

  • It is because I want to bring even the schedules that have no value, for example from 01 to 15 had value, but from 16 has not yet, to show on the graph

Show 1 more comment

1 answer

0

The best way is to put this on a table.
I think that in MySQL will work in the same way as in SQL (otherwise it will only be necessary to make one or the other):

DECLARE @Hora INT = 0

CREATE TABLE #tmpHorasDoDia
(
    Hora NVARCHAR(5)
)

WHILE @Hora < 24
BEGIN
    IF @Hora < 10
        INSERT INTO #tmpHorasDoDia VALUES('0' + CAST(@Hora AS NVARCHAR(2)) + ':00')
    ELSE
        INSERT INTO #tmpHorasDoDia VALUES(CAST(@Hora AS NVARCHAR(2)) + ':00')

    SET @Hora = @Hora + 1
END

  • This is a temporary table?

  • Yes @adventistaam, it is, but you can create a physical table if the goal is to use it in other queries in the future.

  • Like your experience, it gets faster if you create a table to make the comparison than those unions that I created?

  • Yes, it depends on the query, but by default use UNION is never good for performance. But what is your goal with the consultation?

  • It is because I want to bring even the schedules that have no value, for example from 01 to 15 had value, but from 16 has not yet, to show on the graph

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.