Conteúdo


Fundamentos básicos do DB2

Divirta-se com datas e horários

Comments

Conteúdos da série:

Esse conteúdo é a parte # de # na série: Fundamentos básicos do DB2

Fique ligado em conteúdos adicionais dessa série.

Esse conteúdo é parte da série:Fundamentos básicos do DB2

Fique ligado em conteúdos adicionais dessa série.

Importante: leia a renúncia de responsabilidade antes de ler este artigo.
Este artigo foi escrito para o IBM® DB2® para Linux, UNIX® e Windows®.

Este pequeno artigo é indicado para quem é novo no DB2 e deseja entender como manipular datas e horários. A maioria das pessoas que já trabalharam com outros bancos de dados ficam felizes ao se surpreenderem com a facilidade de trabalhar com o DB2.

Os fundamentos básicos

Para obter a data, a hora e o registro de data e hora atuais usando SQL, consulte os registros do DB2 apropriados:

SELECT current date FROM sysibm.sysdummy1 
SELECT current time FROM sysibm.sysdummy1 
SELECT current timestamp FROM sysibm.sysdummy1

A tabela sysibm.sysdummy1 é uma tabela especial na memória que pode ser usada para descobrir o valor dos registros do DB2, conforme a ilustração acima. Também é possível usar a palavra-chave VALUES para avaliar o registro ou a expressão. Por exemplo, no processador de linha de comandos (CLP) do DB2, as seguintes instruções SQL revelam informações semelhantes:

VALUES current date 
VALUES current time 
VALUES current timestamp

Para os exemplos restantes, vou simplificar fornecendo a função ou a expressão sem repetir SELECT ... FROM sysibm.sysdummy1 ou usando a cláusula VALUES.

Para que o horário atual ou o registro de data e hora atual seja ajustado para GMT/CUT, subtraia o fuso horário atual do horário ou do registro de data e hora atual:

current time - current timezone 
current timestamp - current timezone

Dada uma data, um horário ou um registro de data e hora, é possível extrair (quando se aplicar) partes de ano, mês, dia, hora, minutos, segundos e microssegundos, independentemente, usando a função apropriada:

YEAR (current timestamp) 
MONTH (current timestamp) 
DAY (current timestamp) 
HOUR (current timestamp) 
MINUTE (current timestamp) 
SECOND (current timestamp) 
MICROSECOND (current timestamp)

Extrair a data e a hora de forma independente de um registro de data e hora também é muito fácil:

DATE (current timestamp) 
TIME (current timestamp)

Também é possível executar cálculos de data e hora usando o inglês, devido à falta de um termo melhor:

current date + 1 YEAR 
current date + 3 YEARS + 2 MONTHS + 15 DAYS 
current time + 5 HOURS - 3 MINUTES + 10 SECONDS

Para calcular quantos dias existem entre duas datas, é possível subtrair as datas da seguinte forma:

days (current date) - days (date('1999-10-22'))

E aqui está um exemplo de como obter o registro de data e hora atual com a parte de microssegundos reconfigurada para zero:

CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS

Se desejar concatenar os valores de data e hora com outro texto, será necessário converter o valor em uma sequência de caractere primeiro. Para fazer isso, é possível simplesmente usar a função CHAR():

char(current date) 
char(current time) 
char(current date + 12 hours)

Para converter uma cadeia de caractere em um valor de data ou hora, é possível usar:

TIMESTAMP ('2002-10-20-12.00.00.000000') 
TIMESTAMP ('2002-10-20 12:00:00') 
	DATE ('2002-10-20') 
	DATE ('10/20/2002') 
	TIME ('12:00:00') 
	TIME ('12.00.00')

As funções TIMESTAMP(), DATE() e TIME() aceitam diversos outros formatos. Os formatos acima são apenas exemplos que deixarei como exercício para que o leitor os descubra.

Aviso: o que acontecerá se eu excluir sem querer as aspas da função DATE? A função ainda funcionará, mas o resultado não será correto:

SELECT DATE(2001-09-22) FROM SYSIBM.SYSDUMMY1;

Resposta:

====== 
05/24/0006

Qual o motivo da diferença de 2.000 anos nos resultados acima? Quando a função DATE recebe uma cadeia de caractere como entrada, ela assume que se trata de uma representação válida de caractere de uma data do DB2 e a converte corretamente. Em contrapartida, quando a entrada é numérica, a função assume que ela representa o número de dias menos um desde o início da era atual (ou seja, 0001-01-01). Na consulta acima, a entrada era 2001-09-22, que é igual a (2001-9)-22, que é igual a 1970 dias.

Funções de data

Às vezes, é necessário saber a diferença entre dois registros de data e hora. Para isso, o DB2 fornece uma função integrada chamada TIMESTAMPDIFF(). No entanto, o valor retornado é uma aproximação porque ele não considera anos bissextos e assume somente 30 dias por mês. Aqui está um exemplo de como encontrar a diferença de tempo aproximada entre duas datas:

timestampdiff (<n>, char( 
	timestamp('2002-11-30-00.00.00')- 
	timestamp('2002-11-08-00.00.00')))

