CODEWITHSUNDEEP
mysqlsqlcharts
Jakub
Jakub

Reputation: 141

Selecting Candlestick data (max, min, open, close) for specific period of time

I'm working on candlestick chart data. I have a database table (MySql) with

created                  value
2017-09-25 17:55:04    3322.09
2017-09-25 18:00:03    3317.49
2017-09-25 18:05:02    3316.12
2017-09-25 18:10:03    3325.16
2017-09-25 18:15:04    3326.01
2017-09-25 18:20:03    3330.49
2017-09-25 18:25:04    3331.49
2017-09-25 18:30:04    3335.24
2017-09-25 18:35:03    3347.13
2017-09-25 18:40:03    3343.21
2017-09-25 18:45:05    3341.78
2017-09-25 18:50:03    3339.64
2017-09-25 18:55:04    3336.12
2017-09-25 19:00:04    3327.73
2017-09-25 19:05:03    3327.96
2017-09-25 19:10:04    3325.93

And I'm trying to select a maximum, minimum, open value, close value for any period of time (e.g. 15 minutes or every 4 hours, every 6 days etc.)

So far I have 

SELECT p.created, HOUR(`DATE`), MAX(p.value), MIN(p.value) 
FROM price 
GROUP BY DATE(`DATE`),HOUR(`DATE`)

Not so complicated. It gives me maximum and minimum from every hour, so for minutes and "full" periods like days, weeks, month I can use MONTH, DAY etc. functions.

Any idea how to build a query for any period of time, let's say 28 minutes or 31 hours etc? Also, this works for maximum and minimum, but I also need a opening value (first) for time period and close value (last).

Upvotes: 0

Views: 2829

Answers (1)

eggyal
eggyal

Reputation: 125865

To group on arbitrary periods of time, you can take the UNIX time value of your created column (i.e. the number of seconds since the UNIX epoch) and divide by the size of your desired periods, taking only the integer part of the resulting quotient (and discarding the remainder). So, to group every 28 minutes:

SELECT   FROM_UNIXTIME(28*60 * (UNIX_TIMESTAMP(created) DIV (28*60))) grp_id
         MIN(value)    min_value,
         MAX(value)    max_value,
         MIN(created)  min_created,
         MAX(created)  max_created
FROM     price
GROUP BY grp_id

You'll notice that I've also selected the min_created and max_created, which yield the times of the first and last values in each group. You can then join the result back to the price table again to obtain the values of the relevant records (this is essentially an instance of the "groupwise maximum/minimum" problem):

SELECT t.grp_id,
       t.min_value,
       t.max_value,
       price_min.value first_value,
       price_max.value last_value
FROM (
  SELECT   FROM_UNIXTIME(28*60 * (UNIX_TIMESTAMP(created) DIV (28*60))) grp_id
           MIN(value)    min_value,
           MAX(value)    max_value,
           MIN(created)  min_created,
           MAX(created)  max_created
  FROM     price
  GROUP BY grp_id
) t JOIN price price_min ON price_min.created = t.min_created
    JOIN price price_max ON price_max.created = t.max_created

See it on sqlfiddle.

Upvotes: 4

Related Questions