Cristiano Lopes da Silva com br) has a non-transferable license to use this Student Guide

(1)

Gerenciando Dados em

Diferentes Fusos Horários

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(2)

Objetivos

Ao concluir esta lição, você será capaz de:

• Usar tipos de dados semelhantes a DATE que armazenem

frações de segundos e rastreiem fusos horários

• Usar tipos de dados que armazenem a diferença entre

dois valores de data/horário

• Usar as seguintes functions de data/horário:

–

CURRENT_DATE

–

CURRENT_TIMESTAMP

–

LOCALTIMESTAMP

–

DBTIMEZONE

–

SESSIONTIMEZONE

–

EXTRACT

–

TZ_OFFSET

–

FROM_TZ

–

TO_TIMESTAMP

–

TO_YMINTERVAL

–

TO_DSINTERVAL

Objetivos

Nesta lição, você aprenderá a usar tipos de dados semelhantes a DATE que armazenem frações de segundos e rastreiem fusos horários. Esta lição trata de algumas das functions de data/horário disponíveis no banco de dados Oracle.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

(3)

Agenda da Lição

• CURRENT_DATE, CURRENT_TIMESTAMP

e LOCALTIMESTAMP

• Tipos de dados INTERVAL

• Usando as seguintes functions:

– EXTRACT

– TZ_OFFSET

– FROM_TZ

– TO_TIMESTAMP

– TO_YMINTERVAL

– TO_DSINTERVAL

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(4)

Fusos Horários

-08:00

A imagem representa o horário

de cada fuso quando a hora de

Greenwich é 12:00.

-05:00

+02:00 +10:00 +07:00

Fusos Horários

As horas do dia são medidas pela rotação da Terra. O horário do dia em um determinado momento dependerá de sua localização. Quando for meio-dia em Greenwich, Inglaterra, será meia-noite ao longo da Linha de Data Internacional. A Terra está dividida em 24 fusos horários, um para cada hora do dia. O horário ao longo do principal meridiano em Greenwich, Inglaterra, é conhecido como Tempo Médio de Greenwich, ou GMT. O GMT atualmente é conhecido como Coordinated Universal Time - Hora Universal, ou UTC. O UTC é o horário padrão usado como referência por todos os outros fusos horários do mundo. É igual durante todo o ano e não é afetado pela estação de verão nem pelo horário de verão. A linha do meridiano é imaginária e se estende do Polo Norte ao Polo Sul. É conhecida como de longitude zero e é a linha a partir da qual todas as outras linhas de longitude são medidas. Todo o horário é medido em relação ao UTC e todos os locais têm uma latitude (sua distância ao norte ou sul do Equador) e uma longitude (sua distância a leste ou oeste do meridiano de Greenwich).

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(5)

Parâmetro de Sessão TIME_ZONE

TIME_ZONE

pode ser definido como:

• Um deslocamento absoluto

• Fuso horário do banco de dados

• Fuso horário local do SO

• Uma região nomeada

ALTER SESSION SET TIME_ZONE = '-05:00'; ALTER SESSION SET TIME_ZONE = dbtimezone; ALTER SESSION SET TIME_ZONE = local;

ALTER SESSION SET TIME_ZONE = 'America/New_York';

Parâmetro de Sessão TIME_ZONE

O banco de dados Oracle suporta armazenamento de fuso horário com seus dados de data e hora, bem como frações de segundos. O comando ALTER SESSION pode ser usado para alterar os valores de fuso horário na sessão do usuário. Os valores de fuso horário podem ser definidos como um

deslocamento absoluto, um fuso horário local, nomeado ou de banco de dados.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(6)

CURRENT_DATE

, CURRENT_TIMESTAMP

e LOCALTIMESTAMP

• CURRENT_DATE:

–

Retorna a data atual da sessão do usuário

–

Possui um tipo de dados de DATE

• CURRENT_TIMESTAMP:

–

Retorna data/horário atuais da sessão do usuário

–

Possui um tipo de dados de TIMESTAMP WITH TIME ZONE

• LOCALTIMESTAMP:

–

Retorna data/horário atuais da sessão do usuário

–

Possui um tipo de dados de TIMESTAMP

CURRENT_DATE, CURRENT_TIMESTAMP e LOCALTIMESTAMP

As functions CURRENT_DATE e CURRENT_TIMESTAMP retornam a data e o timestamp atuais, respectivamente. Um tipo de dados de CURRENT_DATE é DATE. O tipo de dados de

