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.002.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.003.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.014.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.015.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.006.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.007.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 28.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.009.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.0010.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.0111.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 AM12.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
