Início / dba / Perguntas / 305395
Accepted
Déjà vu
Asked: 2021-12-26 19:02:07 +0800 CST

O tipo de carimbo de data/hora tem milissegundos (ocultos)?

  • 772

A updatedcoluna foi criada como

| updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

um simples SELECTdá

+---------------------+
| updated             |
+---------------------+
| 2021-12-25 00:15:47 |
+---------------------+

Infelizmente, updatednão foi criado como timestamp(3).

Existe alguma maneira de extrair mais precisão de updated? (o MySQL armazena mais precisão do que o segundo, mesmo que mostre apenas segundos?)

mysql-5.7 timestamp

2 respostas

  1. Existe alguma maneira de extrair mais precisão da atualização?

    Não. Não é possível selecionar os dados que estão ausentes na tabela e não podem ser calculados a partir dos dados presentes nas tabelas.

    o MySQL armazena mais precisão que o segundo, mesmo que mostre apenas segundos?

    Não. Ele armazena exatamente o que você pediu. Ele pode armazenar até microssegundos - mas você não solicitou essa precisão.

    Bem, é realmente déjà vu tudo de novo com esta pergunta! Parece haver um bug na implementação do MySQL disso... (quelle surpresa - um bug no MySQL...) pelo menos em dbfiddle.uk e dbfiddle.com. – Vérace - VACINE-SE AGORA

    Você liga NOW()- e obtém a data e hora sem milissegundos. Ligue NOW(3)ou NOW(6)e obtenha um valor mais preciso.

    https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ccaf9b2c25e372c7bf0ca82c66ded787

    Bem, boas notícias do campo MySQL - você não precisa se preocupar com valores fracionários de um segundo - 5.7 não os suporta - Vérace - VACINE-SE AGORA

    Nunca consulte a pessoa do campo MySQL que disse esse absurdo novamente.

    • 4
  2. Best Answer

    <TL;DR>

    Para responder a pergunta primeiro:

    • Existe alguma maneira de extrair mais precisão da atualização? (o MySQL armazena mais precisão do que o segundo, mesmo que mostre apenas segundos?)

    Minha pesquisa sugere fortemente que não há como recuperar mais dados do que os originalmente armazenados no campo - que era TIMESTAMP(0)- o valor padrão de ( p) se nenhuma precisão for especificada (veja a discussão abaixo).

    A razão pela qual isso pode ser afirmado com alguma certeza é que, se observarmos os requisitos de armazenamento para vários tipos aqui , você verá o seguinte:

    Data Type   Storage requirement
Timestamp   4 bytes + fractional seconds storage

    e logo abaixo disso, você verá:

    insira a descrição da imagem aqui

    Então, o que estou tirando disso é que, se você optar por não armazenar TIMESTAMPs especificamente com uma precisão maior que 0, perderá toda a precisão mais alta porque nenhum espaço é reservado para essa precisão extra.

    Uma resposta completamente definitiva exigiria um editor hexadecimal e diversão para toda a família explorando os .ibdarquivos e/ou uma varredura no código-fonte do MySQL, ambos um pouco acima do meu salário! Eu acho que você pode considerar que sua precisão extra se foi, ou melhor, que nunca existiu em primeiro lugar!

    </TL;DR>

    Tendo cometido alguns erros nos comentários (já excluídos), decidi realmente testar (como sugerido em seus comentários em sua própria resposta) minhas afirmações ( mea culpa, mea maxima culpa ).

    Fico feliz em informar que estava correto sobre o problema de arredondamento - se alguém inserir um tempo com 6 segundos de precisão, o valor será arredondado e não truncado.

    Todo o código abaixo (a menos que indicado de outra forma) está disponível no violino aqui .

    MySQL TIMESTAMPe fusos horários!

    Para MySQL, TIMESTAMPs são armazenados como segundos desde UTC - daqui :

    Um carimbo de data/hora. O intervalo é '1970-01-01 00:00:01.000000' UTC a '2038-01-19 03:14:07.999999' UTC. Os valores TIMESTAMP são armazenados como o número de segundos desde a época ('1970-01-01 00:00:00' UTC).

    Como um aparte, há outro violino aqui que mostra a diferença no MySQL entre TIMESTAMPe DATETIME- o último tipo de dados não possui fusos horários.

    Eu recomendo fortemente que você (ou qualquer desenvolvedor) sempre use o UTC para armazenar os horários sempre que possível - é mais simples, mais limpo e o horário de verão (DST) é cuidado para você pelo servidor.

    Além disso, se você for obrigado a usar fusos horários locais, não use um deslocamento (+07:00 no violino) porque você perderá qualquer informação de horário de verão - a razão pela qual eu fiz isso é porque a implementação do MySQL do db<>fiddle não suporta fusos horários (veja aqui ), mas suporta deslocamentos.

    O procedimento de instalação do MySQL cria as tabelas de fuso horário, mas não as carrega.

    Mais sobre MySQL TIMESTAMP:

    Enfim, a primeira coisa que me jogou foi o fato de que TIMESTAMPsem precisão é TIMESTAMP(0), mas isso está no manual :

    Um valor fsp opcional no intervalo de 0 a 6 pode ser fornecido para especificar a precisão de segundos fracionários. Um valor de 0 significa que não há parte fracionária. Se omitido, a precisão padrão é 0.

    e daqui :

    (Isso difere do padrão SQL padrão de 6, para compatibilidade com versões anteriores do MySQL.)

    Justo o suficiente, mas eles pelo menos [could | should]têm uma configuração do Modo SQL do Servidor para substituir isso e alterá-lo para o comportamento do SQL Standard - eles fizeram isso antes para uma pilha de outros MySQL-ismos!

    Outro complicado (mas novamente documentado ) é que ROUNDing ocorre quando você tenta espremer uma fração de precisão mais alta em um campo de precisão mais baixa:

    Inserir um valor TIME, DATE ou TIMESTAMP com uma parte de segundos fracionários em uma coluna do mesmo tipo, mas com menos dígitos fracionários, resulta em arredondamento.

    Comportamento do MySQL:

    Então, agora, vamos examinar o que o comportamento realmente é da seguinte forma:

    CREATE TABLE test 
