Title: SQL Query min(y) max(y) and X over a group by
Tags: mysql;sql;mysql-5.5
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 :(
Comment: 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?

Comment: it would be nice if you post the error message you're getting

Comment: 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`?

Comment: 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.

Comment: so you'll need to work like the examples on the link @Shadow has posted and adapt to your need.

Comment: 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

Comment: @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.

Comment: @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...

Comment: @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

Comment: @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

Comment: @Shadow I think you are right and that it is a duplicate (dont know how to accept it however).

Comment: @ElmerDantas group by interval and TSTAMP just selects every row.

Comment: Tell us about `isStatusGoodBadOrUncertain()`


Here is another answer: 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.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 13, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:04/26/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><sql><mysql-5.5>', 'Title': 'SQL Query min(y) max(y) and X over a group by', 'ViewCount': 149, 'id': 43628808, 'user_AboutMe': '', 'user_AccountId': 1382039.0, 'user_DisplayName': 'Marc', 'user_Reputation': 174.0, 'user_Views': 14.0, 'user_id': 1314823.0}}