CURRENT_TIMESTAMPé TIMESTAMP WITH TIME ZONE. Os valores retornados exibem o deslocamento do fuso horário da sessão SQL que executa as functions. O deslocamento do fuso horário é a diferença (em horas e minutos) entre o horário local e o UTC (Coordinated Universal Time). O tipo de dados de TIMESTAMP WITH TIME ZONE possui o formato:

TIMESTAMP [ (fractional_seconds_precision) ] WITH TIME ZONE

em que fractional_seconds_precision especifica, como opção, o número de dígitos na parte fracional do campo de data/horário SECOND e pode ser um número no intervalo de 0 a 9. O default é 6.

A function LOCALTIMESTAMP retorna a data e o horário atuais no fuso horário da sessão. A diferença entre LOCALTIMESTAMP e CURRENT_TIMESTAMP é que LOCALTIMESTAMP retorna um valor TIMESTAMP, enquanto CURRENT_TIMESTAMP retorna um valor TIMESTAMP WITH TIME ZONE.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(7)

Comparando Data/Horário no

Fuso Horário de uma Sessão

O parâmetro TIME_ZONE é definido como –5:00, e as

instruções SELECT para cada data/horário são executadas

para comparar as diferenças.

ALTER SESSION

SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; ALTER SESSION SET TIME_ZONE = '-5:00';

SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SELECT SESSIONTIMEZONE, LOCALTIMESTAMP FROM DUAL;

Comparando Data/Horário no Fuso Horário de uma Sessão O comando ALTER SESSION define o formato de data da sessão como

'DD-MON-YYYY HH24:MI:SS’—ou seja, dia do mês (1–31)-nome abreviado do mês-hora do dia de 4 dígitos (0–23):minuto (0–59):segundo (0–59).

O exemplo no slide mostra que a sessão é alterada para definir o parâmetro TIME_ZONE como – 5:00. Em seguida, a instrução SELECT para CURRENT_DATE, CURRENT_TIMESTAMP e LOCALTIMESTAMPé executada a fim de apontar as diferenças no formato.

Observação: O parâmetro TIME_ZONE especifica o deslocamento do fuso horário local default para a sessão de SQL atual. TIME_ZONE é apenas um parâmetro de sessão, não de inicialização. O parâmetro TIME_ZONE é definido como se segue:

TIME_ZONE = '[+ | -] hh:mm'

A máscara de formato ([+ | -] hh:mm) indica as horas e os minutos antes ou depois do UTC (Coordinated Universal Time, anteriormente conhecido como Tempo Médio de Greenwich).

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(8)

Comparando Data/Horário no

Fuso Horário de uma Sessão

Resultados de consultas:

Comparando Data/Horário no Fuso Horário de uma Sessão (continuação)

Neste caso, a function CURRENT_DATE retorna a data atual do fuso horário de uma sessão, a function CURRENT_TIMESTAMP retorna a data e o horário do fuso horário de uma sessão como valor de tipo de dados de TIMESTAMP WITH TIME ZONE e a function LOCALTIMESTAMP retorna data/horário atuais no fuso horário de uma sessão.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(9)

DBTIMEZONE

e SESSIONTIMEZONE

• Exiba o valor do fuso horário do banco de dados:

• Exiba o valor do fuso horário da sessão:

SELECT DBTIMEZONE FROM DUAL;

SELECT SESSIONTIMEZONE FROM DUAL;

DBTIMEZONEe SESSIONTIMEZONE

O DBA define o fuso horário default do banco de dados ao especificar a cláusula SET TIME_ZONE da instrução CREATE DATABASE . Se for omitido, o fuso horário default do banco de dados será o fuso horário do sistema operacional. O fuso horário do banco de dados não pode ser alterado em uma sessão com uma instrução ALTER SESSION.

A function DBTIMEZONE retorna o valor do fuso horário do banco de dados. O tipo de retorno é um deslocamento de fuso horário (um tipo de caractere no formato: '[+|-]TZH:TZM') ou um nome de região de fuso horário, dependendo de como o usuário especificou o valor do fuso horário do banco de dados na instrução CREATE DATABASE ou ALTER DATABASE mais recente. O exemplo do slide mostra que o fuso horário do banco de dados foi definido como “–05:00,” pois o parâmetro TIME_ZONEestá no formato:

TIME_ZONE = '[+ | -] hh:mm'

