Início / dba / Perguntas / 305189
Accepted
Dolphin
Asked: 2021-12-21 01:19:20 +0800 CST

como calcular o ano diff com formato de data e hora não padrão no mysql

  • 772

Meu banco de dados (MySQL 5.7) armazena o formato de aniversário do usuário assim 1990年1月1日, agora quero calcular a idade do usuário a partir desse formato datetime. Meu sql se parece com isso, mas retorna null para todos os dados:

select period_diff(date_format(now(), '%Y'), date_format(birthday, '%Y年')) as months 
from spark_user;

a tabela DDL fica assim:

CREATE TABLE `spark_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `birthday` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `index_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4;

Eu também tentei assim:

select period_diff(date_format(now(), '%Y'), date_format(STR_TO_DATE(birthday, '%Y'), '%Y')) as months 
from spark_user;

isso funciona:

    select period_diff( date_format(STR_TO_DATE(birthday, '%Y年%m月%d日'), '%Y'),date_format(now(), '%Y')) as months,
birthday as birthday 
from spark_user;

mas o resultado é estranho:

57  1990年1月1日
57  1990年1月1日
57  1990年1月1日
57  1990年10月10日
47  1980年1月1日
-20 2001年1月1日
63  1996年10月10日
mysql

3 respostas

  1. armazene o formato de aniversário do usuário assim ...

    Uma das muitas regras não escritas sobre bancos de dados relacionais -

    As datas não têm formato ...

    ... ou, pelo menos, nenhum com o qual você ou eu precisemos nos preocupar.

    Se você acha que suas datas têm um "formato", então você está olhando quase certamente para dados de caracteres , não para datas apropriadas e, como você descobriu, é difícil tentar trabalhar com dados de caracteres que contêm valores de data .

    Sempre use o tipo de dados certo para o trabalho certo.

    Claro, STR_TO_DATE converte um valor de caractere em um valor de Data , mas faz isso de acordo com as próprias regras do banco de dados.
    Quando, exatamente, é 01/04/07? Janeiro? Abril? 2001 ou 2007?
    (Você encontrará aquele que funciona para você e tudo ficará bem - até que você tenha que portar seu banco de dados para algum outro DBMS, com regras diferentes e todo o inferno se solta).
    Além disso, usar uma função dessa maneira impede que o banco de dados use qualquer índice que você tenha nesse campo - Verificação de tabela, aqui vamos nós.

    • 1
  2. Best Answer
    YEAR(CURDATE()) - LEFT(birthday, 4)

    Obs: Será desativado por até 1 ano, dependendo da data atual e de quando no ano em que nasceram.

    • 1

  3. Eu uso este comando sql, funciona:

    select YEAR(now()) - YEAR(STR_TO_DATE(birthday, '%Y年%m月%d日'))
    • 0

relate perguntas