Formatos de data e hora no MySQL

O MySQL tem tipos de campos específicos para representar data e hora: DATE e TIME, respectivamente.
Para representar valores combinados dos dois tipos, é possível usar o DATETIME.
Estes valores podem ser armazenados e apresentados nos seguintes formatos:

  • Os valores DATE são manipulados como strings no formato CCYY-MM-DD — onde CC, YY, MM e DD representam o século (century), o ano (year), o mês e o dia do mês.
  • Os valores TIME são representados por strings, no formato hh:mm:ss — hora (hh), minutos (mm) e segundos (ss).
    Os valores TIME podem ser entendidos como referentes à hora do dia. Na verdade, o MySQL os trata como tempo decorrido — de forma que um campo com este tipo de dado pode conter valores superiores a 23:59:59 ou, até mesmo, negativos.
    Caso você se interesse em saber, estes valores podem variar entre -838:59:59 e 838:59:59.
  • Valores DATETIME são strings combinadas de data e hora: CCYY-MM-DD hh:mm:ss.
  • O TIMESTAMP inclui data e hora, mas é representado em uma string CCYYMMDDhhmmss.
    Este tipo de campo possui algumas propriedades especiais e tem valores um pouco mais difíceis de se ler, à primeira vista. Como regra geral, contudo, você pode tratar os campos TIMESTAMP da mesma forma como trata os DATETIME.

No exemplo, abaixo, veja a representação dos campos ‘hora_saida’ e ‘hora_chegada’, da tabela ‘viagem’:

SELECT hora_saida, hora_chegada
    -> FROM viagem;
+------------------+--------------------+
| hora_saida       | hora_chegada       |
+------------------+--------------------+
|         15:00:00 |           15:00:00 |
|         05:01:30 |           02:30:20 |
|         12:30:20 |           17:30:45 |
+------------------+--------------------+

As datas, podem ser representadas, dentro da tabela, da seguinte forma:

SELECT data_saida
    -> FROM viagem;

Não se esqueça que as datas estão no formato ANO-MÊS-DIA:

+------------+
| data_saida |
+------------+
| 2015-02-28 |
| 2015-01-15 |
| 2015-03-05 |
| 2014-12-31 |
| 2015-06-04 |
+------------+

Os campos gravados com valores do tipo DATETIME, na minha tabela registros estão representados da seguinte forma:

SELECT data_nascimento
    -> FROM registros;
+---------------------+
|     data_nascimento |
+---------------------+
| 1970-01-01 00:00:00 |
| 1987-03-05 12:30:15 |
| 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 |
+---------------------+

Estes mesmos valores, se usado o formato do tipo TIMESTAMP, serão representados da seguinte forma:

SELECT data_nascimento
    -> FROM registros;
+------------------+
|  data_nascimento |
+------------------+
|   19700101000000 |
|   19870305123015 |
|   19991231090000 |
|   20000604154530 |
+------------------+

Como você pode ver, a leitura da informação TIMESTAMP é um pouco difícil, mas é um formato perfeito para nomear arquivos ou ser livremente formatado pelo desenvolvedor, através de funções de tratamento de strings.

Como lidar com os formatos de dados de data e hora no MySQL

O formato CCYY-MM-DD, usado pelo MySQL para armazenar dados referentes a data (DATE) segue o padrão ISO 8601. Este formato tem a vantagem de — uma vez que o ano, o mês e o dia serem representados com tamanhos fixos e da esquerda para direita – compor uma string que naturalmente é organizável em ordem temporal. Você pode usar este formato para compor nomes de arquivos que, ao ser listados em ordem alfabética, já ficarão naturalmente organizados também na ordem temporal.

Não é possível alterar a forma com o MySQL grava dados em campos DATE. Se você usa outro formato no seu programa, poderá usar uma função como a DATE_FORMAT() para adequar a string de data ao respectivo campo

Não é possível armazenar datas no formato DD/MM/YY (Dia/Mês/Ano). A função DATE_FORMAT também pode ser usada para formatar a exibição da data.
Alguns programadores optam por armazenar seus dados de datas como strings, em campos do tipo CHAR, comuns e lidam com as datas dentro de seus scripts — mas aí perde-se a possibilidade de fazer operações, dentro do banco de dados, com as datas (que pode trazer ganhos de performance para o sistema).
Quando a string de data já se encontra em formato semelhante ao requerido pela ISO, ele pode sequer precisar de qualquer tratamento para ser armazenado. A string 94-04-14, já será interpretada como 1994-04-14 pelo MySQL — e será armazenada como tal.
O mesmo vai acontecer se você repassar os valores 940414 ou 19940414.

Tipos de dados do MySQL e MariaDB

