[大数据] GBase 8s 日期时间数据类型

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> GBase 8s 日期时间数据类型 -> 正文阅读

[大数据]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
		  5 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 day	5字节
datetime year to second	8字节
datetime year to fraction(3)	10字节
datetime year to fraction(5)	11字节
datetime hour to second	4字节

示例，创建表

-- 创建表
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.+.......

数据类型	存储长度	十六进制值	解析值
date	4字节	0x0000ae77	44663
datetime year to day	5字节	0xc71416040d	20220413
datetime year to second	8字节	0xc71416040d0e2b1a	20220413144326
datetime year to fraction(3)	10字节	0xc71416040d0e2b1a0000	202204131443260000 截3位
datetime year to fraction(5)	11字节	0xc71416040d0e2b1a000000	20220413144326000000 截5位
datetime hour to second	4字节	0xc30e2b1a	144326

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