MySql - Formatar data e hora

Serão descritos 2 exemplos de como formatar data e hora.

1º Exemplo: exibir a data no formato dd/mm/aaaa (dia/mês/ano) e a hora no formato (hh:mm):

Solução: vamos utilizar a função date_format para formatar a data e a hora.

date_format(coluna_da_tabela, '%formato')

Quando utilizamos a função date_format colocamos o nome da coluna antes da virgula e formato depois da virgula entre aspas simples. O formato é acompanhado do simbolo de porcentagem.

Na imagem abaixo, vemos a tabela "tb_fornecedor", vamos formatar a data da coluna "data".

Para formatar a data e a hora executamos a sentença abaixo:

SELECT

produto,

quantidade,

valor,

segmento,

/*Na função DATE_FORMAT coloque o nome da coluna antes da virgula e o formato depois da virgula*/

date_format(data, '%d/%m/%Y') AS data,

date_format(data, '%h:%m') AS hora

FROM tb_fornecedor;

Após executarmos a sentença, são exibidas a colunas de data e hora formatadas. Podemos visualizar o resultado na imagem abaixo.

O primeiro exemplo foi resolvido, antes de propormos mais um exemplo, vamos citar mais alguns parâmetros que podem ser passados na função date_time:

• %W: dia da semana por extenso. Exemplo: segunda, terça, quarta e assim por diante;
• %a: dia da semana por extenso abreviado. Exemplo: Seg, Ter, Qua e assim por diante;
• %M: mês por extenso completo. Exemplo: Janeiro, Fevereiro, Março e assim por diante;
• %b: mês por extenso abreviado. Exemplo: Jan, Fev, Mar e assim por diante;
• %s: segundos de 00 até 59;
• %w: dia da semana representado por um inteiro numérico. 0-Domingo, 1-Segunda, e assim por diante. A sequência termina em 6 - Sábado;

Caso, queira consultar mais parâmetros, veja a função date_format no manual do mysql. 
Agora vamos executar a sentença:

SELECT

data,
date_format(data, '%W') AS semana,
date_format(data, '%a') AS sem_abr,

date_format(data, '%M') AS mes,

date_format(data, '%b') AS mes_abr,

date_format(data, '%s') AS segundo,

date_format(data, '%w') AS sem_int

FROM tb_fornecedor;

Após a execução da sentença acima, obtemos o resultado da imagem abaixo:

Mas os dados apareceram em inglês. E agora?

Para que as coluna sejam exibidas em português, ou em outro idioma, devemos configurar a variável de sistema do mysql chamada "lc_time_names". Esta variável especifica o idioma exibido para os meses e dias da semana e foi adicionado a partir do MySQL 5.0.25. O valor padrão desta variável é inglês americano 'en_US'. Há dois tipos de configuração para esta variável: global e local.

Global: quando você altera a variável lc_time_names globalmente,  a alteração se torna padrão para todas as conexões. Para alterar está variável globalmente é necessário ter permissão de administrador do banco;

Local: quando você altera a variável lc_time_names localmente, a alteração só é valida na conexão atual. Por exemplo, caso você altere para o idioma português do brasil, na conexão em que você está utilizando, os dados relativos a data aparecerão em português. Caso você abra uma nova conexão, os dados aparecerão novamente em inglês, a não ser que seja feita uma alteração para a nova conexão também.

Você pode configurar a variável "lc_time_names"  através de uma interface gráfica como por exemplo o "Mysql Query Browser" ou através de "Linha de Comando", vamos descrever os dois modos.

Configurar variável global através da linha de comando

1º passo - abra uma conexão no mysql;

2º passo - verificar qual o valor está armazenado na variável lc_time_names. Para isso utilize o comando:

SHOW GLOBAL VARIABLES like 'lc_time_names';

OU

SELECT @@global.lc_time_names;

3º passo - alterar o valor da lc_time_names, no nosso exemplo, vamos alterar para pt_BR. Para isso utilize o comando:

SET GLOBAL lc_time_names='pt_BR';

OU

SET @@global.lc_time_names='pt_BR';

4º passo - após ter alterado a variável feche a conexão no mysql:

5º passo - abra uma nova conexão:

6º passo - após a execução da sentença os dados aparecerão em português:

Configurar variável local através de linha de comando

1º passo - abra uma conexão no mysql;

2º passo - verificar qual o valor está armazenado na variável "lc_time_names". Para isso utilize o comando:

SHOW LOCAL VARIABLES like 'lc_time_names';

OU

SELECT @@local.lc_time_names;

3º passo - alterar o valor da lc_time_names, no nosso exemplo, vamos alterar para pt_BR. Para isso utilize o comando:

SET LOCAL lc_time_names='pt_BR';

OU

SET @@local.lc_time_names='pt_BR';

4º passo - após a execução da sentença os dados aparecerão em português:

Configurar variável global através de interface gráfica

1º passo - com o "Mysql Query Browser" aberto, digite o comando abaixo, para verificar qual o valor está armazenado na variável lc_time_names.

SHOW GLOBAL VARIABLES like 'lc_time_names';

2º passo - alterar o valor da lc_time_names. Vamos alterar para pt_BR. Após a alteração feche aba que você executou o comando.

SET GLOBAL lc_time_names='pt_BR';

3º passo - Abra uma nova aba e execute novamente a consulta, os dados serão exibidos em português.

SELECT
data,

date_format(data, '%W') AS semana,

date_format(data, '%a') AS sem_abr,

date_format(data, '%M') AS mes,

date_format(data, '%b') AS mes_abr,

date_format(data, '%s') AS segundo,

date_format(data, '%w') AS sem_int

FROM tb_fornecedor;

Configurar variável local através de interface gráfica

1º passo - com o  "Mysql Query Browser" aberto, digite o comando abaixo, para verificar qual o valor está armazenado na variável lc_time_names.

SHOW LOCAL VARIABLES like 'lc_time_names';

2º passo - alterar o valor da lc_time_names. Vamos alterar para pt_BR.

SET LOCAL lc_time_names='pt_BR';

3º passo - Na mesma aba execute novamente a consulta, os dados serão exibidos em português.

SELECT
data,

date_format(data, '%W') AS semana,

date_format(data, '%a') AS sem_abr,

date_format(data, '%M') AS mes,

date_format(data, '%b') AS mes_abr,

date_format(data, '%s') AS segundo,

date_format(data, '%w') AS sem_int

FROM tb_fornecedor;

Agora que já sabemos configurar a variável "lc_time_names" vamos para apresentar o segundo exemplo.

2º Exemplo: exibir a data por extenso. Ao invés de exibir a data como '2013-09-12' queremos exibir como 'quinta, 12 de setembro de 2013'.

Para a data ser exibida neste formato executamos a sentença abaixo:

SELECT
produto,
quantidade,
valor,
segmento,
date_format(data, '%W, %d de %M de %Y') AS data
FROM tb_fornecedor;

Após executarmos a sentença a data será exibida no formato esperado. Veja o resultado na imagem abaixo: