0

I have the following table, Input. Do you know how to group the tick data into minute interval as shown in the table, Desired Output using mySQL? I read the data directly from csv to mySQL

Criteria

  1. It will take the last price within the minute bar and output as its minute bar. ie If I have the these two tick data, 06:04:59 1444.25, 06:04:59 1444.00, my output will be 0604 1444.00.

  2. Convert the date, 2013-01-02 to 20130102

     Input            
            row Date        Time        Price   Vol
            1 2013-01-02    06:04:59    1444.25 3
            2 2013-01-02    06:04:59    1444.00 2
            3 2013-01-02    06:05:00    1446.25 1
            4 2013-01-02    06:05:01    1446.00 2
            5 2013-01-02    06:05:02    1446.35 4
            6 2013-01-02    06:07:01    1444.00 2
            7 2013-01-02    06:07:02    1444.25 4
            8 2013-01-03    16:08:31    1434.00 2
            9 2013-01-03    16:08:52    1434.25 4
    

    Desired output

        row Date        Time        Price   Vol
        2 20130102  0604        1444.00 2
        5 20130102  0605        1446.35 4
        7 20130102  0607        1444.25 4
        9 20130103  1608        1434.25 4
    

I have an idea but I not sure how to put into mySQL code. 1. Convert the date to YYYYMMDD and time to HHMM 2. Remove all the duplicates having the same YYYYMMDD and HHMM and only keep the last entry

Thank you for your help and advices L

(from Comment)

CREATE TABLE `es_test2` (
   `xdate` date NOT NULL,
   `time` time DEFAULT NULL,
   `price` decimal(8,2) DEFAULT NULL,
   `vol` int(8) unsigned DEFAULT NULL,
   `xcol` int(10) unsigned DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • 1
    Please provide SHOW CREATE TABLE. – Rick James Oct 20 '17 at 16:37
  • Where's row? It is almost always a bad idea to split date and time into separate columns. – Rick James Oct 20 '17 at 21:14
  • Just added. its called xcol. I have added it in the original question. Its difficult to change my system to cater to your suggestion. Sorry about it. – user2392398 Oct 20 '17 at 22:22
1

What you want is something like this which uses the extract(HOUR_MINUTE..)

SELECT t1.date, t1.time, t2.price, t2.vol
FROM (
  SELECT date,
    extract(HOUR_MINUTE from time) AS time,
    max(id) AS maxid
  FROM tbl
  GROUP BY date, extract(HOUR_MINUTE from time)
) AS t1
JOIN tbl AS t2
  ON t1.maxid = t2.id;

The inner selects and groups by the date,hour/min, and grabs the max(id) and count(*). The outer joins back to the table and resolves to find the price.

In other news, why are you storing date and time separately? You should be using a timestamp.

| improve this answer | |
  • Evan: Its easier to manipulate in terms of numbers than timestamp when I retrieve the data into another system. ie 20170213 0608 means 6.08 am on 2017 feb 13th. You know how to put them into number as well? – user2392398 Oct 20 '17 at 20:32
  • Easily, use dev.mysql.com/doc/refman/5.7/en/… Most other systems will support timestamps in ISO 8601 more so than your format. – Evan Carroll Oct 20 '17 at 20:33
  • true. But actually not that easy. ie Matlab vs Excel vs Sas time-stamp conversion is nightmare. So if i want 2nd Jan 2017 to 16th Feb 2017, my program only needs 20170102 < X < 20170216. Much easier =) – user2392398 Oct 20 '17 at 20:47
  • ISO 8601 is also string-sortable: 2007-01-02T00:00 < X < 2007-02-16T00:00 It also works easily with Matlab, should work in excel too – Evan Carroll Oct 20 '17 at 20:48
  • Just tested the answer above. But how do I store the new table? – user2392398 Oct 21 '17 at 1:02
0

In this table, just_date id a DATE datatype.

mysql> SELECT just_date, 0+just_date FROM `dtts`;
+------------+-------------+
| just_date  | 0+just_date |
+------------+-------------+
| 2011-06-08 |    20110608 |
| 2013-03-10 |    20130310 |
| 2014-02-08 |    20140208 |
| 2006-07-31 |    20060731 |

The other part of your question involves groupwise max, but that is impossible for the table as you present it. There is nothing in the table that says which 06:04:59 comes last. Is there an AUTO_INCREMENT or something else that provides that info? INSERT order does not provide it. A table, by definition, has no order.

With the addition of "row", the "groupwise max" techniques can be applied.

| improve this answer | |
  • The table is in sequential order. I am thinking of converting the time to hhmm and then remove the duplicate time and date by keeping the last entry. But I could not add extra column after converting hhmmss to hhmm in integer values. Do you know how? – user2392398 Oct 20 '17 at 4:40
  • 2
    No, the table is not in sequential order. If you want some order, you must provide a column (etc) to determine the order. Any database is free to shuffle the records. – Rick James Oct 20 '17 at 16:37
  • 2
    Even if the data in the table happens to be stored in sequential order, it may not be accessed in stored order, and may be rearranged in the process of match the constraints of the query. – RDFozz Oct 20 '17 at 17:32
  • ok. I will modify the question above to add in row number – user2392398 Oct 20 '17 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.