Tipos de dados são uma forma de classificar as informações que serão armazenados no banco de dados.
Entender os tipos de dados que podem ser armazenados no banco e a que situações se adequam é importante para projetar um banco de dados o mais eficiente possível. Veja, a seguir, alguns dos tipos de dados mais comuns no MySQL, acompanhado de uma breve descrição.

Antes de definir exatamente o tipo de dado que você deseja definir, vale a pena prestar atenção nas seguintes diretrizes, para ajudar a ter melhores resultados:

  • os tipos de valores que podem ser usados;
  • que tamanhos podem ter
  • as operações que podem ser realizadas;
  • seus significados;
  • se podem/serão indexados
  • como devem ser armazenados etc.

Tipos de dados numéricos no MySQL

O MySQL tem todos os tipos numéricos possíveis, o que inclui exatos, aproximados, inteiros, de ponto fixo, ponto flutuante etc. A lista, abaixo, mostra um resumo dos tipos de dados possíveis no MySQL:

  • TINYINT — número inteiro muito pequeno (tiny);
  • SMALLINT — número inteiro pequeno;
  • MEDIUMINT — número inteiro de tamanho médio;
  • INT — número inteiro de tamanho comum;
  • BIGINT — número inteiro de tamanho grande;
  • DECIMAL — número decimal, de ponto fixo;
  • FLOAT — número de ponto flutuante de precisão simples (32 bits);
  • DOUBLE — número de ponto flutuante de precisão dupla (64 bits);
  • BIT — um campo de um bit.

Tipos de dados em strings

Strings são cadeias de caracteres. No MySQL, uma string pode ter qualquer conteúdo, desde texto simples a dados binários – tais como imagens e arquivos. Cadeias de caracteres podem ser comparadas e ser objeto de buscas.

  • CHAR — uma cadeia de caracteres (string), de tamanho fixo e não-binária;
  • VARCHAR — uma string de tamanho variável e não-binária;
  • BINARY — uma string binária de tamanho fixo;
  • VARBINARY — uma string binária de tamanho variável;
  • BLOB — um BLOB (Binary Large OBject – OBjeto Grande Binário) pequeno;
  • TINYBLOB — um BLOB muito pequeno;
  • MEDIUMBLOB — um BLOB de tamanho médio;
  • LONGBLOB — um BLOB grande;
  • TINYTEXT — uma string não-binária e de tamanho bem reduzido;
  • TEXT — uma string não-binária e pequena;
  • MEDIUMTEXT — uma string de tamanho comum e não-binária;
  • LONGTEXT — uma string não-binária de tamanho grande;
  • ENUM — de acordo com o manual do MySQL, é uma string, com um valor que precisa ser selecionado de uma lista predefinida na criação da tabela;
  • SET — é um objeto que pode ter zero ou mais valores – cada um dos quais precisa ser escolhido de uma lista de valores predeterminados quando da criação da tabela.

Armazenamento de data e hora

Há várias opções para armazenar dados relacionados a data e hora. Se você quiser apenas armazenar o ano referente a um evento, pode usar o tipo YEAR. O tipo TIMESTAMP pode ser usado para acompanhar as mudanças ocorridas em um campo de uma tabela. Veja os tipos e suas descrições:

  • DATE — o valor referente a uma data no formato 'CCYY-MM-DD'. Por exemplo 1985-11-25 (ano-mês-dia). O 'CC' se refere aos dois dígitos do século (Century, em inglês);
  • TIME — um valor horário no formato 'hh:mm:ss' (hora:minutos:segundos);
  • TIMESTAMP — timestamp é uma sequência de caracteres ou informação codificada que identifica uma marca temporal ou um dado momento em que um evento ocorreu. No MySQL, ele tem o formato 'CCYY-MM-DD hh:mm:ss' – neste caso, seguem a padronização ISO 8601;
  • YEAR — armazena um ano no formato 'CCYY' ou 'YY';

Dados espaciais

O MySQL tem suporte a tipos de dados que correspondem às classes OpenGIS. Alguns destes carregam valores geométricos simples:

  • GEOMETRY
  • POINT
  • LINESTRING
  • POLYGON

O GEOMETRY pode armazenar qualquer tipo de valor geométrico. Os outros valores simples (POINT, LINESTRING e POLYGON) têm seus valores restritos aos tipos geométricos a que se referem.
Os outros, que seguem listados, carregam valores relativos a coleções/coletivos:

  • GEOMETRYCOLLECTION
  • MULTILINESTRING
  • MULTIPOINT
  • MULTIPOLYGON

Assim, GEOMETRYCOLLECTION pode armazenar coletâneas de objetos de qualquer tipo. Os outros tipos coletivos (MULTILINESTRING, MULTIPOLYGON e GEOMETRYCOLLECTION) restringem-se a cada forma geométrica particular.