Select hard with date

Asked

Viewed 108 times

1

I’m unable to do the following:how to return the time in minutes or hours that a certain WORTH occurred in the table below? Example: how long did the WORTH greater than "23" on 01/05/2017? and if there is an interval between the records? for example 5 minutes with value 23 in the morning and 3 minutes with value 23 in the afternoon? Has to return separately this, or only the total?

 VALOR       HORA
23.77   2017-05-01 21:40:10
23.77   2017-05-01 21:40:11
23.77   2017-05-01 21:40:12
23.93   2017-05-01 21:40:13
23.87   2017-05-01 21:40:14
24.07   2017-05-01 21:40:15
24.07   2017-05-01 21:40:16
23.82   2017-05-01 21:40:18
23.87   2017-05-01 21:40:19
23.94   2017-05-01 21:40:20
23.85   2017-05-01 21:40:21
23.85   2017-05-01 21:40:22
23.78   2017-05-01 21:40:23
24.15   2017-05-01 21:40:24
23.73   2017-05-01 21:40:25
23.73   2017-05-01 21:40:26
23.68   2017-05-01 21:40:27
7.36    2017-05-01 18:40:28
7.34    2017-05-01 18:40:29
7.34    2017-05-01 18:40:31
7.32    2017-05-01 18:40:32
7.39    2017-05-01 18:40:33
7.4     2017-05-01 18:40:34
7.4     2017-05-01 18:40:35
7.41    2017-05-01 18:40:36
7.38    2017-05-01 18:40:37
7.11    2017-05-01 18:40:38
7.11    2017-05-01 18:40:39
7.34    2017-05-01 18:40:40
7.47    2017-05-01 18:40:56
7.54    2017-05-01 18:41:23
mysql sql select-sql

  • What will be the search criteria? Same value and same day?

  • @Jovani what is the expected result? Show me which filter you want and which result I do SQL

  • The criteria will be per day. In the table above for example, the value "24" occurred for 3 seconds on day 01/05 being these ---- for 2 seconds from 2017-05-01 21:40:15 until 2017-05-01 21:40:16 and then occurred 1 second more in 2017-05-01 21:40:24...

  • It would be something like this select 'count in minutes(time)' when value = 23 and date = '01/05/2017' group by hour

  • @Jovani this case 7.47 2017-05-01 18:40:56 and 7.54 2017-05-01 18:41:23 would be what value? And 23.82 2017-05-01 21:40:18 would be 2 seconds? You have control of data recording?

  • which database you use ?

  • @Jovani: What is the database manager: Mariadb? Oracle Database? SQL Server? other?

  • The manager is Mysql...

  • @Marlon Tiedt: 7.47 2017-05-01 18:40:56 and 7.54 2017-05-01 18:41:23 would be what value? If it has the value "7.47" in every second between 18:40:56 and 18:41:23 the result would be 27 seconds...

Show 4 more comments

1 answer

4


See if this is the expected result:

1- Find the shortest date after the value is changed. Ex:

23.77 2017-05-01 21:40:12 23.93 2017-05-01 21:40:13 there was change in value, then the date following the value 23.77 is 2017-05-01 21:40:13

Code: 

  (select top 1 
       x.data 
   from medicoes x 
   where x.valor != m.valor 
   and x.data > m.data 
   order by x.data)

2- Find the shortest date that value occurred, grouping by the last date achieved previously, and already calculating the duration in seconds between the first and last date.

Code:

         select 
             y.dia,
             min(y.data) as primeira_data,
             y.valor,
             y.ultima_data,
             datediff(second,  min(y.data),y.ultima_data) as duracao
          from 
             (select
                  cast(m.data as date) as dia,
                  m.data,
                  m.valor,
                  (select top 1 
                       x.data 
                   from medicoes x 
                   where x.valor != m.valor 
                   and x.data > m.data 
                   order by x.data) as ultima_data
              from medicoes m) y
              group by y.dia, y.valor, y.ultima_data

3- List each day, and sum the durations that occurred by each value, thus having the expected result.

Code:

select
    x.dia,
    x.valor,
    sum(coalesce(x.duracao,0)) as duracao_total_segundos
from
    (select 
         y.dia,
         min(y.data) as primeira_data,
         y.valor,
         y.ultima_data,
         datediff(second,  min(y.data),y.ultima_data) as duracao
      from 
         (select
              cast(m.data as date) as dia,
              m.data,
              m.valor,
              (select top 1 
                   x.data 
               from medicoes x 
               where x.valor != m.valor 
               and x.data > m.data 
               order by x.data) as ultima_data
          from medicoes m) y
          group by y.dia, y.valor, y.ultima_data) x
group by x.dia, x.valor
order by x.dia, x.valor

Note: I put a Coalition in the sum of the duration, because if there is no date greater than the date of the record, the return of the duration will be null, causing the sum to return null as well. Therefore, the last record of the table (in order of date) will always have duration 0 because it is not possible to determine when that value changed.

Check on the Sqlfiddle: http://sqlfiddle.com/#! 6/d5edb/46

Upshot:

