24.生产环境中对不同时区问题的处理办法

1.current_date,current_timestamp,localtimestamp

--1.在会话中修改时区
--偏移量-tz_offset
--数据库时区--time_zone
--系统本地时区--local
--区域名--v$timezone_names
alter session set time_zone = '-05:00';
alter session set time_zone = 'dbtimezone';
alter session set time_zone = 'local';
alter session set time_zone = 'Amwrica/New_York';

--current_date
----系统当前时间
----是日期类型
--current_timestamp
----系统当前时间戳
----是timestamp with time zone类型
--localtimestamp
----用户会话级别得当前时间戳
----是timestamp类型
hr@ORCLPDB01 2023-02-26 17:29:38> alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

Elapsed: 00:00:00.01
hr@ORCLPDB01 26-FEB-2023 18:34:24> select sessiontimezone,current_date from dual;

SESSIONTIMEZONE 							    CURRENT_DATE
--------------------------------------------------------------------------- -----------------------------
+08:00									    26-FEB-2023 18:34:43

Elapsed: 00:00:00.01
hr@ORCLPDB01 26-FEB-2023 18:34:43> select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE 							    CURRENT_TIMESTAMP
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
+08:00									    26-FEB-23 06.35.12.798333 PM +08:00

Elapsed: 00:00:00.00
hr@ORCLPDB01 26-FEB-2023 18:35:12> select sessiontimezone,localtimestamp from dual;

SESSIONTIMEZONE 							    LOCALTIMESTAMP
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
+08:00									    26-FEB-23 06.35.31.165822 PM

Elapsed: 00:00:00.00

2.dbtimezone sessiontimezone

hr@ORCLPDB01 26-FEB-2023 18:35:31> select dbtimezone from dual;

DBTIME
------
+00:00

Elapsed: 00:00:00.00
hr@ORCLPDB01 26-FEB-2023 18:38:21> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

Elapsed: 00:00:00.00

 3.timestamp data types

timestamp fields

hr@ORCLPDB01 2023-02-26 18:42:03> select hire_date from employees;

HIRE_DATE
-------------------
2003-06-17 00:00:00
2005-09-21 00:00:00
2 rows selected.

Elapsed: 00:00:00.01
hr@ORCLPDB01 2023-02-26 18:42:19> alter table employees modify hire_date timestamp;

Table altered.

Elapsed: 00:00:00.09
hr@ORCLPDB01 2023-02-26 18:42:54> select hire_date from employees;

HIRE_DATE
---------------------------------------------------------------------------
17-JUN-03 12.00.00.000000 AM
21-SEP-05 12.00.00.000000 AM

2 rows selected.

Elapsed: 00:00:00.01

4.comparing timestamp data types

hr@ORCLPDB01 2023-02-26 18:48:09> r
  1  create table web_orders ( order_date timestamp with time zone,
  2  delivery_tmie timestamp with local time zone)
  3*

Table created.

Elapsed: 00:00:00.02
hr@ORCLPDB01 2023-02-26 18:48:11> 
hr@ORCLPDB01 2023-02-26 18:48:13> insert into web_orders values (current_date,current_timestamp + 2);

1 row created.

Elapsed: 00:00:00.01
hr@ORCLPDB01 2023-02-26 18:48:47> select * from web_orders;

ORDER_DATE								    DELIVERY_TMIE
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
26-FEB-23 06.48.47.000000 PM +08:00					    28-FEB-23 06.48.47.000000 PM

Elapsed: 00:00:00.01

5.interval data types

hr@ORCLPDB01 2023-02-26 18:48:56> create table warranty(prod_id number,warranty_time interval year(3) to month);

Table created.

Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-26 18:54:57> insert into warranty values(123,interval '8' month);

1 row created.

Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-26 18:55:35> insert into  warranty values(155, interval '200' year(3));

1 row created.

Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-26 18:56:08> insert into warranty values(678, '200-11');

1 row created.

Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-26 18:56:38> select * from warranty;

   PROD_ID WARRANTY_TIME
---------- ---------------------------------------------------------------------------
       123 +000-08
       155 +200-00
       678 +200-11

Elapsed: 00:00:00.00

6.interval day to second 

hr@ORCLPDB01 2023-02-26 19:03:25> create table lab ( exp_id number, test_time interval day(2) to second);

Table created.

Elapsed: 00:00:00.05
hr@ORCLPDB01 2023-02-26 19:04:10> insert into lab values (100012,'90 00:00:00');

1 row created.

Elapsed: 00:00:00.02
hr@ORCLPDB01 2023-02-26 19:05:17> insert into lab values (56098, interval '6 03:30:16' day to second);

