Managing Data in Different Time Zones 管理数据在不同的时区
you should be able to: 1、Use data types similar to DATE that store fractional seconds and track time zones 2、Use data types that store the difference between two datetime values 3、Use the following datetime functions: CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE EXTRACT TZ_OFFSET FROM_TZ 转换为时区类型 TO_TIMESTAMP TO_YMINTERVAL 间隔年到月 TO_DSINTERVAL 间隔天到秒
用户会话的时间：current_date, current_timestamp, localtimestamp select sessiontimezone from dual;
ALTER SESSION SET TIME_ZONE = '-05:00'; ALTER SESSION SET TIME_ZONE = dbtimezone; ALTER SESSION SET TIME_ZONE = local; ALTER SESSION SET TIME_ZONE = 'America/New_York';
毫秒最大精度：9位，默认6位。 select hire_date from emp where rownum<5; alter table emp modify hire_date timestamp(9);
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; ALTER SESSION SET TIME_ZONE = '-5:00'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SELECT SESSIONTIMEZONE, LOCALTIMESTAMP FROM DUAL;
SELECT DBTIMEZONE FROM DUAL;
SELECT SESSIONTIMEZONE FROM DUAL;
CREATE TABLE web_orders (order_date TIMESTAMP WITH TIME ZONE, delivery_time TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO web_orders values (current_date, current_timestamp + 2);
SELECT * FROM web_orders;
INTERVAL Data Types 间隔时间类型 INTERVAL data types are used to store the difference between two datetime values.
There are two classes of intervals: Year-month Day-time
The precision of the interval is: The actual subset of fields that constitutes an interval Specified in the interval qualifier
例： 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;
例： CREATE TABLE lab ( exp_id number, test_time INTERVAL DAY(2) TO SECOND);
INSERT INTO lab VALUES (100012, '90 00:00:00'); INSERT INTO lab VALUES (56098, INTERVAL '6 03:30:16' DAY TO SECOND);
SELECT * FROM lab;
例： SELECT last_name, employee_id, hire_date FROM employees WHERE EXTRACT(YEAR FROM TO_DATE(hire_date, 'DD-MON-RR')) > 2007 ORDER BY hire_date;
例： SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) FROM employees WHERE manager_id = 100;
例： SELECT TZ_OFFSET('US/Eastern'), TZ_OFFSET('Canada/Yukon'), TZ_OFFSET('Europe/London') FROM DUAL;
例： SELECT FROM_TZ(TIMESTAMP '2000-07-12 08:00:00', 'Australia/North') FROM DUAL;
例： SELECT TO_TIMESTAMP ('2007-03-06 11:00:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
例： SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL FROM employees WHERE department_id = 20;
例： SELECT last_name, TO_CHAR(hire_date, 'mm-dd-yy:hh:mi:ss') hire_date, TO_CHAR(hire_date + TO_DSINTERVAL('100 10:00:00'), 'mm-dd-yy:hh:mi:ss') hiredate2 FROM employees;
DST 夏年时，中国未用了
