Pick up the time of day

Question

Pick up the time of day

Navigation

#1 by (0 votes)

1

I have the following select to pick up the time of 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 simpler to do this?

Because it seems like putting this query together like the one I want to do is kind of 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

Type one last_day to get the last date of the month

mysql sql

asked by anonymous 24.07.2018 / 14:58

1 answer

Data normalization in R Importing data type 1,000 (thousands) of html

score 0 · Answer 1

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

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