No lugar de <n>, use um dos valores a seguir para indicar a unidade de tempo do resultado:

  • 1 = Frações de segundo
  • 2 = Segundos
  • 4 = Minutos
  • 8 = Horas
  • 16 = Dias
  • 32 = Semanas
  • 64 = Meses
  • 128 = Trimestres
  • 256 = Anos

O uso de timestampdiff() é mais preciso quando as datas estão próximas do que quando elas estão distantes. Se você precisar de um cálculo mais preciso, use o seguinte para determinar a diferença de tempo (em segundos):

(DAYS(t1) - DAYS(t2)) * 86400 + 
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2))

Para facilitar, também é possível criar uma função SQL definida pelo usuário daquilo que foi citado acima:

CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP) 
RETURNS INT 
RETURN ( 
(DAYS(t1) - DAYS(t2)) * 86400 + 
(MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2)) 
) 
@

Se você precisar determinar se um ano é um ano bissexto, aqui está uma função SQL útil que pode ser criada para determinar o número de dias em um determinado ano:

CREATE FUNCTION daysinyear(yr INT) 
RETURNS INT 
RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE 
        CASE (mod(yr, 4))   WHEN 0 THEN 
        CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END 
        ELSE 365 END 
	END)@

Finalmente, aqui está um gráfico de funções integradas para manipulação de data. A intenção é ajudá-lo a identificar rapidamente uma função que possa se adequar às suas necessidades, não fornecer uma referência completa. Consulte a Referência de SQL para obter mais informações sobre essas funções.

As funções SQL de Data e Hora são as seguintes:

  • DAYNAME: retorna uma cadeia de caractere composta por letras maiúsculas e minúsculas contendo o nome do dia (por exemplo, sexta-feira) para a parte de dia do argumento.
  • DAYOFWEEK: retorna o dia da semana no argumento como um valor de número inteiro no intervalo de 1 a 7, em que 1 representa domingo.
  • DAYOFWEEK_ISO: retorna o dia da semana no argumento como um valor de número inteiro no intervalo de 1 a 7, em que 1 representa segunda-feira.
  • DAYOFYEAR: retorna o dia do ano no argumento como um valor de número inteiro no intervalo de 1 a 366.
  • DAYS: retorna uma representação de número inteiro de uma data.
  • JULIAN_DAY: retorna um valor de número inteiro que representa o número de dias desde 1º de janeiro de 4712 A.C. (o início do calendário de data Juliana) até o valor de data especificado no argumento.
  • MIDNIGHT_SECONDS : retorna um valor de número inteiro no intervalo de 0 a 86 400 que representa o número de segundos entre a meia-noite e o valor de horário especificado no argumento.
  • MONTHNAME: retorna uma cadeia de caractere composta por letras maiúsculas e minúsculas contendo o nome do mês (por exemplo, janeiro) para a parte de mês do argumento.
  • TIMESTAMP_ISO: retorna um valor de registro de data e hora com base no argumento de data, hora ou registro de data e hora.
  • TIMESTAMP_FORMAT: retorna um registro de data e hora a partir de uma cadeia de caractere que foi interpretada usando um modelo de caractere.
  • TIMESTAMPDIFF: retorna um número estimado de intervalos do tipo definido pelo primeiro argumento, com base na diferença entre dois registros de data e hora.
  • TO_CHAR : retorna uma representação de caractere de um registro de data e hora que foi formatado usando um modelo de caractere. TO_CHAR é sinônimo de VARCHAR_FORMAT.
  • TO_DATE: retorna um registro de data e hora a partir de uma cadeia de caractere que foi interpretada usando um modelo de caractere. TO_DATE é sinônimo de TIMESTAMP_FORMAT.
  • WEEK : retorna a semana do ano no argumento como um valor de número inteiro no intervalo de 1 a 54. A semana começa com domingo.
  • WEEK_ISO : retorna a semana do ano no argumento como um valor de número inteiro no intervalo de 1 a 53.

Mudando do formato de data

Uma pergunta comum que sempre recebo relacionada à apresentação de datas. O formato padrão usado para datas é determinado pelo código de território do banco de dados (que pode ser especificado no horário de criação do banco de dados). Por exemplo, meu banco de dados foi criado usando territory=US. Portanto, o formato de data é semelhante ao seguinte:

values current date 
1 
---------- 
05/30/2003 
 
1 record(s) selected.

Ou seja, o formato é MM/DD/AAAA. Se você desejar mudar o formato, ligue a coleção de pacotes de utilitário do db2 para usar um formato de data diferente. Os formatos suportados são:

  • DEF: usar um formato de data e hora associado ao código de território.
  • EUR: usar o padrão da IBM para formato de data e hora da Europa.
  • ISO: usar o formato de data e hora da organização de normas internacionais.
  • JIS: usar o formato de data e hora do Japanese Industrial Standard.
  • LOC : usar um formato de data e hora em formato local associado ao código de território do banco de dados.
  • USA: usar o padrão da IBM para formato de data e hora dos EUA.

