How to split a sample of MySQL on periods?



There is a table of transactions (the transaction date and its value). I need to graph transactions, breaking them into intervals. For example I want to see the sum of transactions for the previous day, breaking them down into hour increments. Ie show the amount of transactions from 00:00 to 01:00 27/03, c 01:01 to 02:00 27/03 etc to 00:00 28/03.  
  

I use this query:  
  

```
SELECT id, DATE_FORMAT(tstamp, '%Y-%m-%d %H:%i') as dt, sum(amount) as amount
FROM transactions
GROUP BY time_to_sec(tstamp)- time_to_sec(tstamp)%(60*60)
ORDER BY dt DESC
```
  
  

The problem is that it displays some strange value date:  
  

```
| 2018-03-27 19:22 | 103112.02551724651 |
| 2018-03-27 21:45 | 60061.116740413505 |
| 2018-03-28 01:16 | 103629.88987819279 |
| 2018-03-28 04:55 | 158171.05616502554 |
| 2018-03-28 05:00 | 107739.29807474681 |
| 2018-03-28 06:25 | 106808.2803523091 |
| 2018-03-28 07:26 | 158702.58691193615 |
| 2018-03-28 08:05 | 143791.8812470863 |
| 2018-03-28 09:39 | 126213.54103553489 |
| 2018-03-28 10:09 | 152209.51851207032 |
| 2018-03-28 11:21 | 145477.54706805685 |
| 2018-03-28 13:34 | 127102.92891048045 |
| 2018-03-28 14:27 | 121832.60294653267 |
| 2018-03-28 17:13 | 103144.06730868602 |
| 2018-03-29 00:13 | 48084.689964825106 |
| 2018-03-29 02:35 | 148910.04249381932 |
| 2018-03-29 03:16 | 171725.706533219 |
| 2018-03-29 12:37 | 141955.3731104362 |
| 2018-03-29 15:50 | 130090.14078938986 |
| 2018-03-29 16:33 | 98498.52734681964 |
| 2018-03-29 18:40 | 51951.85931785985 |
| 2018-03-29 22:19 | 65228.564068534484 |
| 2018-03-29 23:20 | 41622.47713425062 |
| 2018-04-02 20:00 | 68994.59391487371 |
```
  
  

As you can see, they are not located at precise intervals. What is wrong in the query?
In the end, I found this query, which groups the way it should:  
  

```
SELECT 
 FROM_UNIXTIME( TRUNCATE( UNIX_TIMESTAMP( tstamp )/900, 0 ) * 900 ) as h,
 AVG( amount ) as v
FROM 
transactions
GROUP BY 
h
ORDER BY 
 h desc
LIMIT
 24
```
Hi,  

  

check the following Code  

SQLCopy Code

```
-- Question 1 Answer
SELECT E.EmployeeID,E.Day, (E.TotalTime/60) 'TotalHours', (E.TotalTime%60) 'Minites'
FROM (SELECT EmployeeID,CONVERT(VARCHAR(10),FromTime,102) 'Day',SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
      FROM tblEmployeeTimeCard
      GROUP BY EmployeeID,CONVERT(VARCHAR(10),FromTime,102))E

-- Question 1&2 Answer
SELECT E.EmployeeID,E.Day, (E.TotalTime/60) 'TotalHours', (E.TotalTime%60) 'Minites',
CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND((E.TotalTime/60*6),1))
     ELSE (E.TotalTime/60) END 'TotalWorkHours'
FROM (SELECT EmployeeID,CONVERT(VARCHAR(10),FromTime,102) 'Day',SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
      FROM tblEmployeeTimeCard
      GROUP BY EmployeeID,CONVERT(VARCHAR(10),FromTime,102))E
```
  

  

For ur Question 2, I am not getting. give some more details about that. if possible send some sample data. if access over lap like (From date is 2013 -04-05 11:55:00PM to 2013-04-05 19:10:00PM)  

then we can't able to fine the time difference. So check the code for Question No 1 and give the feedback.  

  

Regards,  

GVPrabu Looks like YYMMDD to me. I would try to verify this in the documentation or whatever information source you have on the old database. You could also look for inappropriate numbers like MM not between 1 and 12 and DD not between 1 and 31. If you find any inappropriate numbers than this is the wrong approach. This does not look like a date based on an offset number of days after a start date because the start date would have to be about 2600 years ago. a pattern with twelve data and twelve greedydata is going to be ridiculously expensive if it fails to match. it may well exceed the 30 second timeout. i would suggest you match multiple patterns to pick out parts of the message

