SQL Query min(y) max(y) and X over a group by

Question

I have a very simple table called MYTABLE:

TSTAMP VARCHAR(20), VALUE VARCHAR(20)

The timestamp is like yyyy-mm-ddTHH:MM:SS.zzzZ (ex: 2017-04-25T12:23:00.000Z). Value is a float.

I know this table model is just bad but it has been done many years ago by someone else who obviously didn't know what he/she was doing.

I am trying to write an efficient query to get the min(VALUE), the max(VALUE) and their TIMESTAMP over intervals of time (ex: min/max of each minute). I am able to get the min and max value with the following query but I cannot see a way to get their timestamps.

SELECT MIN(tstamp)
     , MAX(tstamp)
     , MIN(value) minVal
     , MAX(value) max
     , ABS(TRUNCATE(((UNIX_TIMESTAMP(tstamp)*1000+SUBSTR(tstamp,-4,3)) - 1493115780000)/20000,0)) intervalNumber
     , tstamp
  FROM mytable
 WHERE tstamp BETWEEN '2017-04-25T12:23:00.000Z' AND '2017-04-25T12:24:00.000Z' 
   AND NOT tstamp = '2017-04-25T12:24:00.000Z' 
 GROUP 
    BY intervalNumber;

WHERE 1493115780000 is the result of:

    SELECT UNIX_TIMESTAMP('2017-04-25T12:23:00.000Z')*1000+SUBSTR('2017-04-25T12:23:00.000.000Z',-4,3);

*EDIT BECAUSE I WAS NOT CORRECTLY EXPLAINING What i get is for each interval: first timestamp , lasttimestamp ,min value , maxvalue , internval number, first timestamp

What i want is: timestamp of min value, timestamp of max value, min value, max value, interval number

I am using Mysql 5.5.

Any help would be appreciated :) It looks like an university tutorial but this is now too far behind me :(

it would be nice if you post the error message you're getting — Elmer Dantas, Apr 26 '17 at 08:31
@ElmerDantas I am not getting error message. It is just that i am not able to figure out how to get the timestamps of min and max values. — Marc, Apr 26 '17 at 08:34
@Milney : i am not the one who defined this datamodel (i have been complaining on it for many years....) i know it is just wrong... — Marc, Apr 26 '17 at 08:36
Is the timestamp you are wanting saved in the TSTAMP field? I think STR_TO_DATE() can convert your TSTAMP 'yyyy-mm-ddTHH:MM:SS.zzzZ' to a more standard MYSQL datetime stamp.. then you can use UNIX_TIMESTAMP() to convert that to a timestamp. But im probably way of the pace here with what you are after — Jason Joslin, Apr 26 '17 at 08:45
@Strawberry oups i missed this one while making this query simpler (actually there is a status and i filter bad ones). But it is not part of the question. I will edit. thanks — Marc, Apr 26 '17 at 08:49
Since each time period may encompass multiple rows (1 or more), why not just get the min and max timestamp, and maybe a row count? — Sloan Thrasher, Apr 26 '17 at 08:51
I won't consider your whole format, abs, truncate thing..if your question is about "how do I get min and max from timestamp" this is not the case to just `select timestamp, min(value), max(value) where clause` and grouping by `timestamp`? — Elmer Dantas, Apr 26 '17 at 09:11
@ElmerDantas You are right, i should have much simplified the question. Sorry for that. Regarding your answer it is not correct: i need to group by time intervals. It could be like : i need min/max value and their timestamps for each hour of data — Marc, Apr 26 '17 at 09:41
@Shadow I think you are right and that it is a duplicate (dont know how to accept it however). — Marc, Apr 26 '17 at 09:42
it's a little difficult because I can't test it but I think you just need to remove your `max/min(tstamp)` from you select and group by interval AND tstamp. — Elmer Dantas, Apr 26 '17 at 09:50
@ElmerDantas group by interval and TSTAMP just selects every row. — Marc, Apr 26 '17 at 09:55
so you'll need to work like the examples on the link @Shadow has posted and adapt to your need. — Elmer Dantas, Apr 26 '17 at 10:10

score 0 · Answer 1 · answered Apr 26 '17 at 08:50

Assuming ABS(TRUNCATE(((UNIX_TIMESTAMP(TSTAMP)*1000+SUBSTR(TSTAMP,-4,3)) - 1493115780000)/20000,0)) as intervalNumber is correct.

I think that you're looking for something like :

-- Min value
SELECT top 1 min(TSTAMP), VALUE,
    ABS(TRUNCATE(((UNIX_TIMESTAMP(TSTAMP)*1000+SUBSTR(TSTAMP,-4,3)) - 1493115780000)/20000,0)) as intervalNumber
FROM MYTABLE
WHERE   TSTAMP between '2017-04-25T12:23:00.000Z' AND '2017-04-25T12:24:00.000Z' 
    AND NOT TSTAMP='2017-04-25T12:24:00.000Z' 
GROUP BY VALUE desc

-- Max value
SELECT top 1 max(TSTAMP), VALUE,
    ABS(TRUNCATE(((UNIX_TIMESTAMP(TSTAMP)*1000+SUBSTR(TSTAMP,-4,3)) - 1493115780000)/20000,0)) as intervalNumber
FROM MYTABLE
WHERE   TSTAMP between '2017-04-25T12:23:00.000Z' AND '2017-04-25T12:24:00.000Z' 
    AND NOT TSTAMP='2017-04-25T12:24:00.000Z' 
GROUP BY VALUE asc

The best solution would be to clean your table in the first place by dumping all in a temp table, recreate the table with correct structure and dump back with conversions. Your request shall be simplier after that.

SQL Query min(y) max(y) and X over a group by

1 Answers1