Curso Oracle 11g - Lesson 5 - Programação Part2

--lesson 5
--Alterar o fuso horário
alter session set time_zone = '-5:00';

--mostrar o fuso horário
select dbtimezone,sessiontimezone, current_date, current_timestamp,
localtimestamp
from dual;

Create table tab_datas (
data1 date,
data2 timestamp,
data3 timestamp with time zone,
data4 timestamp with local time zone
);
alter session set time_zone = '-3:00';

insert into tab_datas values
(current_date, current_date, current_date, current_date);

select * from tab_datas;

---Interval
--Conta com intervalos de anos e meses

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;

select current_date + interval '8' month  from warranty;
select current_date, warranty_time, current_date + warranty_time from warranty;

create table garantia
(produto number(6),
data_compra date,
garantia_meses number(2));

insert into garantia values(1,'15/09/2011', 18);

select * from garantia;

select data_compra, garantia_meses, ADD_MONTHS(data_compra,garantia_meses)
from garantia;



--EXTRACT
--Extrair ano ou mês

select * from employees;

select last_name,
extract(year from sysdate) - extract(year from hire_date),
extract(month from hire_date)
from employees
order by 2 desc;

select extract(month from hire_date), count(*)
from employees
group by extract(month from hire_date);

--TZ_OFFSET
--Mostra  fuso horário de uma região especifica
select sessiontimezone, current_date from dual;
select tz_offset('America/Sao_Paulo') from dual;

--Mostra todos os fusos horário
select * from v$timezone_names;

--FROM_TZ
--Mostra uma data/horário conforme o fuso horário escolhido
select from_tz(timestamp '2000-07-12 08:00:00', 'Australia/North')
from dual;

--TO_TIMESTAMP
--transformar explicitamente um texto em data/hora

Select to_timestamp('10-11-12 08:00:00')
from dual;

--TO_YMINTERVAL
--soma ano e mes em uma data
--TO_DSINTERVAL
--soma dias, horas, minutos e segundos
select hire_date, hire_date + to_yminterval('1-2') as hire_date,
hire_date + to_dsinterval('100 10:00:00') as hire_date2
from employees;



Fiquem a vontade para comentar e sugerir melhorias e/ou correção dos exemplos acima.