SQL - Selecting days instead of hours?

0
votes

I am using this to select AVG of hours! How can I select days instead of hours?

SELECT 
    round(AVG(s.temperatur),2) snitt_temp, 
    max(s.temperatur) max_temp, 
    min(s.temperatur) min_temp,

    round(AVG(s.fuktighet),2) snitt_fukt, 
    max(s.fuktighet) max_fukt, 
    min(s.fuktighet) min_fukt,

    round(AVG(s.rssi),2) snitt_rssi, 
    max(s.rssi) max_rssi, 
    min(s.rssi) min_rssi,

    cast(CONCAT(cast(date(s.time) AS char) ,' ',hour(s.time),':0:0')  as datetime) as hrs
FROM test.test s
group by hrs

I did manage to select days with changing hour(s.time) to day(s.time), but it also added hours! Also I want it to start going back 24 hours (one day) from current time, like what was the average value from now (this very second) and 24 hours back.

Current output when I change "hour" to "day" (I'm getting one entry for each day but it's one hour later then the previous one): 

26.08   26.78   24.03   35.06   40.75   30.74   -41.46  0   -52 2019-05-01 01:00:00
25.63   26.66   24.79   29.71   35.41   23.92   -40.06  -35 -49 2019-05-02 02:00:00
24.64   25.58   20.21   28.35   36.75   21.71   -40.26  0   -52 2019-05-03 03:00:00
24.02   25.58   23.02   27.62   31.93   26.14   -41.35  -35 -50 2019-05-04 04:00:00
24.43   25.7    23.38   29.31   44.77   26.41   -40.72  -36 -55 2019-05-05 05:00:00
24.99   25.8    23.84   31.01   41.82   22.38   -43.91  -38 -52 2019-05-06 06:00:00
24.70   25.5    24.03   27.27   32.28   25.03   -42.35  -38 -54 2019-05-07 07:00:00
24.91   25.73   23.9    27.19   32.57   25.31   -44.32  -38 -52 2019-05-08 08:00:00

The data that I want is AVG of time between -24 hours from now() and now() !

Edit: After changing to "DATE(s.time) as date" I get (don't worry about the order of the dates, it is me sorting manually): 

24.67   25.5    24.03   33.11   34.48   30.83   -49.12  -45 -60 2019-11-27
25.72   26.63   24.55   34.96   40.71   30.71   -49.67  -45 -59 2019-11-26
25.49   26.59   24.51   35.52   42  33.49   -50.15  -44 -66 2019-11-25
26.24   27.14   24.96   36.47   42.84   33.52   -50.62  -45 -60 2019-11-24
26.58   27.6    25.92   35.49   38.41   32.64   -52.92  -48 -61 2019-11-23
26.72   27.55   25.99   34.85   39.68   31.66   -53.53  -47 -67 2019-11-22
26.21   27.05   25.96   32.23   34.55   30.42   -52.29  -47 -64 2019-11-21
25.75   27.58   24.12   32.30   36.15   30.67   -51.23  -46 -74 2019-11-20
27.04   28.58   24.86   33.93   43.49   29.99   -54.73  -47 -72 2019-11-19

Is this from the moment of request and days back or is it 00:00:00, to 00:00:00 the next day? I need last 24 hours from the moment I populate the list! If I request data and the time is 14:38, I want Average data from 14:38 and back to the previous day when it was 14:38 and so on (last 24 hours from the second of getting data).

2
sqlmariadb
Can you please share current output and expected output? – jishan siddique
what is the data type of test.time? – LukStorms
Name: time, Type: timestamp, Null: No, Default: current_timestamp() ? And it looks like this: 2019-11-27 19:04:12. – Bluelight

2 Answers

1
votes

I think that this it what you need.

SELECT 
    round(AVG(s.temperatur),2) snitt_temp, 
    max(s.temperatur) max_temp, 
    min(s.temperatur) min_temp,

    round(AVG(s.fuktighet),2) snitt_fukt, 
    max(s.fuktighet) max_fukt, 
    min(s.fuktighet) min_fukt,

    round(AVG(s.rssi),2) snitt_rssi, 
    max(s.rssi) max_rssi, 
    min(s.rssi) min_rssi,

    DATE(s.time) as date
FROM test.test s
group by date

Let me know if it works. It not, please share more info about your test table(Ex. create table statement).

0
votes

A date & time can be treated as a string.

2020-01-12 05:51:55
0000000001111111111
123456789-123456789

So,

LEFT(my_date,  7) is a year & month
LEFT(my_date, 10) is a date
LEFT(my_date, 13) is a hour
LEFT(my_date, 16) is a minute

Then

SELECT LEFT(my_date, 13) AS to_hour,
       AVG(temperature) AS avg_temp,
    FROM tbl
    GROUP BY 1  -- (shorthand for the 1st expression in `SELECT`)

will give you the avg temp for each hour.