A function SESSIONTIMEZONE retorna o valor do fuso horário da sessão atual. O tipo de retorno é um deslocamento de fuso horário (um tipo de caractere no formato '[+|-]TZH:TZM') ou um nome de região de fuso horário, dependendo de como o usuário especificou o valor do fuso horário da sessão na instrução ALTER SESSION mais recente. O exemplo do slide mostra que o fuso horário da sessão é o deslocamento para UTC em –8 horas. Observe que o fuso horário do banco de dados é diferente do da sessão atual.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(10)

Tipos de Dados TIMESTAMP

Igual ao tipo de dados de TIMESTAMP; também inclui um deslocamento de fuso horário em seu valor

TIMESTAMP WITH LOCAL TIME ZONE

Igual ao tipo de dados de TIMESTAMP, também inclui:

TIMEZONE_HOURe TIMEZONE_MINUTE ou TIMEZONE_REGION

TIMESTAMP WITH TIME ZONE

Ano, Mês, Dia, Hora, Minuto, Segundo com frações de segundos

TIMESTAMP

Campos Tipo de Dados

Tipo de Dados de TIMESTAMP

O tipo de dados de TIMESTAMP é uma extensão do tipo de dados de DATE.

TIMESTAMP (fractional_seconds_ precision)

Este tipo de dados contém os valores de data de ano, mês e dia, além dos valores de tempo de hora, minuto e segundo, em que a precisão significativa de frações de segundos é o número de dígitos na parte fracional do campo de data/horário SECOND. Os valores aceitos de

fractional_seconds_precisionsignificativos estão entre 0 e 9. O default é 6.

TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE

Esse tipo de dados contém todos os valores de TIMESTAMP, bem como o valor de deslocamento do fuso horário.

TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE

Esse tipo de dados contém todos os valores de TIMESTAMP, com as seguintes exceções:

• Os dados são normalizados para o fuso horário do banco de dados quando são armazenados no banco de dados.

• Quando os dados são recuperados, os usuários vêem os dados no fuso horário da sessão.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(11)

Campos de TIMESTAMP

TIMEZONE_MINUTE –12 a 14 TIMEZONE_HOUR 00 a 59,9(N) em que 9(N) é a precisão SECOND 00 a 59 MINUTE 00 a 23 HOUR 01 a 31 DAY 01 a 12 MONTH –4712 a 9999 (excluindo o ano 0) YEAR Valores Válidos Campo de Data e Hora

00 a 59

Campos de TIMESTAMP

Cada tipo de dados de data/horário é composto de vários desses campos. Datas/horários só serão mutuamente comparáveis e atribuíveis se tiverem os mesmos campos de data/horário.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(12)

Diferença Ente DATE e TIMESTAMP

-- when hire_date is

of type DATE

SELECT hire_date

FROM employees;

ALTER TABLE employees

MODIFY hire_date TIMESTAMP;

SELECT hire_date

FROM employees;

B

A

…

Tipo de Dados de TIMESTAMP: Exemplo

No slide, o exemplo A mostra os dados da coluna hire_date da tabela EMPLOYEES quando o tipo de dados da coluna é DATE. No exemplo B, a tabela é alterada e o tipo de dados da coluna hire_dateé transformado em TIMESTAMP. A saída mostra as diferenças na exibição. Você pode converter de DATE para TIMESTAMP quando a coluna tiver dados, mas não pode converter de DATE ou TIMESTAMP para TIMESTAMP WITH TIME ZONE, a menos que a coluna esteja vazia.

É possível especificar a precisão de fração de segundos do timestamp. Se nenhuma for especificada, como neste exemplo, o valor definido como default será 6.

Por exemplo, a instrução a seguir define a precisão de fração de segundos como 7:

ALTER TABLE employees

MODIFY hire_date TIMESTAMP(7);

Observação: O tipo de dados de data do Oracle aparece por default como mostrado neste exemplo. Entretanto, o tipo de dados de data também contém informações adicionais como horas, minutos, segundos, AM e PM. Para obter a data nesse formato, você pode aplicar uma máscara de formato ou uma function no valor de data.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(13)

Comparando Tipos de Dados de TIMESTAMP

CREATE TABLE web_orders

(order_date TIMESTAMP WITH TIME ZONE,

delivery_time TIMESTAMP WITH LOCAL TIME ZONE);

INSERT INTO web_orders values

(current_date, current_timestamp + 2);

**SELECT * FROM web_orders;**

Comparando Tipos de Dados de TIMESTAMP