grok {

break\_on\_match => false

match => {

… A couple of notes to get started:



> 
> .apply(literal\_eval)
> 
> 
> 


Wouldn't this be better as `map`?



> 
> I then re-create the Dask DF:
> 
> 
> ddf = dd.from\_pandas(ddf\_out, npartitions=nCores)
> 
> 
> 


`ddf_out` was already a dask dataframe, I don't know why you had to do this.



> 
> The columns in result seem to match those in metadir.
> 
> 
> 


The value of `result.columns` is taken from the meta you provided, no computing happens until you ask for it (dask is lazy in most operations). Does the ValueError exception not provide further information?


Here is a full example



```
x = ({'F9_07_PZ_COMP_DIRECT': '0',
  'F9_07_PZ_DIRTRSTKEY_NAME': 'DEBRA MEALY',
  'F9_07_PZ_COMP_OTHER': '0',
  'F9_07_PZ_COMP_RELATED': '0',
  'F9_07_PZ_TITLE': 'CHAIR PERSON',
  'F9_07_PZ_AVE_HOURS_WEEK': '1.00',
  'F9_07_PC_TRUSTEE_INDIVIDUAL': 'X'},
 {'F9_07_PZ_COMP_DIRECT': '0',
  'F9_07_PZ_DIRTRSTKEY_NAME': 'HELEN GORDON',
  'F9_07_PZ_COMP_OTHER': '0',
  'F9_07_PZ_COMP_RELATED': '0',
  'F9_07_PZ_TITLE': 'VICE CHAIR',
  'F9_07_PZ_AVE_HOURS_WEEK': '1.00',
  'F9_07_PC_TRUSTEE_INDIVIDUAL': 'X'})
df = pd.DataFrame({'a': x})
d = dd.from_pandas(df, 1)
meta = pd.DataFrame(columns=['F9_07_PZ_COMP_DIRECT', 
       'F9_07_PZ_DIRTRSTKEY_NAME',
       'F9_07_PZ_COMP_OTHER', 'F9_07_PZ_COMP_RELATED', 'F9_07_PZ_TITLE',
       'F9_07_PZ_AVE_HOURS_WEEK', 'F9_07_PC_TRUSTEE_INDIVIDUAL'], dtype="O")
d.map_partitions(lambda df: df.a.apply(pd.Series), meta=meta).compute()

```

How did I know what `meta` to use? I applied the function to the pandas dataframe - you might use a small piece of the dataframe to do this.


Some additional notes:


* it is an anti-pattern to load the data with pandas, pass to dask workers and then gather the whole result back to a pandas (in memory) dataframe, you are very unlikely to see a speedup this way and may incur a lot of overhead. You are better off loading with something like `dd.read_csv`, and aggregating or writing with dask functions too. Only `compute()` on something that will be small or not return anything (because it involves writing the output). The official examples do not use from\_pandas.
* string and dict processing are python methods, and so hold the interpreter lock (GIL) of any python function: threads will not actually run in parallel. To get parallelism, you need to run in processes, which is most easily achieved using <https://docs.dask.org/en/latest/setup/single-distributed.html>
* the distributed scheduler also gives you access to the dashboard, which has a lot of useful information to diagnose how your system is running. You can also configure a lot about its behaviour, in case you have firewall rules you need to obey. Use [UNIX\_TIMESTAMP](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions) function to convert timestamps to seconds, then subtract:



```
select UNIX_TIMESTAMP('20190102 02:14:18','yyyyMMdd HH:mm:ss') -
       UNIX_TIMESTAMP('20190102 00:01:05','yyyyMMdd HH:mm:ss');

```

Returns:


`7993` seconds. 


Difference in `'HH:mm:ss'` format:



```
select from_unixtime(UNIX_TIMESTAMP('20190102 02:14:18','yyyyMMdd HH:mm:ss') -
       UNIX_TIMESTAMP('20190102 00:01:05','yyyyMMdd HH:mm:ss'), 'HH:mm:ss');

```

Returns:



```
02:13:13

``` Simply using `df.mean()` will Do The Right Thing(tm) with respect to NaNs:



```
>>> df
                 A      B
DATE                     
2013-05-01  473077  71333
2013-05-02   35131  62441
2013-05-03     727  27381
2013-05-04     481   1206
2013-05-05     226   1733
2013-05-06     NaN   4064
2013-05-07     NaN  41151
2013-05-08     NaN   8144
2013-05-09     NaN     23
2013-05-10     NaN     10
>>> df.mean(axis=1)
DATE
2013-05-01    272205.0
2013-05-02     48786.0
2013-05-03     14054.0
2013-05-04       843.5
2013-05-05       979.5
2013-05-06      4064.0
2013-05-07     41151.0
2013-05-08      8144.0
2013-05-09        23.0
2013-05-10        10.0
dtype: float64

```

You can use `df[["A", "B"]].mean(axis=1)` if there are other columns to ignore. you would need something like this then pull a list of people in that database after the ammount of time 
 /**
 * @event discordmessagereaction
 */
 $.bind('discordmessagereaction', function (event) {
 var reactionevent = event.getevent(),
 reactionuser = reactioneve… Hi, Really hard to work this out from your query but I suspect the answer would be a correlated sub query to find the latest appointment date.



https://www.geeksforgeeks.org/sql-correlated-subqueries/ hi david, thank you for question. these are called alerts, and show payments that have come into and out of your account. the cards you see should be dragged and dropped on the slideby grid in the row for that particular customer or supplier (it doesn't matter which date, as slideby will automatically move this to the correct incoming/outgoing payment date). please visit this page to learn more about alerts and how these can be matched to any invoices and estimates you have created. You could try using the in line view like this:


Select table1.col1, table1.col2, table1…., table2.col1, table2.col2, table2….  

 from  

(SELECT *  

FROM dbo.Shaft\_Scales  

WHERE dbo.Shaft\_Scales.ID = (SELECT max(ID) from dbo.Shaft\_Scales ID) as table1,  

(SELECT *  

FROM OtherTable  

WHERE dbo.OtherTable.ID = (SELECT max(ID) from dbo.OtherTable ID) as table2


—————- another approach from meandyou 


This works on [at least some] SQL processors


SELECT T1.* , T2.*  

FROM table1 T1, table2 T2  

WHERE …


Do we have to discuss why not to use “SELECT * ” in your SQL?<pre>


Try this


SELECT * FROM T1 as a, T2 as b WHERE a.ID = (SELECT max(ID) from T1)  

and b.ID = (SELECT max(ID) from T2);</pre>