Order_number T_Calc_Time
2013040100086600 09:16:34
2013040100146880 09:19:13
2013040100406920 09:35:30
2013040100893120 10:13:09
2013040100086600 10:15:14
2013040101906200 12:07:59
2013040100146880 14:48:22
2013040103401460 15:06:34
2013040103407980 15:07:16
2013040103401460 15:09:11
2013040103407980 15:24:22
select Order_number, count(Order_number),T_Calc_Time,TIME_TO_SEC(TIMEDIFF(T_Calc_Time,T_Calc_Time)) from cash_order01 group by Order_number into outfile '/tmp/ordernumber1.csv';
I have to find out that I the order number come again then i have to calculate the difference between those times.
for example the order 2013040100086600 come at 09:16:34 and leave at 10:15:14 i have find the difference and print for each order. If the order comes only once then we have say it is gone to next day.
can any one help me?
For this, you will have to create a stored procedure in MySQL in which you will iterate through the list, select records for the currently reviewed order number and use the TIMEDIFF() MySQL function to calculate the difference in seconds between the min() and max() time-stamp.
Wouldn't it be much easier to have two columns for "come" and "leave". Then you can use the "Order_number" as a primary key, which appears only ones (more efficient) and you don't need to look for all the appaerances of the order number and simply calculate form the columns "come" and "leave" with TIMEDIFF() the time difference and if "leave" is empty, than you already know, that you can't compute the time difference. But to be honest this is quite basic stuff, which you can find in "MySQL"-Textbooks.
He would have to create a view or sub-query that would group the records by Order_number and select the min(T_Calc_Time) as come and max(T_Calc_Time) as leave and then calculate the difference with TIMEDIFF().
This is a valid approach, but keep in mind that he has 15M+ records and creating an additional view of executing an additional sub-query would consume way too much time.
First thing the data is big and I got one day data (15GB)only, after a month I have to process a 6 months data. Second I am new to DBMS implementation. I am following DBMS by raghu ramakrishnan and MySQL cookbook by paul dubois(Completed 9 chapter still 11 chapters to go)
So I simply used this query on very small data(11 records data) it produce only 8 records. and in large data it produced large number of records than original records.
select a.Order_number,b.Order_number, a.T_Calc_Time,b.T_Calc_Time, timediff(greatest(a.T_Calc_Time,b.T_Calc_Time),least(a.T_Calc_Time,b.T_Calc_Time)) from ankit as a inner join ankit as b on a.Order_number=b.Order_number where a.T_Calc_Timeb.T_Calc_Time.
I am still trying to implement Mr. Milan Tair advice. Thank you very much Mr.Milan Tair for helping me.
Try this:
SELECT
a.order_number,
Min(a.t_calc_time) start,
Max(a.t_calc_time) end,
Timediff(MAX(a.t_calc_time), Min(a.t_calc_time)) time_difference
FROM
ankit AS a
GROUP BY
a.order_number;
It should work better and faster.
Try to limit the query for, say, a 100 records with LIMIT 0, 100 (at the end of the query) to see if the results are good?.
- Badges
- Science topic
- Similar topics
- Mathematical Sciences
- Graphs