No exemplo do slide, uma nova tabela web_orders é criada com uma coluna de tipo de dados TIMESTAMP WITH TIME ZONEe uma coluna de tipo de dados TIMESTAMP WITH LOCAL TIME ZONE. Essa tabela é preenchida sempre que uma web_order é emitida. O timestamp e o fuso horário do usuário que emite a ordem é inserido com base no CURRENT_DATE value. O timestamp e o fuso horário locais são preenchidos inserindo-se dois dias a partir do valor de CURRENT_TIMESTAMPpara ambos sempre que uma ordem é emitida. Quando uma empresa baseada na web garante a remessa, ela pode estimar o prazo de entrega com base no fuso horário da pessoa que emite a ordem.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(14)

Agenda da Lição

• CURRENT_DATE, CURRENT_TIMESTAMP

e LOCALTIMESTAMP

• Tipos de dados de INTERVAL

• Usando as seguintes functions:

– EXTRACT

– TZ_OFFSET

– FROM_TZ

– TO_TIMESTAMP

– TO_YMINTERVAL

– TO_DSINTERVAL

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(15)

Tipos de Dados de INTERVAL

• Os tipos de dados de INTERVAL são usados para

armazenar a diferença entre dois valores de data/horário.

• Existem duas classes de intervalos:

–

Ano-mês

–

Dia-hora

• A precisão do intervalo é:

–

O subconjunto real de campos que constitui um intervalo

–

Especificado em um qualificador de intervalo

Dias, Hora, Minuto, Segundo com fração de segundos

INTERVAL DAY TO SECOND

Ano, Mês INTERVAL YEAR TO MONTH

Campos Tipo de Dados

Tipos de Dados de INTERVAL

Os tipos de dados de INTERVALsão usados para armazenar a diferença entre dois valores de data/horário. Existem duas classes de intervalos: ano-mês e dia-hora. Um intervalo ano-mês é constituído de um subconjunto contíguo de campos de YEAR e MONTH, enquanto um intervalo dia-hora é composto de um subconjunto contíguo de campos que consiste em DAY, HOUR, MINUTE e SECOND. O subconjunto real de campos que constitui um intervalo é chamado de precisão do intervalo e é especificado no qualificador de intervalo. Como o número de dias em um ano depende do calendário, o intervalo ano-mês depende do NLS, enquanto o intervalo dia-hora, não.

O qualificador de intervalo também pode especificar a precisão do campo inicial (ou único), que é o número de dígitos. Caso o campo final seja o valor SECOND, o qualificador de intervalo também poderá especificar a precisão das frações de segundos, que é o número de dígitos na parte fracional do valor SECOND. Se não for especificado, o valor default de precisão do campo à inicial será de 2 dígitos, e o valor default de precisão de fração de segundos será de 6 dígitos.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(16)

Este tipo de dados armazena um período em anos e meses, em que year_precision é o número de dígitos no campo de data/horário YEAR. Os valores aceitos estão entre 0 e 9. O default é 6. INTERVAL DAY (day_precision) TO SECOND

(fractional_seconds_precision)

Este tipo de dados armazena um período em dias, horas, minutos e segundos, em que

day_precisioné o número máximo de dígitos no campo de data/horário DAY (os valores aceitos estão entre 0 e 9, o default é 2) e fractional_seconds_precision é o número de dígitos da parte fracional do campo SECOND. Os valores aceitos estão entre 0 e 9. O default é 6.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(17)

Campos INTERVAL

00 a 59,9(N) em que 9(N) é a precisão SECOND 00 a 59 MINUTE HOUR DAY MONTH YEAR Campo INTERVAL 00 a 23

Qualquer número inteiro positivo ou negativo 00 a 11

Qualquer número inteiro positivo ou negativo Valores Válidos de Intervalo

Campos INTERVAL

INTERVAL YEAR TO MONTHpode ter campos para YEAR e MONTH.

INTERVAL DAY TO SECONDpodem ter campos para DAY, HOUR, MINUTE e SECOND. O subconjunto real de campos que constitui um item do tipo de intervalo é definido por um qualificador de intervalo e esse subconjunto é conhecido como precisão do item.

Os intervalos de ano-mês são mutuamente comparáveis e atribuíveis apenas a outros intervalos de ano-mês, e os de dia-hora são mutuamente comparáveis e atribuíveis somente a outros intervalos de dia-hora.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(18)

INTERVAL YEAR TO MONTH

: Exemplo

CREATE TABLE warranty

(prod_id number, warranty_time INTERVAL YEAR(3) TO MONTH);

INSERT INTO warranty VALUES (123, INTERVAL '8' MONTH); INSERT INTO warranty VALUES (155, INTERVAL '200'

YEAR(3));