1 row created.

Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-26 19:05:24> select * from lab;

    EXP_ID TEST_TIME
---------- ---------------------------------------------------------------------------
    100012 +90 00:00:00.000000
     56098 +06 03:30:16.000000

Elapsed: 00:00:00.00

7.extract

hr@ORCLPDB01 2023-02-26 19:05:35> select extract ( year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)
------------------------
		    2023

Elapsed: 00:00:00.01

  1  select last_name,hire_date, extract ( month from hire_date )
  2  from employees
  3* where manager_id = 100

LAST_NAME		  HIRE_DATE								      EXTRACT(MONTHFROMHIRE_DATE)
------------------------- --------------------------------------------------------------------------- ---------------------------
Kochhar 		  21-SEP-05 12.00.00.000000 AM										9
De Haan 		  13-JAN-01 12.00.00.000000 AM										1
Raphaely		  07-DEC-02 12.00.00.000000 AM									       12
Weiss			  18-JUL-04 12.00.00.000000 AM										7
Fripp			  10-APR-05 12.00.00.000000 AM										4
Kaufling		  01-MAY-03 12.00.00.000000 AM										5
Vollman 		  10-OCT-05 12.00.00.000000 AM									       10
Mourgos 		  16-NOV-07 12.00.00.000000 AM									       11
Russell 		  01-OCT-04 12.00.00.000000 AM									       10
Partners		  05-JAN-05 12.00.00.000000 AM										1
Errazuriz		  10-MAR-05 12.00.00.000000 AM										3
Cambrault		  15-OCT-07 12.00.00.000000 AM									       10
Zlotkey 		  29-JAN-08 12.00.00.000000 AM										1
Hartstein		  17-FEB-04 12.00.00.000000 AM										2

8.tz_offset

hr@ORCLPDB01 2023-02-26 19:09:48> select tz_offset('US/Eastern'),
  2  tz_offset('Canada/Yukon'),
  3  tz_offset('Europe/London')
  4  from dual;

TZ_OFFS TZ_OFFS TZ_OFFS
------- ------- -------
-05:00	-08:00	+00:00

Elapsed: 00:00:00.00

9.from_tz

hr@ORCLPDB01 2023-02-26 19:14:13> select from_tz(timestamp '2000-07-12 08:00:00','Australia/North') from dual;

FROM_TZ(TIMESTAMP'2000-07-1208:00:00','AUSTRALIA/NORTH')
---------------------------------------------------------------------------
12-JUL-00 08.00.00.000000000 AM AUSTRALIA/NORTH

Elapsed: 00:00:00.00

10.to_timestamp

hr@ORCLPDB01 2023-02-26 19:15:33> select to_timestamp ( '2007-03-06 11:00:00','YYYY-MM-DD HH:MI:SS') from dual;

TO_TIMESTAMP('2007-03-0611:00:00','YYYY-MM-DDHH:MI:SS')
---------------------------------------------------------------------------
06-MAR-07 11.00.00.000000000 AM

Elapsed: 00:00:00.01

 11.to_yminterval

hr@ORCLPDB01 2023-02-26 19:19:22> r
  1  select hire_date,
  2  hire_date + to_yminterval('01-02') as hire_date_ymininterval
  3  from employees
  4* where department_id = 20

HIRE_DATE								    HIRE_DATE_YMININTERVAL
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
17-FEB-04 12.00.00.000000 AM						    17-APR-05 12.00.00.000000000 AM
17-AUG-05 12.00.00.000000 AM						    17-OCT-06 12.00.00.000000000 AM

12.to_dsinterval

hr@ORCLPDB01 2023-02-26 19:23:53> r
  1  select last_name,
  2  to_char(hire_date,'mm-dd-yy:hh:mi:ss') hire_date,
  3  to_char(hire_date + to_dsinterval('100 10:00:00'), 'mm-dd-yy:hh:mi:ss') hiredate2
  4* from employees

LAST_NAME		  HIRE_DATE	    HIREDATE2
------------------------- ----------------- -----------------
King			  06-17-03:12:00:00 09-25-03:10:00:00
Kochhar 		  09-21-05:12:00:00 12-30-05:10:00:00
De Haan 		  01-13-01:12:00:00 04-23-01:10:00:00
Hunold			  01-03-06:12:00:00 04-13-06:10:00:00
Ernst			  05-21-07:12:00:00 08-29-07:10:00:00
Austin			  06-25-05:12:00:00 10-03-05:10:00:00
Pataballa		  02-05-06:12:00:00 05-16-06:10:00:00
Lorentz 		  02-07-07:12:00:00 05-18-07:10:00:00