2

I looked on the internet and I found your post on the stackexchange.
I will try to explain in more detail what we really need
I do not know well to write English
It is a system for airporttaxi

id   date       time        direction

7   2014-04-15  21:35:00    to
8   2014-04-15  15:20:00    from
9   2014-04-15  17:35:00    to
10  2014-04-15  08:15:00    to
11  2014-04-15  14:40:00    to
12  2014-04-15  11:35:00    to
13  2014-04-15  14:35:00    from

from / to = from airport / to airport

I need a mysql script we calculate the difference between the time of driving the same day
example

script needs to sort the first time

08:15:00
11:35:00
14:35:00
14:40:00
15:20:00
17:35:00
21:35:00

then calculate the difference between each ride, starting with the first

I want something like this

start        end          dif_min

08:15:00     11:35:00     200
11:35:00     14:35:00     180
14:35:00     14:40:00     5
14:40:00     15:20:00     40
15:20:00     17:35:00     135
17:35:00     21:35:00     240
21:35:00     00:00:00     25

I much appreciate it if you help me, and I hope you understand what I want

  • With TIMEDIFF() you can calculate the time between two entries. – frlan Apr 15 '14 at 9:17
  • Why do not you read from the beginning before you write an answer ? – Pinelo de la Koliba Apr 15 '14 at 9:21
  • 1
    What query did you come up with yourself? – Colin 't Hart Apr 15 '14 at 9:43
  • I want this willtaxi.at/mysql.jpg – Pinelo de la Koliba Apr 15 '14 at 9:58
  • Your data is a little inaccurate. For id 10, the date should be 2014-04-15 not 2014-04-13 since the display is for the same day. – RolandoMySQLDBA Apr 15 '14 at 15:44
1

THIS IS THE QUERY YOU NEED

SET @GivenDate = '2014-04-15';
SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
FROM airport_taxi WHERE date=@GivenDate;
SET @nexttime = 0;
SELECT
    TIME(FROM_UNIXTIME(StartTime)) "start",
    TIME(FROM_UNIXTIME(EndTime)) "end",
    FLOOR((EndTime - StartTime)/60) diff_min
