plsql programming 10 日期和时间戳

年 月 日 时 分 秒 时区 用小时表示的相对于 UTC 的时差 用分钟表示的相对于 UTC 的时差

date 存储日期和时间, 不带时区, 精确到秒

timestamp 存储日期和时间, 不带时区, 时间精度可以达到10亿分之一秒, 小数点后9位. 除了精度这一点之外, TIMESTAMP 和 DATE 相同

timestamp with timezone 存储时区, 日期和时间, 精度达到小数点后 9 位

timestamp with local timezone 保存日期和精确到小数点后 9 位, 这个数据类型对时区敏感, 这个类型的值会自动在数据库所在时区及当地时区(会话端)之间转化. 这个值保存到数据库时, 也会转换成数据库时区, 而不是保存本地(会话)时区. 当从数据库提取数据时, 这个值又会从数据库的时区转换成本地(会话)时区.

例如: 在全世界范围内开会, 因为大家时区不同, 而此时你选用这种类型, 比如你规定几点开会, 那么就会按照你所在的时区变更时间到跟你时区相匹配的时间. 这样, 用户自己本身就不用计算复杂的时区转换逻辑, 而由数据库自己完成. (数据库会自动变更时间匹配到你的时区)

UTC 00, 标准时间, 各个时区都是参考这个00， + 或 - 相对时区. 所以, 如果选择这个类型, 可以把数据库时区设置成这个时间. UTC

声明日期和时间变量

var_name [constant] datetime_type [:= | default initial_value]

选择日期时间数据类型

面对这么多类型, 什么时候使用什么类型 ?

• 如果对时间要求到小数的秒, 则可以选择 TIMESTAMP
• 如果希望数据库能够自动在数据库时区和会话时区之间进行时间转换, 使用TIMESTAMP WITH LOCAL TIME ZONE
• 如果希望跟踪数据入口的会话时区, 使用 timestamp with time zone.
• 我们可以使用 timestamp 类型代替 date类型, 一个不带亚秒精度信息的 timestamp 会占用 7个字节, 和date一样, 如果 timestamp 带有压秒数据, 就会占用 11 个字节的存储空间.

其他一些考虑:

• 要是必须和 timestamp 数据类型出现之前的已有应用程序相兼容, 就的使用 DATE 类型.
• 如果使用数据库是 oracle 9i 以前的版本, 我们别无选择只能用 DATE 类型.

就以上函数使用的场合:

1. oracle 8i 以前, 我们只能选择 sysdate

2. 你所感兴趣的到底是数据库的时间还是会话的时间, 如果是会话时间, 那么返回会话时区, 如果是数据库时间, 那么返回数据库时区

3. 返回日期和时间中是否需要时期, 如果需要就要选择 current_timestamp 和 systimestamp

另外, 你要保证会话所在时区和数据库时区的正确性, 函数 sessiontimezone 和 dbtimezone 分别报告你的会话时区和数据库时区.

begin
	dbms_output.put_line('session timezone=' || sessiontimezone);
	dbms_output.put_line('session timestamp=' || current_timestamp);
	dbms_output.put_line('db server timestamp=' || systimestamp);-- 结果 +08:00
	dbms_output.put_line('db timezone=' || dbtimezone); -- 结果 00:00
	execute immediate 'alert session set time_zone=dbtimezone';
	dbms_output.put_line('db timestamp=' || current_timestamp);
	execute immediate 'alert session set time_zone=local';
end;

注意, 这里有一点要特别注意, 虽然调用systimestamp 显示的是数据库的时区, 这个时区是真正时区, 例如数据库在北京, 这个时区可以是 +08:00, 但是我们设置 db time zone 时, 要设置 00, 因为这样, 当真正需要 local time 转换时, 转换方便.

interval 数据类型 时间间隔

interval year to month 用年和月定义时间间隔

interval day to second 用天, 小时, 分钟, 秒(包括小数秒) 定义时间间隔.

var_name interval year to [year_precision] month   -- year_precision 代表年的位数, 从0-4, 缺省是2

var_name intrerval day [天的位数] to second [秒的位数]

天的位数: 0 到 9 , 缺省是 2

秒的位数: 0 到 9, 缺省是 6

什么时候用 interval

当我们要处理时间数量时, 可以用 interval,

下面例子中, 如果不用 interval 也可以处理, 但是很麻烦, 因为时间的间隔计算, 需要将年, 月 等字段分别提取计算, 还涉及到进位等问题.

例如:

-- chap10_02.sql
declare
    start_date TIMESTAMP;
    end_date TIMESTAMP;
    service_interval interval year to month;
    years_of_service number;
    months_of_service number;
begin
    start_date := to_timestamp('29-dec-1988', 'dd-mon-yyyy');
    end_date := to_timestamp('26-dec-1995', 'dd-mon-yyyy');

    -- 确定工龄, 并显示出来:
    service_interval := (end_date - start_date) year to month;
    dbms_output.put_line(service_interval);

    years_of_service := extract(year from service_interval);
    months_of_service := extract(month from service_interval);
    dbms_output.put_line(years_of_service || 'years and ' || months_of_service || 'months');
end;
/
show errors;

例子2:

讨论一个有流水生产线的企业, 组装每个产品所需要的时间(组装时间)是一个很重要的度量指标, 我们叫 ST, 缩短这个间隔会让产品线更有效率, 因此管理层希望能够跟踪并报告这个间隔, 在这个例子中, 每个产品在组装过程中都用一个跟踪号进行识别, 用来保存组装信息是这样的:

-- chap10_03.sql
table assembiles (
    tracking_id number not null,
    start_time timestamp not null,
    build_time interval day to second
);

function calc_bulid_time (esn IN assembiles.tracking_id%type)
    return dsinterval_unconstrained
is
    start_ts assembiles.start_time%type;
begin
    select start_time into start_ts from assemblies
     where tracking_id = esn;
    return localtimestamp - start_ts;
end;
/
show errors;

日期和时间戳直接量

date ‘yyyy-mm-dd’例如: ‘2012-12-12’

timestamp ‘yyyy-mm-dd hh:mi: ss[.fffffffff] [{+ | –}hh:mi]’

例如: ‘2012-12-12 05:05:00.123456789 +08:00’

-- chap10_04.sql
-- 直接量日期, 时间戳
declare
    ts1 timestamp with time zone;
    ts2 timestamp with time zone;
    ts3 timestamp with time zone;
    ts4 timestamp with time zone;
    ts5 date;
begin
    -- 有两个小数部分
    ts1 := timestamp '2002-02-19 11:52:00.00 -06:00';
    -- 9位小数, 24小时,
    ts2 := timestamp '2002-02-19 14:00:00.000000000 -05:00';
    -- 没有任何小数的秒
    ts3 := timestamp '2002-02-19 13:52:00 -5:00';
    -- 没有带时区, 使用缺省的会话时区
    ts4 := timestamp '2002-02-19 13:52:00';
    -- 一个日期直接量
    ts5 := date '2002-02-19';
end;

时间和日期这一章, 内容很多, 个人感觉很多东西都用不上, 所以没有收录, 需要其他内容可以查看原书.