(
  s TIMESTAMP,
  t TIMESTAMP(0) NULL DEFAULT NULL,
  u TIMESTAMP(1) NULL DEFAULT NULL,
  v TIMESTAMP(2) NULL DEFAULT NULL,
  w TIMESTAMP(3) NULL DEFAULT NULL,
  x TIMESTAMP(4) NULL DEFAULT NULL,
  y TIMESTAMP(5) NULL DEFAULT NULL,
  z TIMESTAMP(6) NULL DEFAULT NULL
);

    e, em seguida, preencha-o:

    INSERT INTO test VALUES -- insert the same value 8 times into the different fields!
(
  '2021-12-27 10:30:15.567899',
  '2021-12-27 10:30:15.567899', '2021-12-27 10:30:15.567899', 
  '2021-12-27 10:30:15.567899', '2021-12-27 10:30:15.567899',
  '2021-12-27 10:30:15.567899', '2021-12-27 10:30:15.567899',
  '2021-12-27 10:30:15.567899'
);

    e então examinamos nossos dados:

    SELECT 's' AS dt, s  AS ts FROM test  UNION
SELECT 't',       t        FROM test  UNION
SELECT 'u',       u        FROM test  UNION
SELECT 'v',       v        FROM test  UNION
SELECT 'w',       w        FROM test  UNION
SELECT 'x',       x        FROM test  UNION
SELECT 'y',       y        FROM test  UNION
SELECT 'z',       z        FROM test;

    Resultado:

    dt  ts
s   2021-12-27 10:30:16.000000
t   2021-12-27 10:30:16.000000
u   2021-12-27 10:30:15.600000
v   2021-12-27 10:30:15.570000
w   2021-12-27 10:30:15.568000
x   2021-12-27 10:30:15.567900
y   2021-12-27 10:30:15.567900
z   2021-12-27 10:30:15.567899

    Assim, podemos ver que (conforme documentado) TIMESTAMPe TIMESTAMP(0)(que são os mesmos de qualquer maneira) são arredondados de 15.567899para 16e que esse arredondamento continua na linha dependendo da precisão especificada - conforme o manual!

    Então, como remediar isso?

    Existem duas possibilidades - a primeira é que você está fazendo medições TIMESTAMP(6) antigas de algum lugar e precisa truncar (e não arredondá -las - veja o violino principal ), e/ou possivelmente que, daqui para frente, você queira use TIMESTAMP(3)como seu tipo de campo e NOW(3)como sua entrada ( violino diferente ).

    • Medições de TIMESTAMP(6):

    Isso exigirá truncar os valores no TIMESTAMP(6)campo. Isso se mostrou mais complicado do que pode parecer - veja o violino para os estágios, mas o melhor que consegui foi:

    TIMESTAMP(SUBSTRING(DATE_FORMAT(ts, '%Y-%m-%d %H:%i:%s.%f'), 1, 23))

    Isso pode parecer um pouco como andar pelas casas, mas a DATE_FORMATfunção MySQL tem uma granularidade de microssegundos, mas (estranhamente) não de milissegundos! Qualquer sugestão para uma solução mais simples e elegante é bem-vinda!

    Este valor pode então ser inserido em ap = 3 TIMESTAMP(veja fiddle ).

    • a função AGORA():

    O ( manual ) diz:

    Se o argumento fsp for fornecido para especificar uma precisão de segundos fracionários de 0 a 6, o valor de retorno incluirá uma parte de segundos fracionários desses muitos dígitos.

    Então, dias felizes. E verificando ( violino ):

    CREATE TABLE now_test 
