Question 20392 in Mysql Question posted in
The official MySQL documentation can be found here.

Grouping into interval of 2 minutes within a time range – Mysql

i have some issue with grouping the data in interval of 2 minutes.
here is my table data

ticker open high low close time
APPL 1940 1950 1935 1948 2023-03-20 11:00:00
APPL 1950 1960 1945 1958 2023-03-20 11:01:00
APPL 1960 1970 1955 1968 2023-03-20 11:02:00
APPL 1970 1980 1965 1978 2023-03-20 11:03:00
APPL 1980 1990 1985 1998 2023-03-20 11:04:00
APPL 1990 2000 1975 1988 2023-03-20 11:05:00

My query:

select
  open,
  MAX(high) as high,
  MIN(low) as low,
  close,
  time,
  FROM_UNIXTIME(FLOOR( UNIX_TIMESTAMP(time)/120 ) * 120) AS quaterhour
from tableName
where ticker = 'APPL'
group by quaterhour
order by quaterhour ASC

Expected output:

ticker open high low close time
APPL 1940 1960 1935 1958 2023-03-20 11:00:00
APPL 1960 1980 1955 1978 2023-03-20 11:02:00
APPL 1980 2000 1975 1988 2023-03-20 11:04:00

I only have issues with getting the latest close price with in range. right now getting from first row not last row in 2 minute.

Tags:

2

Answers


  1. 0

    When ONLY_FULL_GROUP_BY is disabled, MySQL allows non-aggregate grouping columns to be omitted, in which case it returns a random row (usually the first row encountered) instead of aggregating. Your query does this.

    List all non-aggregating columns in the group by:

    select
  open,
  MAX(high) as high,
  MIN(low) as low,
  close,
  FROM_UNIXTIME(FLOOR( UNIX_TIMESTAMP(time)/120 ) * 120) AS quaterhour
from tableName
where ticker = 'APPL'
group by open, close, quaterhour
order by quaterhour ASC

    You must remove the time from the select to aggregate over time.

    Also, "quarter" is a poor name choice for a 2-minute period.

    Login or Signup to reply.
  2. 0

    Try this, i hope there is no typo

    select
  open,
  MAX(high) as high,
  MIN(low) as low,
  MAX(IF((minute(time) & 1),close,0)),
  time,
  time - INTERVAL (minute(time) & 1) MINUTE AS quaterhour
from tableName
where ticker = 'APPL'
group by quaterhour
order by quaterhour ASC;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search