PLSQL总结——12.时间与时间戳

/*
  关于时间戳的用法
*/

declare
  my_date       date;
  my_timesstamp timestamp; --精确

  start_date date;
  end_date   date;
  /*时间戳1变量*/
  my_age    interval year to month; --安装年月计算的时间戳
  my_years  number;
  my_months number;
  /*时间戳2变量*/
  my_times   interval day to second; --安装小时计算的时间戳,默认是timestamp精度计算
  my_hours   number;
  my_minutes number;
  my_seconds number;

begin
  /*date与timestamp区别*/
  my_date       := sysdate;
  my_timesstamp := sysdate;

  dbms_output.put_line(my_date);
  dbms_output.put_line(my_timesstamp);

  /*时间戳1*/
  start_date := to_date('15-AUG-88',
                        'DD-MON-RR',
                        'NLS_DATE_LANGUAGE=AMERICAN');
  end_date   := to_date('24-JAN-12',
                        'DD-MON-RR',
                        'NLS_DATE_LANGUAGE=AMERICAN');

  my_age := (end_date - start_date) year to month;

  dbms_output.put_line(my_age);

  my_years  := extract(year from my_age);
  my_months := extract(month from my_age);
  dbms_output.put_line('the year is ' || my_years);
  dbms_output.put_line('the months is ' || my_months);

  /*时间戳2*/
  start_date := to_date('4-56-34',
                        'hh24-mi-ss',
                        'NLS_DATE_LANGUAGE=AMERICAN');
  end_date   := to_date('23-58-30',
                        'hh24-mi-ss',
                        'NLS_DATE_LANGUAGE=AMERICAN');

  my_times := (end_date - start_date) day to second;

  dbms_output.put_line(my_times);
  my_hours   := extract(hour from my_times);
  my_minutes := extract(minute from my_times);
  my_seconds := extract(second from my_times);

  dbms_output.put_line('the hour is ' || my_hours);
  dbms_output.put_line('the minutes is ' || my_minutes);
  dbms_output.put_line('the seconds is ' || my_seconds);

end;

/*
--查选nls的设置
--select * from nls_session_parameters
*/
/*
--修改session的nls
begin
  dbms_session.set_nls('NLS_LANGUAGE', 'AMERICAN');
end;
*/