Intervals, Timestamp & Co.

Timezone onn db
select DBTIMEZONE from dual

Get world timezones for country
select tzname,tzabbrev from V$TIMEZONE_NAMES

Alter timezone
ALTER database SET TIME_ZONE = 'America/Denver';
alter session set time_zone = '-07:30'
alter session set time_zone =local
alter session set time_zone = dbtimezone

Time zone in tables
CREATE TABLE test (booking_date TIMESTAMP WITH LOCAL TIME ZONE);
--Caution the statement above will prevent you to change the database time zone later
--ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns
--use instead:
CREATE TABLE test (booking_date TIMESTAMP WITH TIME ZONE);

Example: LOCAL, data is changed to fit current time zone, so CAUTION
drop table test

CREATE TABLE test (a TIMESTAMP WITH TIME ZONE, b TIMESTAMP WITH local TIME ZONE);

alter session set time_zone = '-00:00'

INSERT INTO test VALUES(sysdate, sysdate);

--you are not allowed to query the tzh:tzm for b because it relates to current/local time zone
select to_char(a, 'dd/mm/yyyy hh24:mi tzh:tzm'), to_char(b, 'dd/mm/yyyy hh24:mi') from test
-----
27/03/2009 11:23 +00:00       27/03/2009 11:23

alter session set time_zone = '-07:30'

select to_char(a, 'dd/mm/yyyy hh24:mi tzh:tzm'), to_char(b, 'dd/mm/yyyy hh24:mi') from test
-----
27/03/2009 11:23 +00:00       27/03/2009 03:53

Create an interval
alter table lav_attivita add int_lavoro interval day(9) to second(9);

select cast('50 10:20:20.123' as interval day(9) to second(9)) from dual

Convert two dates to one interval
update mytable set myInterval = (
  to_timestamp(to_char(d2, 'dd/mm/yyyy hh24:mi'), 'dd/mm/yyyy hh24:mi') - 
  to_timestamp(to_char(d1, 'dd/mm/yyyy hh24:mi'), 'dd/mm/yyyy hh24:mi')
)

Add one day and one hour to a date
select sysdate + cast('1 1:00:0.0' as interval day(9) to second(9)) from dual
or
select sysdate + to_dsinterval('1 1:00:0.0') from dual

Add one year and six months to a date
select sysdate + to_yminterval('1-6') from dual

Calculate seconds difference between two dates

create or replace FUNCTION intervalToSeconds(i interval day to second)
   RETURN NUMBER
IS
   numgiorni    NUMBER;
   numore       NUMBER;
   numminuti    NUMBER;
   numsecondi   NUMBER;
   RESULT       NUMBER;
BEGIN
   numgiorni  := EXTRACT (DAY FROM i);
   numore     := EXTRACT (HOUR FROM i);
   numminuti  := EXTRACT (MINUTE FROM i);
   numsecondi := EXTRACT (SECOND FROM i);
   RESULT :=
        numsecondi
      + (numminuti * 60)
      + (numore * 60 * 60)
      + (numgiorni * 60 * 60 * 24);
   RETURN ABS(RESULT);
END;

FUNCTION getsecondsdifference (md1 DATE, md2 DATE)
   RETURN NUMBER
IS
   numgiorni    NUMBER;
   numore       NUMBER;
   numminuti    NUMBER;
   numsecondi   NUMBER;
   RESULT       NUMBER;
   i            INTERVAL DAY (9)TO SECOND (9);
BEGIN
   i := CAST (md1 AS TIMESTAMP) - CAST (md2 AS TIMESTAMP);
   return intervalToSeconds(i);
END;

Generate a (unique... depends...) timestamp close to another
--I needed this function once in my life somewhere...
  create or replace function generateTimestampLessThan(d timestamp) return timestamp is
    t timestamp;
  begin
    select current_timestamp into t from dual;
    return t- cast(EXTRACT(DAY FROM t - d)+1|| ' 00:00:00.000' as interval day to second);
  end;

Timestamp complete format
select * from myTable where tmstp = to_timestamp('12/09/2008 17:09:34,018167', 'dd/mm/yyyy hh24:mi:ssXFF')