dia         valor   duracao_total_segundos
2017-05-01  7.11    2
2017-05-01  7.32    1
2017-05-01  7.34    19
2017-05-01  7.36    1
2017-05-01  7.38    1
2017-05-01  7.39    1
2017-05-01  7.4     2
2017-05-01  7.41    1
2017-05-01  7.47    27
2017-05-01  7.54    10727
2017-05-01  23.68   0
2017-05-01  23.73   2
2017-05-01  23.77   3
2017-05-01  23.78   1
2017-05-01  23.82   1
2017-05-01  23.85   2
2017-05-01  23.87   2
2017-05-01  23.93   1
2017-05-01  23.94   1
2017-05-01  24.07   3
2017-05-01  24.15   1

Edit: I noticed after you asked for some filters and other options, like: show shift, and time in minutes or hours, then...

Code updated and changed to MYSQL:

select
x.dia,
x.valor,
x.turno,
sum(coalesce(x.duracao,0)) as duracao_total_segundos,
sum(coalesce(x.duracao,0))/60.0 as duracao_total_minutos,
sum(coalesce(x.duracao,0))/60.0/60.0 as duracao_total_horas
from
    (select 
         y.dia,
         min(y.data) as primeira_data,
         y.valor,
         y.ultima_data,
         (UNIX_TIMESTAMP(y.ultima_data) - UNIX_TIMESTAMP(min(y.data))) as duracao,
         y.turno
      from 
         (select
              cast(m.data as date) as dia,
              m.data,
              m.valor,
              (select  
                   min(x.data)
               from medicoes x 
               where x.valor != m.valor 
               and x.data > m.data 
               ) as ultima_data,
                (case 
                  when hour(m.DATA)<12 then 'Manhã' 
                  when hour(m.DATA)<18 then 'Tarde'
                  else 'Noite'
                  end ) as turno
                from medicoes m) y
          group by y.dia, y.valor, y.ultima_data,y.turno) x
group by x.dia, x.valor, x.turno
order by x.dia, x.valor;

I updated on Sqlfiddle: http://sqlfiddle.com/#! 9/705f6/12 (obs. The Output is from the last query

The Where part, I leave to you.

I put some extra data in the fiddle to get results with different shifts.

Upshot:

dia         valor   turno   duracao_total_segundos  duracao_total_minutos   duracao_total_horas
2017-05-01  0       Manhã   10797   179.95      2.9991666666
2017-05-01  0       Tarde   17942   299.033333  4.9838888833
2017-05-01  7.11    Manhã   3       0.05        0.0008333333
2017-05-01  7.11    Noite   2       0.033333    0.00055555
2017-05-01  7.11    Tarde   3       0.05        0.0008333333
2017-05-01  7.32    Noite   1       0.016666    0.0002777666
2017-05-01  7.34    Noite   19      0.316666    0.0052777666
2017-05-01  7.36    Noite   1       0.016666    0.0002777666
2017-05-01  7.38    Noite   1       0.016666    0.0002777666
2017-05-01  7.39    Noite   1       0.016666    0.0002777666
2017-05-01  7.4     Noite   2       0.033333    0.00055555
2017-05-01  7.41    Noite   1       0.016666    0.0002777666
2017-05-01  7.47    Noite   27      0.45        0.0075
2017-05-01  7.54    Noite   10727   178.783333  2.9797222166
2017-05-01  23.68   Noite   0       0           0
2017-05-01  23.73   Noite   2       0.033333    0.00055555
2017-05-01  23.77   Noite   3       0.05        0.0008333333
2017-05-01  23.78   Noite   1       0.016666    0.0002777666
2017-05-01  23.82   Noite   1       0.016666    0.0002777666
2017-05-01  23.85   Noite   2       0.033333    0.00055555
2017-05-01  23.87   Noite   2       0.033333    0.00055555
2017-05-01  23.93   Noite   1       0.016666    0.0002777666
2017-05-01  23.94   Noite   1       0.016666    0.0002777666
2017-05-01  24.07   Noite   3       0.05        0.0008333333
2017-05-01  24.15   Noite   1       0.016666    0.0002777666

  • I’ll test it when I get home today, but I think that’s it. Just to understand what I need: this table records the electric current value of a residence every second.... knowing that the shower for example consumes 23 amperes (column value ), how to know how many times the shower was used on day X and for how long each time?

  • the sensor that measures this, is individual, for example, in the shower has a sensor, in the refrigerator other, or is there in the standard, a sensor that measures the overall consumption of the house ? I would place a column to identify the sensor sending the information, and possibly a sequential number each time it starts reading. but pass more information to try to help you better

  • has only one sensor that measures the total current, to put in all points would be expensive to mount, as it is an experience only. But since I’ve been mapping the currents of each piece of equipment, and my focus is systems, I’m trying to put together some algorithm that has only one sensor and the current default values of each piece of equipment (which are in another table) I could tell what is being or has been used at certain times...

  • I understand, but it wouldn’t be very accurate, because adverse situations can happen, but since it’s an experiment, it’s valid. Test the code I’ve already put in and then we’ll see how many times the equipment has been used

  • Thank you so much for the help, I will need to improve the way I enter the data, because I have the record of only 2 months (my idea was to always keep the history of 1 year), and already there are more than 50 000,000 lines, so the research takes a long time to return the values.Again thank you very much for the effort in helping me, helped a lot!!!

  • good that helped, about performance, I believe that it is possible to improve, remember to create index for the columns as well. To start, you can put to store the turn in another column, so that it is not necessary the case, and also the date without the hours in another column, to discard the cast

Show 1 more comment

Browser other questions tagged mysql sql select-sql

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