GBase 8s 日期时间数据类型

数据类型看起来非常简单，但用起来会发现有许多知识点。以下以GBase 8s v8.8 3.0.0_1版本为例介绍日期时间数据类型。

1、常见的日期时间数据类型

1.1、date

最常用的日期类型，它可以保存日期。可以表示公元0001-01-01至9999-12-31之间的日期值，可接受输入值为0000-00-00，等效于null值。
date类型在数据库中的存储固定为4字节，与int类型一致。

insert into tab_date values(1,'1900-01-01');
insert into tab_date values(0,'1899-12-31');
insert into tab_date values(2,'0000-00-00');   -- date将显示null
insert into tab_date values(3,'9999-12-31');
insert into tab_date values(4,'0001-01-01');
insert into tab_date values(44663,44663);      -- date将显示2022-04-13

查询

select * from tab_date;

结果

       col1 col2       

          1 1900-01-01
          0 1899-12-31
          2           
          3 9999-12-31
          4 0001-01-01
      44663 2022-04-13

date数据类型转换为int类型

select col1,col2::int from tab_date;

结果

       col1 (expression) 

          1            1
          0            0
          2             
          3      2958464
          4      -693594
      44663        44663

1.2、datetime FIRST to LAST

datetime与date的区别是不仅可以保存日期，还能保存时间和小数秒，小数位数可以指定为1-5，所以最高精度可以到十万分之一秒。
datetime类型在数据库中的存储依据FIRST和LAST来确认。最少2字节，最大使用11字节存储。
其中：FIRST和LAST可使用year,month,day,hour,minute,second,fraction(N) (1<=N<=5)，FIRST要大于等于LAST。
常见的类型及存储字节长度

数据类型存储长度
datetime year to day5字节
datetime year to second8字节
datetime year to fraction(3)10字节
datetime year to fraction(5)11字节
datetime hour to second4字节

示例，创建表

-- 创建表
create table tab_dt
(
  col1 date,
  col2 datetime year to day,
  col3 datetime year to second,
  col4 datetime year to fraction(3),
  col5 datetime year to fraction(5),
  col6 datetime hour to second
);
-- 插入数据
insert into tab_dt values(
 current year to fraction(5),
 current year to fraction(5),
 current year to fraction(5),
 current year to fraction(5),
 current year to fraction(5),
 current year to fraction(5));

查询数据

select * from tab_dt;

结果

col1  2022-04-13
col2  2022-04-13
col3  2022-04-13 14:43:26
col4  2022-04-13 14:43:26.000
col5  2022-04-13 14:43:26.00000
col6  14:43:26

1.3、timestamp

timestamp是datetime year to second/fraction(N)的别名。
timestamp表示datetime year to second；
timestamp(N) (1<=N<=6)表示datetime year to fraction(N) (1<=N<=5)，特别说明：timestamp(6)实际将转换成datetime year to fraction(5)。
示例，创建表

create table tab_ts
(
  col1 timestamp,
  col2 timestamp(3),
  col3 timestamp(5),
  col4 timestamp(6)
);

insert into tab_ts values(
 current year to fraction(5),
 current year to fraction(5),
 current year to fraction(5),
 current year to fraction(5));

查询数据

select * from tab_ts;

结果

col1  2022-04-13 14:49:34
col2  2022-04-13 14:49:34.000
col3  2022-04-13 14:49:34.00000
col4  2022-04-13 14:49:34.00000

1.4、date和datetime数据类型内部存储验证

使用oncheck -pp 打印数据存储页
获取tab_dt表的partnum

select hex(partnum) from systables where tabname = 'tab_dt';

结果

(expression) 

0x0060009F

通过oncheck -pp TBLSpace PAGENUM dump页

oncheck -pp 0x60009f 0x1

结果

addr             stamp    chksum nslots flag type         frptr frcnt next     prev
6:4000           4133214  1ec7   1      801  DATA         66    1974  0        0       
        slot ptr   len   flg
        1    24    42    0  
slot   1:
    0:  0  0 ae 77 c7 14 16  4  d c7 14 16  4  d  e 2b   ...wG....G.....+
   16: 1a c7 14 16  4  d  e 2b 1a  0  0 c7 14 16  4  d   .G.....+...G....
   32:  e 2b 1a  0  0  0 c3  e 2b 1a                     .+....C.+.......
数据类型存储长度十六进制值解析值
date4字节0x0000ae7744663
datetime year to day5字节0xc71416040d20220413
datetime year to second8字节0xc71416040d0e2b1a20220413144326
datetime year to fraction(3)10字节0xc71416040d0e2b1a0000202204131443260000 截3位
datetime year to fraction(5)11字节0xc71416040d0e2b1a00000020220413144326000000 截5位
datetime hour to second4字节0xc30e2b1a144326

