Início / user-3703

Mei's questions

Martin Hope
Mei
Asked: 2012-04-13 18:30:48 +0800 CST
  • 0

Eu tenho um banco de dados com dois valores DATETIME e, a partir deles, gero alguns novos valores:

tdiff = timediff(d1,d2)+0
day1 = truncate(tdiff/60.0/60.0/24.0,0)
day2 = datediff(d1,d2)+0

Todas essas três "variáveis" são campos FLOAT no banco de dados (temporário). Eu esperaria isso day1e day2nunca diferiria em mais de 1 ou 2. No entanto, existem certos valores que parecem gerar uma resposta selvagem - e eles tendem a ser para certos valores de tdiff. Calcular o valor de tdiffmostra que os cálculos estão corretos.

Conceitualmente, day2 deve ser a diferença entre as datas dos valores DATETIME e day1 deve ser o número de períodos de 24 horas no mesmo período de tempo.

As maiores diferenças estão no valor máximo para timediff: usando cálculos, o número de períodos de 24 horas é pouco mais de 97. No entanto, usando a função DATEDIFF mostra um intervalo de tempo tão baixo quanto 60 dias. Isso não faz sentido.

Também há grandes diferenças quando TIMEDIFF resulta em 26 períodos de 24 horas: DATEDIFF relata 10 dias - ou quando TIMEDIFF resulta em 30 períodos de 24 horas e DATEDIFF relata 11 dias. Também há diferenças quando TIMEDIFF informa 79 dias, mas DATEDIFF fornece 29 dias.

Aqui está um exemplo de saída - de um script Ruby (e no formato de array Ruby) - com colunas d1,d2,TIMEDIFF,períodos de 24 horas,DATEDIFF:

["2012-04-11 06:15:21", "2012-02-09 22:11:21", "8.38596e+06", "97", "62"]
["2012-04-11 06:15:22", "2012-02-09 22:13:24", "8.38596e+06", "97", "62"]
["2012-04-11 06:15:25", "2012-02-09 22:15:29", "8.38596e+06", "97", "62"]
["2012-04-11 05:47:25", "2012-03-31 05:07:01", "2.64402e+06", "30", "11"]
["2012-04-11 05:47:26", "2012-03-31 05:09:03", "2.64382e+06", "30", "11"]
["2012-04-11 05:47:27", "2012-03-31 05:27:21", "2.64201e+06", "30", "11"]
["2012-04-11 05:48:43", "2012-01-27 16:39:32", "8.38596e+06", "97", "75"]
["2012-04-11 05:49:00", "2012-04-01 19:58:00", "2.2551e+06", "26", "10"]
["2012-04-11 05:49:00", "2012-04-01 20:00:02", "2.25486e+06", "26", "10"]
["2012-04-11 05:49:00", "2012-04-01 20:02:04", "2.25466e+06", "26", "10"]
["2012-04-11 05:49:05", "2012-04-01 20:04:04", "2.2545e+06", "26", "10"]
["2012-04-11 05:49:06", "2012-04-01 20:06:06", "2.2543e+06", "26", "10"]
["2012-04-11 05:49:06", "2012-01-27 17:36:07", "8.38596e+06", "97", "75"]
["2012-04-11 05:49:07", "2012-04-01 20:08:10", "2.25406e+06", "26", "10"]
["2012-04-11 06:15:06", "2012-03-13 12:23:24", "6.89514e+06", "79", "29"]
["2012-04-11 06:15:07", "2012-03-13 12:25:25", "6.89494e+06", "79", "29"]

Por que esses cálculos são tão diferentes? Estou assumindo que é uma falta de compreensão do TIMEDIFF, mas o que estou perdendo?

mysql functions
Martin Hope
Mei
Asked: 2012-04-13 12:20:58 +0800 CST
  • 3

Estou tentando entender como comparar um valor DATETIME (ostensivamente inserido como GMT) e um valor TIMESTAMP (gerado automaticamente na hora INSERT).

Pelo que entendi, DATETIME é definido para o que for definido - e o relatório volta com o mesmo valor, não importa quais fusos horários estejam em vigor no servidor ou cliente.

Na documentação do MySQL, parece que os valores TIMESTAMP são relatados no fuso horário atual, mas armazenados em UTC.

Nessa situação, subtrair o valor DATETIME (evento anterior) do valor TIMESTAMP deve ser possível sem muita conversão de fuso horário - porque DATETIME é GMT e o valor TIMESTAMP é UTC. No entanto, fazer isso com precisão - e entendê-lo - está provando ser um problema.

Tenho situações em que preciso (DATETIME - TIMESTAMP)em minutos e em dias. Se assumirmos que a coluna dé DATETIME e a coluna té TIMESTAMP ... Até agora, tentei:

  • DATEDIFF(d,t)
  • TIMEDIFF(d,t)
  • UNIX_TIMESTAMP(d) - UNIX_TIMESTAMP(t) - 1800(contabilizando o horário de verão)

Nosso fuso horário aqui é US Central Time - atualmente Central Daylight Time - GMT-6 para CST e GMT-5 para CDT atual.

Também parece que DATEDIFF converte ambos os valores em datas e os compara - em vez de comparar o tempo entre os valores e dividir pelo número de 24 horas em cada um. Assim, eu não acho que o DATEDIFF funcionará diretamente para mim de qualquer maneira.

As perguntas específicas, então, são:

  • Como você compara um DATETIME em GMT com um valor TIMESTAMP? Especificamente, encontrar o número de minutos entre os dois, assumindo que o valor TIMESTAMP foi gerado por último.
  • Qual é a melhor maneira de obter a diferença em slots de 24 horas entre os mesmos dois valores? Suponho que seria - usando A1 como resposta à pergunta acima - TRUNCATE(A1 / (24.0 * 60.0))...

UPDATE: Tentei usar a CONVERT_TZfunção com os valores de US/Centrale GMT. No entanto, a CONVERT_TZfunção com tais especificações parece ler a configuração do horário de verão a partir da hora atual do sistema e não da hora lida no campo do banco de dados. Isso significa que quando os dados devem ser considerados como uma data no CST, eles são lidos como CDT.

Continuo pensando que deve ser simples obter a representação UTC de um campoTIMESTAMP - o que simplificaria as coisas, me parece.

ATUALIZAÇÃO 2: Parece-me que eu falei errado sobre CONVERT_TZ. Estou usando esta função e parece funcionar:

TIMEDIFF(CONVERT_TZ(t),'US/Central','UTC'),d)

Mesmo com uma mudança de fuso horário (de CST para CDT), parece funcionar ...

mysql datatypes