FROM (SELECT (@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
FROM (SELECT FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60) 
;

SAMPLE DATA

mysql> use test
Database changed
mysql> drop table if exists airport_taxi;
Query OK, 0 rows affected (0.13 sec)

mysql> create table airport_taxi
    -> (id int not null auto_increment primary key,
    -> date date,time time,direction varchar(10));
Query OK, 0 rows affected (0.38 sec)

mysql> insert into airport_taxi values
    -> ( 7,'2014-04-15','21:35:00','to'),
    -> ( 8,'2014-04-15','15:20:00','from'),
    -> ( 9,'2014-04-15','17:35:00','to'),
    -> (10,'2014-04-15','08:15:00','to'),
    -> (11,'2014-04-15','14:40:00','to'),
    -> (12,'2014-04-15','11:35:00','to'),
    -> (13,'2014-04-15','14:35:00','from');
Query OK, 7 rows affected (0.21 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from airport_taxi;
+----+------------+----------+-----------+
| id | date       | time     | direction |
+----+------------+----------+-----------+
|  7 | 2014-04-15 | 21:35:00 | to        |
|  8 | 2014-04-15 | 15:20:00 | from      |
|  9 | 2014-04-15 | 17:35:00 | to        |
| 10 | 2014-04-15 | 08:15:00 | to        |
| 11 | 2014-04-15 | 14:40:00 | to        |
| 12 | 2014-04-15 | 11:35:00 | to        |
| 13 | 2014-04-15 | 14:35:00 | from      |
+----+------------+----------+-----------+
7 rows in set (0.00 sec)

QUERY EXECUTED

mysql> SET @GivenDate = '2014-04-15';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
    -> FROM airport_taxi WHERE date=@GivenDate;
Query OK, 1 row affected (0.00 sec)

mysql> SET @nexttime = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     TIME(FROM_UNIXTIME(StartTime)) "start",
    ->     TIME(FROM_UNIXTIME(EndTime)) "end",
    ->     FLOOR((EndTime - StartTime)/60) diff_min
    -> FROM (SELECT (@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
    -> FROM (SELECT FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
    -> FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
    -> UNION SELECT
    ->     TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    ->     FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    ->     UNIX_TIMESTAMP(@LastDateTime))/60)
    -> ;
+----------+----------+----------+
| start    | end      | diff_min |
+----------+----------+----------+
| 08:15:00 | 11:35:00 |      200 |
| 11:35:00 | 14:35:00 |      180 |
| 14:35:00 | 14:40:00 |        5 |
| 14:40:00 | 15:20:00 |       40 |
| 15:20:00 | 17:35:00 |      135 |
| 17:35:00 | 21:35:00 |      240 |
| 21:35:00 | 00:00:00 |      145 |
+----------+----------+----------+
7 rows in set (0.00 sec)

mysql>

Just remember to set the @GivenDate to whatever date you need

BTW Your last line is based on midnight the next day. Therefore, the diff_min is 145 not 25. If you have a knock-off time of 10:00 PM at th eairport, then change

UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60) 

to this

UNION SELECT
    TIME(@LastDateTime),TIME(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime + INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60) - 120

Subtracting 120 backs 2 hours away from midnight of the next day makes the last line's end 10:00 PM

Give it a Try !!!

UPDATE 2014-04-16 09:44 EDT

I cannot really test this, but you can try this:

SET @GivenDate = '2014-04-15';
SELECT CONCAT(@GivenDate,' ',MAX(time)) INTO @LastDateTime
FROM airport_taxi WHERE date=@GivenDate;
SELECT id INTO @LastID FROM airport_taxi
WHERE date=@GivenDate ORDER BY time DESC LIMIT 1;
SET @nexttime = 0;
SELECT
    id,
    TIME(FROM_UNIXTIME(StartTime)) "start",
    TIME(FROM_UNIXTIME(EndTime)) "end",
    FLOOR((EndTime - StartTime)/60) diff_min
FROM (SELECT id,(@prevtime:=@nexttime) StartTime,(@nexttime:=dttm) EndTime
FROM (SELECT id,FLOOR(UNIX_TIMESTAMP(CONCAT(date,' ',time))) dttm
FROM airport_taxi WHERE date=@GivenDate ORDER BY time) AA) A WHERE StartTime>0
UNION SELECT @LastID,
    TIME(@LastDateTime),TIME(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND),
    FLOOR((UNIX_TIMESTAMP(DATE(@LastDateTime+INTERVAL 1 DAY) + INTERVAL 0 SECOND) -
    UNIX_TIMESTAMP(@LastDateTime))/60) 
;

I hope this helps !!!

| improve this answer | |
  • Yes, That is what we need. Now I see that I made a mistake in the last row... it works perfectly thanks a lot – Pinelo de la Koliba Apr 15 '14 at 21:10
  • RolandoMySQLDBA, How to select and ID with "start" "stop" and "dif_min" ? – Pinelo de la Koliba Apr 16 '14 at 13:18
  • I added a suggestion to insert the ID on every displayed row. Give it a Try !!! – RolandoMySQLDBA Apr 16 '14 at 13:45
  • last id doubles at the end of the table, but perfect, thanks a lot for your help – Pinelo de la Koliba Apr 16 '14 at 14:51
0

Uuse this to get duration between two times:

select startTime, duration, time, TIME_TO_SEC(TIMEDIFF(time,startTime)) as diff
from idling
limit 25;

select startTime, duration DIV 60 as duration, time, TIMESTAMPDIFF(MINUTE,startTime,time) as diff
from idling
limit 25;
| improve this answer | |

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.