(
  s TIMESTAMP,
  t TIMESTAMP(0) NULL DEFAULT NULL,
  u TIMESTAMP(1) NULL DEFAULT NULL,
  v TIMESTAMP(2) NULL DEFAULT NULL,
  w TIMESTAMP(3) NULL DEFAULT NULL,
  x TIMESTAMP(4) NULL DEFAULT NULL,
  y TIMESTAMP(5) NULL DEFAULT NULL,
  z TIMESTAMP(6) NULL DEFAULT NULL
);

    Insira oito NOW(p)valores:

    INSERT INTO now_test VALUES
(NOW(), NOW(0), NOW(1), NOW(2), NOW(3), NOW(4), NOW(5), NOW(6));

    E depois:

    SELECT 'NOW()' , 's' AS dt, s  AS ts FROM now_test  UNION
SELECT 'NOW(0)', 't',       t        FROM now_test  UNION
SELECT 'NOW(1)', 'u',       u        FROM now_test  UNION
SELECT 'NOW(2)', 'v',       v        FROM now_test  UNION
SELECT 'NOW(3)', 'w',       w        FROM now_test  UNION
SELECT 'NOW(4)', 'x',       x        FROM now_test  UNION
SELECT 'NOW(5)', 'y',       y        FROM now_test  UNION
SELECT 'NOW(6)', 'z',       z        FROM now_test;

    Resultado:

    NOW()   dt  ts
NOW()   s   2021-12-27 19:28:38.000000
NOW(0)  t   2021-12-27 19:28:38.000000
NOW(1)  u   2021-12-27 19:28:38.600000
NOW(2)  v   2021-12-27 19:28:38.620000
NOW(3)  w   2021-12-27 19:28:38.628000
NOW(4)  x   2021-12-27 19:28:38.628700
NOW(5)  y   2021-12-27 19:28:38.628760
NOW(6)  z   2021-12-27 19:28:38.628767

    Podemos ver no exemplo acima que se o arredondamento estivesse ocorrendo, o wcampo ( NOW(3)) seria arredondado para 19:28:38.629devido ao seguinte 7. Algumas execuções do violino podem ser necessárias para que isso se torne óbvio!

    Tamanho no disco:

    Tentei encontrar uma consulta para obter os tamanhos individuais dos campos do MySQL no disco, mas o MySQL aparentemente não expõe essas informações - pelo menos não através de sua interface SQL. PostgreSQL tem a pg_column_size()função e também tem a pageinspectextensão contrib para quem gosta de mexer com dumps hexadecimais e bit-twiddling .

    O melhor que eu consegui, com exceção de obter um editor hexadecimal e/ou cavar o código-fonte do MySQL foi isso (veja o final do violino):

    SELECT
  table_name  AS "The table",
  data_length AS "Data length"
FROM
  information_schema.tables
WHERE
    TABLE_NAME = 'test'
ORDER BY
  1 + 2
DESC;

    Resultado:

    The table   Data length
     test         16384

    Então, 16kB para 8 registros. Uma pesquisa rápida revela ( manual ):

    Cada tablespace consiste em páginas de banco de dados. Cada tablespace em uma instância MySQL tem o mesmo tamanho de página. Por padrão, todos os tablespaces têm um tamanho de página de 16 KB;

    Na mesma página, há menção a innodb_page_size :

    SHOW VARIABLES LIKE 'innodb_page_size';

    Resultado:

    Variable_name   Value
innodb_page_size    16384

    Portanto, tudo o que a Data Length...consulta acima está nos dizendo é que usamos 1 página de espaço em disco para uma pequena tabela - dificilmente surpreendente e não ajuda muito. Veja minha discussão na <TL;DR>introdução sobre requisitos de armazenamento para ver por que (com toda a probabilidade) seus bits de precisão extras foram para aquele grande balde no céu... de profundis... .

    • 4

relate perguntas