Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

enter image description here

share|improve this question
1  
Why are you storing dates as strings? There is a date and a datetime datatype. –  ypercube Nov 18 '13 at 15:35
    
I have three questions: 1) Is the time format for the start column in every row the same format? 2) Is the time format for the stop column in every row the same format? 3) What is the year for the stop column? (I ask this because if you start 2013-12-31 23:59:59 and stop 2014-01-01 00:00:01, this implies the Year 2014) –  RolandoMySQLDBA Nov 18 '13 at 16:05
    
@ypercube - because format is entirely different that we have in datetime and timestamp datatype..i tried this one as well , but couldnt find any difference and such data is incremental data , increasing to lakh and crores.. –  user21546 Nov 18 '13 at 18:21
    
@RolandoMySQLDBA - 1. Yes 2. Same 3. Oops , there is no year , we have to add current year here as well.. –  user21546 Nov 18 '13 at 18:24
    
well we can assume 2013 year in stop column –  user21546 Nov 18 '13 at 18:25

1 Answer 1

up vote 0 down vote accepted

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> SET @StartDT = 'Mon Jan 21, 21:07:47 IST 2013';
Query OK, 0 rows affected (0.00 sec)

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

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

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

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

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

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

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

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

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

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

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

mysql> 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> SET @YYYMMDD_Start = CONCAT(@YYYY_Start,'-',@MM_Start,'-',@DD_Start);
Query OK, 0 rows affected (0.00 sec)

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

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

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

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

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

Final Answer

mysql> 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

share|improve this answer
    
well..a long explanation..thanks for your assistance..i want to appreciate ..can you please tell me how should i use format as per me..for ex. as in datetime & timestamp data type, format is specific...and if i need to customize it..how should i do it ?? also if i need to insert any date's format then it should insert in customized format only...how should i do this –  user21546 Nov 21 '13 at 5:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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