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

Question

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).

See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the opening and closing values for time periods. — Barmar, Oct 23 '17 at 21:52
To get a specific period of time, use `WHERE created BETWEEN start_time AND end_time` — Barmar, Oct 23 '17 at 21:52
@Barmar - no, please _don't_ use `BETWEEN`. Especially as there are timestamps with sub-second precision, it would be better to use an exclusive upper-bound (`<`), before something unintended happens. — Clockwork-Muse, Oct 23 '17 at 23:31
@Jakub - I usually like to construct a temporary/virtual table (usually just an inline reference) of grouping ranges. This is easiest with a calendar table and numbers table, but the idea is to get something like a `(groupId=1, start='2014-01-01 00:00:00.000', end=start + 28 minutes)` that you can then group on, and is usually helpful for hitting indices, too. — Clockwork-Muse, Oct 23 '17 at 23:40

eggyal · Accepted Answer · 2017-10-23T22:35:59.117

4

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.

edited Oct 23 '17 at 22:35

answered Oct 23 '17 at 22:30

eggyal

122,705
18
212
237

:winces: ouch. No index for you, come back, one year. – Clockwork-Muse Oct 23 '17 at 23:29
@Clockwork-Muse: Depends on the size of the dataset. By all means cache `grp_id` in an indexed column if need be. – eggyal Oct 23 '17 at 23:37
What? Um, no. That would assume the groups are stable, which I tend to consider unlikely (at least two have already been mentioned). I'd much rather solve the general problem of getting joinable, indexable criteria. – Clockwork-Muse Oct 23 '17 at 23:40
@Clockwork-Muse: I look forward to seeing your proposed solution. – eggyal Oct 23 '17 at 23:41
If the table "price" contains 2 or more records with same date (created), then this SQL may return 2 or more records with same "grp_id" (because of joins). – Osoian Marcel Apr 27 '18 at 10:09
@OsoianMarcel: which of those multiple records would you like MySQL to select (and which would you like it to filter)? If there’s no way to differentiate, then returning the set of all such matching records to the application for it to decide how to handle seems to me to be correct behaviour. If there is some way to differentiate, then that should be specified and added to the join criteria. – eggyal Apr 27 '18 at 10:14

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

1 Answers1