Início / dba / Perguntas / 53482
Accepted
simplifiedDB
Asked: 2013-11-19 05:29:54 +0800 CST

Diferença de data e hora no MySQL quando o formato é Varchar

  • 772

Tenho duas colunas em uma tabela: STOPe START, do tipo varchar.

Eu quero ter a diferença entre as duas colunas e estou usando: 

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

Está me dando null, por causa de diferentes tipos de dados.

Para testar, também tentei:

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

Também é dar null.

O que devo fazer pela diferença? Eu tenho nnúmero de dados e tempo em diferentes formatos.

insira a descrição da imagem aqui

mysql mysql-5.5

1 respostas

  1. Best Answer

    Você realmente precisa unificar todos os carimbos de data e hora em um formato para ambos starte stop.

    Só para dar um exemplo, vou calcular a diferença de tempo para a primeira linha.

    Aqui está o código para calculá-lo:

    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;

    Pronto para ver a diferença? Aqui está:

    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)

    Resposta final

    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)

    Mesmo que isso seja correto, seria ilógico criar um script com esse método complicado para todas as linhas, especialmente porque algumas linhas têm formatos diferentes.

    Portanto, é de seu interesse converter colunas starte em oustopDATETIMETIMESTAMP

    • 0

relate perguntas