Datetime Difference in MySQL when format is Varchar I have two columns in a table: STOP and START, of type varchar.

I want to have the difference between both columns, and I'm using :  

SELECT DATEDIFF(stop, start) AS difference from T1;


It is giving me null, because of different data types.

To test, I also tried: 

SELECT STR_TO_DATE('5/16/2011 20:14 PM', '%c %e %Y %H:%i:%s');


It is also giving null.

What should I do for the difference? I have n number of data and time in different formats.
You really need to unify all Date and Time Stamps into one format for both start and stop.

Just to give you an example, I'll compute the time difference for the first row.

Here is the code to compute it:

SET @StartDT = 'Mon Jan 21, 21:07:47 IST 2013';
SET @StopDT  = 'Tue Jan 22, 07:59:58';
SET @YYYY_Start = SUBSTR(@StartDT,-4);
SET @MMM_Start  = RIGHT(LEFT(@StartDT,7),3);
SET @DD_Start   = RIGHT(LEFT(@StartDT,10),2);
SET @HMS_Start  = RIGHT(LEFT(@StartDT,20),8);
SET @YYYY_Stop  = SUBSTR(@StartDT,-4);
SET @MMM_Stop   = RIGHT(LEFT(@StopDT,7),3);
SET @DD_Stop    = RIGHT(LEFT(@StopDT,10),2);
SET @HMS_Stop   = RIGHT(LEFT(@StopDT,20),8);
SET @MM_Start   = RIGHT(LEFT((LOCATE(@MMM_Start,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
SET @MM_Stop    = RIGHT(LEFT((LOCATE(@MMM_Stop,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
SELECT @YYYY_Start,@YYYY_Stop,@MM_Start,@MM_Stop,@DD_Start,@DD_Stop,@HMS_Start,@HMS_Stop,@MM_Start,@MM_Stop;
SET @YYYMMDD_Start = CONCAT(@YYYY_Start,'-',@MM_Start,'-',@DD_Start);
SET @YYYMMDD_Stop  = CONCAT(@YYYY_Stop,'-',@MM_Stop,'-',@DD_Stop);
SET @TS_Start = CONCAT(@YYYMMDD_Start,' ',@HMS_Start);
SET @TS_Stop  = CONCAT(@YYYMMDD_Stop,' ',@HMS_Stop);
SET @TimeDiffSec = UNIX_TIMESTAMP(@TS_Stop) - UNIX_TIMESTAMP(@TS_Start); 
SET @TimeDiff = SEC_TO_TIME(@TimeDiffSec); 
SELECT @TS_Start,@TS_Stop,@TimeDiffSec,@TimeDiff;


Ready to see the difference? Here it is:

mysql&gt; SET @StartDT = 'Mon Jan 21, 21:07:47 IST 2013';
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @StopDT  = 'Tue Jan 22, 07:59:58';
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @YYYY_Start = SUBSTR(@StartDT,-4);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @MMM_Start  = RIGHT(LEFT(@StartDT,7),3);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @DD_Start   = RIGHT(LEFT(@StartDT,10),2);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @HMS_Start  = RIGHT(LEFT(@StartDT,20),8);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @YYYY_Stop  = SUBSTR(@StartDT,-4);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @MMM_Stop   = RIGHT(LEFT(@StopDT,7),3);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @DD_Stop    = RIGHT(LEFT(@StopDT,10),2);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @HMS_Stop   = RIGHT(LEFT(@StopDT,20),8);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @MM_Start   = RIGHT(LEFT((LOCATE(@MMM_Start,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @MM_Stop    = RIGHT(LEFT((LOCATE(@MMM_Stop,'JanFebMarAprMayJunJulAugSepOctNovDev')+2)/3+100,3),2);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT @YYYY_Start,@YYYY_Stop,@MM_Start,@MM_Stop,@DD_Start,@DD_Stop,@HMS_Start,@HMS_Stop,@MM_Start,@MM_Stop;
+-------------+------------+-----------+----------+-----------+----------+------------+-----------+-----------+----------+
| @YYYY_Start | @YYYY_Stop | @MM_Start | @MM_Stop | @DD_Start | @DD_Stop | @HMS_Start | @HMS_Stop | @MM_Start | @MM_Stop |
+-------------+------------+-----------+----------+-----------+----------+------------+-----------+-----------+----------+
| 2013        | 2013       | 01        | 01       | 21        | 22       | 21:07:47   | 07:59:58  | 01        | 01       |
+-------------+------------+-----------+----------+-----------+----------+------------+-----------+-----------+----------+
1 row in set (0.00 sec)

mysql&gt; SET @YYYMMDD_Start = CONCAT(@YYYY_Start,'-',@MM_Start,'-',@DD_Start);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @YYYMMDD_Stop  = CONCAT(@YYYY_Stop,'-',@MM_Stop,'-',@DD_Stop);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @TS_Start = CONCAT(@YYYMMDD_Start,' ',@HMS_Start);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @TS_Stop  = CONCAT(@YYYMMDD_Stop,' ',@HMS_Stop);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @TimeDiffSec = UNIX_TIMESTAMP(@TS_Stop) - UNIX_TIMESTAMP(@TS_Start);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @TimeDiff = SEC_TO_TIME(@TimeDiffSec);
Query OK, 0 rows affected (0.00 sec)


Final Answer

mysql&gt; SELECT @TS_Start,@TS_Stop,@TimeDiffSec,@TimeDiff;
+---------------------+---------------------+--------------+-----------+
| @TS_Start           | @TS_Stop            | @TimeDiffSec | @TimeDiff |
+---------------------+---------------------+--------------+-----------+
| 2013-01-21 21:07:47 | 2013-01-22 07:59:58 |        39131 | 10:52:11  |
+---------------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)


Even though this is right, it would be illogical to script this convoluted method for all rows, especially since some rows have different formats.

Therefore, it is in your best interest to convert start and stop columns into DATETIME or TIMESTAMP