2、常见问题

2.1、如何取当前时间

sysdate -- 返回当前系统日期和时间，精确到十万分之一秒
current -- 返回当前系统日期和时间，精确到千分之一秒
current FIST to LAST -- 返回当前系统日期和时间，返回FIST to LAST的精度
today -- 返回当前系统日期，精确到天
示例，创建表

select
  sysdate,
  current,
  current year to second,
  today
from dual;

结果

(expression)  2022-04-13 15:25:17.65463
(expression)  2022-04-13 15:25:17.654
(expression)  2022-04-13 15:25:17
(expression)  2022-04-13

2.2、如何进行日期运算

日期型数据可以与数值加减得到新的日期，加减数值单位为天
sysdate + 1 -- 取明天的当前时间
sysdate - 1/24 -- 取当前时间的前一个小时
sysdate + 1 units day -- 取明天的当前时间
sysdate - 1 units hour -- 取当前时间的前一个小时
sysdate + interval(1 02:05) day to minute -- 取当前时间1天2小时5分后的时间
示例，创建表

select 
  sysdate + 1,
  sysdate - 1/24,
  sysdate + 1 units day,
  sysdate - 1 units hour,
  sysdate + interval(1 02:05) day to minute
from dual;

结果

(expression)  2022-04-14 15:29:46.65466
(expression)  2022-04-13 14:29:46.65466
(expression)  2022-04-14 15:29:46.65466
(expression)  2022-04-13 14:29:46.65466
(expression)  2022-04-14 17:34:46.65466

2.3、如何求两个日期的间隔时间

可以直接把两个日期相减，返回的数据类型为interval
示例语句

select 
  sysdate - date('2022-04-01') 
from dual;

结果

(expression)
       12 15:32:31.98025

特别注意，使用date()函数，date('2022-04-01')表示将字符串'2022-04-01'转换为日期，但date(2022-04-01)将转换成date(2022)。
如果需要两个日期相减的值为数值，需要自定义函数（转换）。可参考：https://gbasedbt.com/index.php/archives/339/

2.4、如何将日期转字符

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS.FF3')
gbase_to_char(sysdate,'%Y-%m-%d %H:%M:%S.%F3')
示例语句

select 
  to_char(sysdate,'YYYY-MM-DD HH24:MI:SS.FF3'),
  gbase_to_char(sysdate,'%Y-%m-%d %H:%M:%S.%F3')
from dual;

结果

(expression)  2022-04-13 15:43:28.263 
(expression)  2022-04-13 15:43:28.263

2.5、如何将字符转日期

to_date('2022-04-13 15:01:54.123','YYYY-MM-DD HH24:MI:SS.FF3')
gbase_to_date('2022-04-13 15:01:54.123','%Y-%m-%d %H:%M:%S.%F3')
示例语句

select 
  to_date('2022-04-13 15:01:54.123','YYYY-MM-DD HH24:MI:SS.FF3'),
  gbase_to_date('2022-04-13 15:01:54.123','%Y-%m-%d %H:%M:%S.%F3')
from dual;

结果

(expression)  2022-04-13 15:01:54.12300              
(constant)    2022-04-13 15:01:54.12300

3、常用日期函数

3.1、TO_CHAR(DATE,FMT) 或者GBASE_TO_CHAR(DATE,FMT) --格式化日期成字符

格式备注：
HH表示12小时进制，HH24表示采用24小时进制，MM表示月份，MI表示分钟；
示例语句

select 
  to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),
  gbase_to_char(sysdate,'%Y-%m-%d %H:%M:%S')
from dual;

3.2、TO_DATE(CHAR,FORMATSTR) --将字符转换成日期

to_date('2022-04-13 15:01:54','YYYY-MM-DD HH24:MI:SS')
gbase_to_date('2022-04-13 15:01:54','%Y-%m-%d %H:%M:%S')
格式备注：
HH表示12小时进制，HH24表示采用24小时进制，MM表示月份，MI表示分钟；
示例语句

select 
  to_date('2022-04-13 15:01:54','YYYY-MM-DD HH24:MI:SS'),
  gbase_to_date('2022-04-13 15:01:54','%Y-%m-%d %H:%M:%S')
from dual;

3.3、TRUNC(DATE,FMTS) -- 返回截断后的日期值

FMTS对于datetime类型，可接受year,month,dd,day,hh,mi
FMTS对于date类型，可接受year,month,dd,day
dd表示截取当天的00:00，day表示截取本周所在周日的00:00
date示例语句

select 
  today, 
  trunc(today,'dd'), 
  trunc(today,'day') 
from dual;

结果

(expression)  2022-04-13
(expression)  2022-04-13
(expression)  2022-04-10

