Oracle Timestamp类型

作者：--    发布时间：2019-11-20

在本教程中将学习oracle timestamp数据类型以及如何在oracle数据库中有效处理timestamp数据。

oracle timestamp数据类型简介

timestamp数据类型用于存储日期和时间数据，包括年，月，日，时，分和秒。

另外，它存储小数秒，它不是由date数据类型存储的。

要定义timestamp列，请使用以下语法：

column_name timestamp[(fractional_seconds_precision)]

fractional_seconds_precision指定second字段小数部分的位数。它的范围从0到9，这意味着可以使用timestamp数据类型来存储到纳秒的精度。
如果省略fractional_seconds_precision，则默认为6。

以下表达式说明了如何定义timestamp列：

...
started_at tmestamp(2),
...

在此示例中，started_at列是一个timestamp列，其分数秒精度设置为微秒。

oracle timestamp文字

要指定timestamp文字，请使用以下格式：

timestamp 'yyyy-mm-dd hh24:mi:ss.ff'

以下示例说明了timestamp文字：

timestamp '1999-12-31 23:59:59.10'

有关如何构造格式字符串的详细信息，请查看oracle日期格式。

oracle timestamp示例

首先，创建一个名为logs的新表，其中包含一个timestamp列用于演示。

create table logs (
    log_id number generated by default as identity,
    message varchar(2) not null,
    logged_at timestamp (2) not null,
    primary key (log_id)
);

其次，将新行插入到logs表中：

insert into logs (
    message,
    logged_at
    )
values (
    'invalid username/password for root user',
    localtimestamp(2)
    );

insert into logs (
    message,
    logged_at
    )
values (
    'user root logged in successfully',
    localtimestamp(2)
    );

在这个例子中，使用localtimestamp(2)函数中得到当前的本地时间戳，其精确度秒为微秒，并将该值插入到logs表的logs_at列中。

第三，从日志表中查询timestamp数据：

select log_id,
       message,
       logged_at
from logs;

执行上面查询语句，得到下面结果 -

格式化timestamp值

要更改timestamp值的输出，可以使用to_char()函数，将timestamp值或列的名称作为第一个参数，将格式字符串作为第二个参数。

以下语句使用to_char()函数来格式化logged_at列中的值：

select message,
    to_char(logged_at, 'month dd, yyyy "at" hh24:mi')
from logs;

执行上面查询语句，得到下面结果 -

提取timestamp组件

要提取timestamp组件(如年，月，日，小时，分钟和秒)，请使用extract()函数：

extract( component from timestamp);

如以下面的语句示例：

select 
    message,
    extract(year from logged_at) year,
    extract(month from logged_at) month,
    extract(day from logged_at) day,
    extract(hour from logged_at) hour,
    extract(minute from logged_at) minute,
    extract(second from logged_at) second
from 
    logs;

注意到nls_date_language参数决定日期名称(thursday)，月份名称(august)以及timestamp缩写(thu，aug)的语言。

默认的timestamp格式

当字符类型的值被转换为timestamp数据类型时，oracle使用nls_timestamp_format参数来控制默认时间戳格式。

以下语句返回oracle数据库系统中当前的默认时间戳格式：

select
  value
from
  v$nls_parameters
where
  parameter = 'nls_timestamp_format';

执行上面查询语句，将得到以下结果 -

dd-mon-rr hh.mi.ssxff am

例如，假设要将新行以logs_attane值插入到logs表中，则使用to_timestamp()函数将字符串转换为timestamp值，如下所示：

insert into logs (
    message,
    logged_at
    )
values (
    'test default oracle timestamp format',
    to_timestamp('03-aug-17 11:20:30.45 am')
    );

请注意，时间戳值 - '03-aug-17 11:20:30.45 am'遵循标准时间戳格式。

下面来验证结果：

select
  log_id, message, logged_at
from
  logs;

执行上面示例代码，得到以下结果 -

如果想使用其他时间戳格式而不是默认格式，则可以使用alter session set语句来执行此操作。

在本教程中，您已经了解了oracle timestamp数据类型以及如何有效地处理时间戳。