INSERT INTO warranty VALUES (678, '200-11'); SELECT * FROM warranty;

Tipo de Dados de INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTHarmazena um período usando os campos de data/horário YEAR e MONTH. Especifique INTERVAL YEAR TO MONTH como se segue:

INTERVAL YEAR [(year_precision)] TO MONTH

em que year_precision é o número de dígitos no campo de data/horário YEAR. O valor default de year_precision é 2.

Restrição: O campo inicial deve ser mais significativo do que o campo final. Por exemplo, INTERVAL '0-1' MONTH TO YEARnão é válido.

Exemplos

• INTERVAL '123-2' YEAR(3) TO MONTH

Indica um intervalo de 123 anos, 2 meses

• INTERVAL '123' YEAR(3)

Indica um intervalo de 123 anos, 0 meses

• INTERVAL '300' MONTH(3)

Indica um intervalo de 300 meses

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(19)

INTERVAL DAY TO SECOND; o tipo de coluna, o argumento PL/SQL, a variável e o tipo de retorno devem ser um dos dois. Mas para literais de intervalo, o sistema reconhece outros tipos de intervalo ANSI (American National Standards Institute) como INTERVAL '2' YEAR ou INTERVAL '10' HOUR. Nesses casos, cada intervalo é convertido em um dos dois tipos suportados.

No exemplo do slide, é criada uma tabela WARRANTY, que contém uma coluna warranty_time que aceita o tipo de dados INTERVAL YEAR(3) TO MONTH. Valores diferentes são inseridos nela para indicar anos e meses de vários produtos. Quando essas linhas são recuperadas da tabela, você vê o valor do ano separado do valor do mês por um (-).

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(20)

Tipo de Dados de INTERVAL DAY TO SECOND:

Exemplo

CREATE TABLE lab

( exp_id number, test_time INTERVAL DAY(2) TO SECOND); INSERT INTO lab VALUES (100012, '90 00:00:00');

INSERT INTO lab VALUES (56098,

INTERVAL '6 03:30:16' DAY TO SECOND);

SELECT * FROM lab;

Tipo de Dados de INTERVAL DAY TO SECOND: Exemplo

No exemplo do slide, você está criando a tabela de laboratório com uma coluna test_time do tipo de dados de INTERVAL DAY TO SECOND. Em seguida, insira nela o valor “90 00:00:00” para indicar 90 dias e 0 horas, 0 minutos, 0 segundos e INTERVAL '6 03:30:16' DAY TO SECONDpara indicar 6 dias, 3 horas, 30 minutos e 16 segundos. A instrução SELECT mostra como esses dados são exibidos no banco de dados.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(21)

Agenda da Lição

• CURRENT_DATE, CURRENT_TIMESTAMP

e LOCALTIMESTAMP

• Tipos de dados de INTERVAL

• Usando as seguintes functions:

–

EXTRACT

–

TZ_OFFSET

–

FROM_TZ

–

TO_TIMESTAMP

–

TO_YMINTERVAL

–

TO_DSINTERVAL

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(22)

EXTRACT

• Exiba o componente YEAR de SYSDATE.

• Exiba o componente MONTH de HIRE_DATE para

funcionários cujo MANAGER_ID seja 100.

SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;

SELECT last_name, hire_date,

EXTRACT (MONTH FROM HIRE_DATE) FROM employees

WHERE manager_id = 100;

EXTRACT

A expressão EXTRACT extrai e retorna o valor de um campo de data/horário especificado de uma expressão de valor de data/horário ou intervalo. Você pode extrair qualquer componente citado na sintaxe anterior usando a function EXTRACT. A sintaxe da function EXTRACT é:

SELECT EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND] [TIMEZONE_HOUR] [TIMEZONE_MINUTE]

[TIMEZONE_REGION] [TIMEZONE_ABBR]

FROM [datetime_value_expression] [interval_value_expression]);

Quando você extrai TIMEZONE_REGION ou TIMEZONE_ABBR (abreviação), o valor retornado é uma string que contém o nome ou a abreviação do fuso horário apropriado. Quando extrai qualquer um dos outros valores, o valor retornado é uma data do calendário gregoriano. Durante a extração de data/horário com um valor de fuso horário, o valor retornado está em UTC.

No primeiro exemplo do slide, a function EXTRACT é usada para extrair YEAR de SYSDATE. No segundo exemplo do slide, a function EXTRACT é usada a fim de extrair MONTH da coluna HIRE_DATEda tabela EMPLOYEES para os funcionários que reportam ao gerente cujo