datetime示例语句

select 
  sysdate, 
  trunc(sysdate,'dd'), 
  trunc(sysdate,'day') 
from dual;

结果

(expression)  2022-04-13 16:05:08.22697
(expression)  2022-04-13 00:00
(expression)  2022-04-10 00:00

3.4、返回DATE的某一部份内容

YEAR(DATE) -- 返回年份
MONTH(DATE) -- 返回月份
DAY(DATE) -- 返回天数
WEEKDAY(DATE) -- 返回周几，0表示周日
QUARTER(DATE) -- 返回季数
示例语句

select 
  year(sysdate),
  month(sysdate),
  day(sysdate),
  weekday(sysdate),
  quarter(sysdate)
from dual;

结果

(expression)      2022
(expression)         4
(expression)        13
(expression)         3
(expression)         2

3.5、ADD_MONTHS(DATE,MONTHS) --在DATE增加月份得到新日期

ADD_MONTHS(sysdate,3) --当前日期加3个月
ADD_MONTHS是一个比较有意思的函数，它会自动处理大小月及闰月，如下所示
示例语句

select 
  ADD_MONTHS(date('2010-02-12'), 1),
  ADD_MONTHS(date('2010-02-27'), 1),
  ADD_MONTHS(date('2010-02-28'), 1),  -- 将显示2010-03-28，而不是2010-03-31
  ADD_MONTHS(date('2010-01-31'), 1),
  ADD_MONTHS(date('2010-03-31'), 1)
from dual;

结果

(constant)  2010-03-12 
(constant)  2010-03-27 
(constant)  2010-03-28 
(constant)  2010-02-28
(constant)  2010-03-30

3.6、LAST_DAY(DATE) --返回日期所在月份的最后一天日期

对于date类型，将返回本月最后一天的日期值；
对于datetime 类型，将返回本周最后一天的当前时间值；
示例语句

select 
  LAST_DAY(today),
  LAST_DAY(sysdate)
from dual;

结果

(expression)  2022-04-30   
(expression)  2022-04-30 16:23:59.22870

3.7、NEXT_DAY(DATE,CHAR) --从给定日期开始返回下个CHAR指定星期的日期

CHAR可以使用全称或者三字线简写，如：Monday/Mon,Sunday/Sun
对于date类型，将返回下一个指定周几的日期值;
对于datetime 类型，将返回下一个指定周几的当前时间值；
示例语句

select
  NEXT_DAY(today,'FRIDAY'),
  NEXT_DAY(sysdate,'FRI')
from dual;

结果

(expression)  2022-04-15   
(expression)  2022-04-15 16:28:31.19810

3.8、TO_YMINTERVAL(CHAR)和TO_DSINTERVAL(CHAR)

TO_YMINTERVAL(CHAR) --返回[年-月]格式构成的时间间隔，一般用于日期加减运算
TO_DSINTERVAL(CHAR) --返回[天 时:分:秒]格式构成的时间间隔，一般用于日期加减运算
示例语句

select
  sysdate,
  sysdate + TO_YMINTERVAL('01-02'),
  sysdate + TO_DSINTERVAL('3 04:05:05')
from dual;

结果

(expression)  2022-04-13 16:32:05.63342            
(expression)  2023-06-13 16:32:05.63342             
(expression)  2022-04-16 20:37:10.63342

3.9、NUMTOYMINTERVAL(N,CHAR)和NUMTODSINTERVAL(N,CHAR)

NUMTOYMINTERVAL(N,CHAR) --返回CHAR中指定单位的时间间隔数值，一般用于日期加减运算
CHAR可以为YEAR,MONTH
NUMTODSINTERVAL(N,CHAR) --返回CHAR中指定单位的时间间隔数值，一般用于日期加减运算
CHAR可以为DAY,HOUR,MINUTE,SECOND
示例语句

select
  sysdate,
  sysdate + NUMTOYMINTERVAL(1,'month'),
  sysdate + NUMTOYMINTERVAL(3,'year'),
  sysdate + NUMTODSINTERVAL(1,'day'),
  sysdate + NUMTODSINTERVAL(2,'hour'),
  sysdate + NUMTODSINTERVAL(3,'minute'),
  sysdate + NUMTODSINTERVAL(4,'second')
from dual;

结果

(expression)  2022-04-13 16:37:31.75430
(expression)  2022-05-13 16:37:31.75430
(expression)  2025-04-13 16:37:31.75430
(expression)  2022-04-14 16:37:31.75430
(expression)  2022-04-13 18:37:31.75430
(expression)  2022-04-13 16:40:31.75430
(expression)  2022-04-13 16:37:35.75430

标签: 数据类型, GBase, datetime, timestamp, 日期时间

相关文章推荐

添加新评论