Para mudar o formato padrão para ISO no Windows (AAAA-MM-DD), execute as etapas a seguir:

  1. Na linha de comando, mude do diretório atual para sqllib\bnd.

    Por exemplo:
    No Windows: c:\program files\IBM\sqllib\bnd
    No UNIX:/home/db2inst1/sqllib/bnd

  2. Conecte ao banco de dados a partir do shell do sistema operacional como um usuário com autoridade SYSADM:
    db2 connect to DBNAME 
    db2 bind @db2ubind.lst datetime ISO blocking all grant public

    (No seu caso, substitua o nome do seu banco de dados e o formato de data desejado por DBNAME e ISO, respectivamente).

Agora é possível observar que o banco de dados usa o formato de data ISO:

values current date 
1 
---------- 
2003-05-30 
 
  1 record(s) selected.

Formatação de data/hora customizada

No último exemplo, demonstramos como alterar a maneira que o DB2 apresenta as datas em alguns formatos localizados. Mas e se você desejar um formato customizado como "aaaammdd"? A melhor maneira de fazer isso é gravar sua própria função de formatação customizada.

Aqui está o UDF:

create function ts_fmt(TS timestamp, fmt varchar(20)) 
returns varchar(50) 
return 
with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as 
( 
    select 
    substr( digits (day(TS)),9), 
    substr( digits (month(TS)),9) , 
    rtrim(char(year(TS))) , 
    substr( digits (hour(TS)),9), 
    substr( digits (minute(TS)),9), 
    substr( digits (second(TS)),9), 
    rtrim(char(microsecond(TS))) 
    from sysibm.sysdummy1 
    ) 
select 
case fmt 
    when 'yyyymmdd' 
        then yyyy || mm || dd 
    when 'mm/dd/yyyy' 
        then mm || '/' || dd || '/' || yyyy 
    when 'yyyy/dd/mm hh:mi:ss' 
        then yyyy || '/' || mm || '/' || dd || ' ' ||  
               hh || ':' || mi || ':' || ss 
    when 'nnnnnn' 
        then nnnnnn 
    else 
        'date format ' || coalesce(fmt,' <null> ') ||  
        ' not recognized.' 
    end 
from tmp 
</null>

O código de função pode parecer complexo à primeira vista, mas após um exame detalhado, você verá que na verdade ele é muito simples e elegante. Primeiro, usamos uma expressão de tabela comum (CTE) para dividir um registro de data e hora (o primeiro parâmetro de entrada) em seus componentes individuais. Em seguida, verificamos o formato fornecido (o segundo parâmetro de entrada) e remontamos o registro de data e hora usando o formato e as partes solicitados.

A função também é muito flexível. Para incluir outro padrão, basta anexar outra cláusula WHEN com o formato esperado. Quando um padrão inesperado é encontrado, uma mensagem de erro é retornada.

Exemplos de uso:

values ts_fmt(current timestamp,'yyyymmdd') 
 '20030818' 
values ts_fmt(current timestamp,'asa')  
 'date format asa not recognized.'

Resumo

Esses exemplos respondem às perguntas mais comuns que tenho encontrado sobre datas e horas. Vou atualizar este artigo com mais exemplos se os feedbacks sugerirem que eu faça isso. (Na verdade, eu já o atualizei três vezes, graças aos leitores).

Agradecimentos

Bill Wilkins, DB2 Partner Enablement
Randy Talsma

Renúncia de responsabilidade

Este artigo contém código de amostra. A IBM concede a você ("Licenciado") uma licença não exclusiva, sem royalty, para usar esse código de amostra. No entanto, o código de amostra é fornecido no estado em que se encontra, sem garantia, EXPRESSA OU IMPLÍCITA, INCLUINDO QUALQUER GARANTIA IMPLÍCITA DE COMERCIALIZAÇÃO, ADEQUAÇÃO A UM PROPÓSITO ESPECÍFICO OU NÃO INFRAÇÃO. A IBM E SEUS LICENCIADORES NÃO SERÃO RESPONSABILIZADOS POR NENHUM DANO SOFRIDO PELO LICENCIADO QUE RESULTE DO USO QUE ELE FIZER DO SOFTWARE. EM NENHUM CASO A IBM OU SEUS LICENCIADORES SERÃO RESPONSABILIZADOS POR QUALQUER PERDA DE RENDA, LUCRO OU DADOS OU POR DANOS DIRETOS, INDIRETOS, ESPECIAIS, CONSEQUENTES, INCIDENTAIS OU PUNITIVOS, CAUSADOS POR QUALQUER MOTIVO, E INDEPENDENTEMENTE DA TEORIA DE RESPONSABILIDADE, QUE RESULTEM DO USO OU DA INCAPACIDADE DE USAR O SOFTWARE, MESMO SE A IBM TIVER SIDO AVISADA SOBRE ESSES DANOS.


Recursos para download


Tema relacionado


Comentários

Acesse ou registre-se para adicionar e acompanhar os comentários.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=80
Zone=Information Management
ArticleID=1041227
ArticleTitle=Fundamentos básicos do DB2: Divirta-se com datas e horários
publish-date=12192016