seja 100.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(23)

TZ_OFFSET

Exiba o deslocamento de fuso horário para os fusos

'US/Eastern', 'Canada/Yukon' e 'Europe/London'

:

SELECT TZ_OFFSET('US/Eastern'),

TZ_OFFSET('Canada/Yukon'), TZ_OFFSET('Europe/London') FROM DUAL;

TZ_OFFSET

A function TZ_OFFSET retorna o deslocamento de fuso horário correspondente ao valor informado. O valor de retorno depende da data em que a instrução é executada. Por exemplo, se a function TZ_OFFSETretornar um valor –08:00, esse valor indicará que o fuso horário em que o comando foi executado está oito horas atrás do UTC. Você pode informar um nome de fuso horário válido, um deslocamento de fuso horário do UTC (que retorna a si mesmo) ou a palavra-chave

SESSIONTIMEZONEou DBTIMEZONE. A sintaxe da function TZ_OFFSET é:

TZ_OFFSET ( ['time_zone_name'] '[+ | -] hh:mm'] [ SESSIONTIMEZONE] [DBTIMEZONE]

A Ford Motor Company possui sede em Michigan, EUA, cujo fuso horário é o do leste dos Estados Unidos. O presidente da empresa, sr. Ford, deseja conduzir uma conference call com os vice-presidentes de operações do Canadá e da Europa, que estão nos fusos horários de Canadá/Yukon e Europa/Londres, respectivamente. O sr. Ford deseja saber o horário em cada um desses locais para confirmar a participação de sua gerência na reunião. Seu assistente, o sr. Scott, ajuda emitindo as consultas mostradas no exemplo e obtém os seguintes resultados:

• O fuso horário 'US/Eastern' está atrasado quatro horas em relação ao UTC. • O fuso horário 'Canada/Yukon' está atrasado sete horas em relação ao UTC. • O fuso horário 'Europe/London' está uma hora adiantado em relação ao UTC.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(24)

dinâmica de desempenho V$TIMEZONE_NAMES. SELECT * FROM V$TIMEZONE_NAMES;

…

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(25)

FROM_TZ

Exiba o valor de TIMESTAMP

'2000-03-28 08:00:00'

como

valor de TIMESTAMP WITH TIME ZONE para a região de fuso

horário 'Australia/North'.

SELECT FROM_TZ(TIMESTAMP

'2000-07-12 08:00:00', 'Australia/North') FROM DUAL;

FROM_TZ

A function FROM_TZ converte um valor de TIMESTAMP em um valor de TIMESTAMP WITH TIME ZONE.

A sintaxe da function FROM_TZ é:

FROM_TZ(TIMESTAMP timestamp_value, time_zone_value)

em que time_zone_value é uma string de caracteres no formato 'TZH:TZM' ou uma expressão de caracteres que retorna uma string em TZR (região de fuso horário) com um formato TZD opcional. TZDé uma string de fuso horário abreviada com informações sobre o horário de verão. TZR

representa a região do fuso horário nas strings de entrada de data/horário. 'Australia/North' e 'PST'são exemplos para o horário padrão EUA/Pacífico, 'PDT' para horário de verão

EUA/Pacífico etc.

O exemplo do slide converte um valor de TIMESTAMP em TIMESTAMP WITH TIME ZONE. Observação: Para exibir uma lista de valores válidos para os elementos de formato TZR e TZD, consulte a view dinâmica de desempenho V$TIMEZONE_NAMES.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(26)

TO_TIMESTAMP

Exiba a string de caracteres

'2007-03-06 11:00:00'

como um valor TIMESTAMP:

SELECT TO_TIMESTAMP ('2007-03-06 11:00:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

TO_TIMESTAMP

A function TO_TIMESTAMP converte uma string de CHAR, VARCHAR2, NCHAR ou o tipo de dados de NVARCHAR2 em um valor do tipo de dados TIMESTAMP. A sintaxe da function

TO_TIMESTAMPé:

TO_TIMESTAMP (char,[fmt],['nlsparam'])

O fmt opcional especifica o formato de char. Se você omitir fmt, a string deverá estar no formato default do tipo de dados TIMESTAMP. O nlsparam opcional especifica o idioma no qual

abreviações e nomes de mês e dia são retornados. Este argumento pode ter o seguinte formato:

'NLS_DATE_LANGUAGE = language'

Se você omitir nlsparams, essa function usará o idioma de data default para sua sessão. O exemplo do slide converte uma string de caracteres em um valor de TIMESTAMP.

Observação: Você pode usar a function TO_TIMESTAMP_TZ para converter uma string de CHAR, VARCHAR2, NCHAR, ou de tipo de dados NVARCHAR2 em um valor do tipo de dados TIMESTAMP WITH TIME ZONE. Para obter mais informações sobre essa function, consulte Oracle Database SQL Language Reference 11g Release 1 (11.1).

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(27)

TO_YMINTERVAL

Exiba uma data de um ano e dois meses após a data de

contratação dos funcionários do departamento com o

DEPARTMENT_ID

20.

SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL FROM employees WHERE department_id = 20; TO_YMINTERVAL

A function TO_YMINTERVAL converte uma string de caracteres de CHAR, VARCHAR2, NCHAR, ou tipo de dados de NVARCHAR2 em um tipo de dados de INTERVAL YEAR TO MONTH. O tipo de dados de INTERVAL YEAR TO MONTH armazena um período usando os campos de data/horário YEARe MONTH. O formato de INTERVAL YEAR TO MONTH é:

INTERVAL YEAR [(year_precision)] TO MONTH

em que year_precision é o número de dígitos no campo de data/horário YEAR. O valor default de year_precision é 2.

A sintaxe da function TO_YMINTERVAL é:

TO_YMINTERVAL (char)

em que char é a string de caracteres a ser convertida

O exemplo no slide calcula uma data de um ano e dois meses após a data de contratação dos funcionários do departamento 20 da tabela EMPLOYEES.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(28)

TO_DSINTERVAL

Exiba uma data 100 dias e 10 horas após a data de

contratação de todos os funcionários.

SELECT last_name,

TO_CHAR(hire_date, 'mm-dd-yy:hh:mi:ss') hire_date, TO_CHAR(hire_date + TO_DSINTERVAL('100 10:00:00'), 'mm-dd-yy:hh:mi:ss') hiredate2 FROM employees;

…

TO_DSINTERVAL

TO_DSINTERVALconverte uma string de caracteres de CHAR, VARCHAR2, NCHAR, ou de tipo de dados de NVARCHAR2 em um tipo de dados de INTERVAL DAY TO SECOND.

No exemplo do slide, a data de 100 dias e 10 horas após a contratação é obtida.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

(29)

Horário de Verão

• Primeiro Domingo de Abril

–

O horário salta de 01:59:59 para 03:00:00.

–

Os valores entre 02:00:00 a 02:59:59 AM não são válidos.

• Último Domingo de Outubro

–

O horário salta de 02:00:00 para 01:00:01.

–

Os valores entre 01:00:01 a 02:00:00 são ambíguos porque

são acessados duas vezes.

DST (Horário de Verão)

A maioria dos países ocidentais avança o relógio uma hora durante os meses de verão. O período é chamado de horário de verão. O horário de verão compreende o primeiro domingo de abril até o último domingo de outubro na maioria dos Estados Unidos, México e Canadá. Os países da União Europeia cumprem o horário de verão, mas eles o chamam de período de verão. O período de verão da Europa começa uma semana antes de sua contrapartida norte-americana, mas termina na mesma época.

O banco de dados Oracle verifica automaticamente, para qualquer região de fuso horário, se o horário de verão está em vigor e retorna os valores respectivos ao horário local. O valor de data/horário é suficiente para o banco de dados Oracle verificar se o horário de verão está em vigor em uma determinada região em todos os casos, exceto casos de limite. Um caso de limite ocorre durante o período de início ou término do horário de verão. Por exemplo, na região EUA/Oriental, quando o horário de verão entra em vigor, o horário é alterado de 01:59:59 para 03:00:00. O intervalo de uma hora entre 02:00:00 e 02:59:59 não existe. Quando o horário de verão termina, o horário é alterado de 02:00:00 para 01:00:01, e o intervalo de uma hora entre 01:00:01 e 02:00:00 é repetido.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(30)

ERROR_ON_OVERLAP_TIMEé um parâmetro de sessão que faz com que o sistema emita um erro ao encontrar uma data/horário que ocorra no período sobreposto e nenhuma abreviação de fuso horário tiver sido especificada para distinguir o período.

Por exemplo, o horário de verão termina em 31 de outubro, às 02:00:01. Os períodos sobrepostos são: • 31/10/2004 01:00:01 a 31/10/2004 02:00:00 (EDT)

• 31/10/2004 01:00:01 a 31/10/2004 02:00:00 (EST)

Se você inserir uma string de data/horário que ocorra em um desses dois períodos, deverá especificar a abreviação do fuso horário (por exemplo, EDT ou EST) na string de entrada para que o sistema determine o período. Sem essa abreviação do fuso horário, o sistema faz o seguinte:

Se o parâmetro ERROR_ON_OVERLAP_TIME for FALSE, ele considerará que o horário de entrada é o padrão (por exemplo, EST). Caso contrário, um erro será indicado.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(31)

Quiz

O parâmetro de sessão TIME_ZONE pode ser definido como:

1. Um deslocamento relativo

2. Fuso horário do banco de dados

3. Fuso horário local do SO

4. Uma região nomeada

Respostas: 2, 3, 4

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(32)

Resumo

Nesta lição, você aprendeu a usar as functions:

• CURRENT_DATE

• CURRENT_TIMESTAMP

• LOCALTIMESTAMP

• DBTIMEZONE

• SESSIONTIMEZONE

• EXTRACT

• TZ_OFFSET

• FROM_TZ

• TO_TIMESTAMP

• TO_YMINTERVAL

• TO_DSINTERVAL

Resumo

Esta lição trata de algumas das functions de data/horário disponíveis no banco de dados Oracle.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(33)

Exercício 5: Visão Geral

Este exercício aborda o uso das functions de data/horário.

Exercício 5: Visão Geral

Neste exercício, você exibe os deslocamentos de fuso horário, CURRENT_DATE,

CURRENT_TIMESTAMPe LOCALTIMESTAMP. Define também os fusos horários e usa a function EXTRACT

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

.

non-transferable license to use this Student Guideฺ

(34)

2. a. Crie consultas para exibir os deslocamentos do fuso horário (TZ_OFFSET) para os seguintes fusos horários.

- EUA/Pacífico-Novo

- Cingapura

- Egito

b. Altere a sessão a fim de definir o valor do parâmetro TIME_ZONE para o deslocamento do fuso horário de EUA/Pacífico-Novo.

c. Exiba CURRENT_DATE, CURRENT_TIMESTAMP e LOCALTIMESTAMP para esta sessão.

d. Altere a sessão a fim de definir o valor do parâmetro TIME_ZONE para o deslocamento do fuso horário de Cingapura.

e. Exiba CURRENT_DATE, CURRENT_TIMESTAMP e LOCALTIMESTAMP para esta sessão.

Observação: O resultado poderá ser diferente com base na data em que o comando foi executado.

Observação: Observe no exercício anterior que CURRENT_DATE,

CURRENT_TIMESTAMPe LOCALTIMESTAMP dependem do fuso horário da sessão. 3. Crie uma consulta para exibir DBTIMEZONE e SESSIONTIMEZONE.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(35)

funcionários que trabalham no departamento 80.

5. Altere a sessão para definir NLS_DATE_FORMAT como DD-MON-YYYY.

6. Examine e execute o script lab_05_06.sql para criar a tabela SAMPLE_DATES e preenchê-la.

a. Selecione na tabela e exiba os dados.

b. Modifique o tipo de dados da coluna DATE_COL e altere-o para TIMESTAMP. Selecione na tabela para exibir os dados.

c. Tente modificar o tipo de dados da coluna DATE_COL e altere-o para TIMESTAMP WITH TIME ZONE. O que acontece?

7. Crie uma consulta para recuperar os sobrenomes com base na tabela EMPLOYEES e calcular o status da avaliação. Se o ano de contratação for 1998, exiba Needs Review para o status da avaliação, caso contrário, exiba not this year! Nomeie a coluna de status da avaliação como Review. Classifique os resultados pela coluna HIRE_DATE.

Dica: Use uma expressão CASE com a function EXTRACT para calcular o status da avaliação.

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(36)

Cristiano Lopes da Silva (opscris@yahooฺcomฺbr) has a

non-transferable license to use this Student Guideฺ

(37)

8. Crie uma consulta para imprimir os sobrenomes e o número de anos de serviço de cada funcionário. Se o funcionário tiver sido empregado por cinco ou mais anos, imprima 5 years of service. Se o funcionário tiver sido empregado por 10 ou mais anos, imprima 10 years of service. Se o funcionário tiver sido empregado por 15 ou mais anos, imprima 15 years of service. Se nenhuma dessas condições for atendida, imprima maybe next year! Classifique os resultados pela coluna HIRE_DATE. Use a tabela EMPLOYEES.