活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[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类型一致。





  
```sql
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
```
查询  
```sql
select * from tab_date;
```
结果  
```text
       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类型  
```sql		  
select col1,col2::int from tab_date;
```
结果  
```text
       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字节|

示例，创建表  
```sql
-- 创建表
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));
```
查询数据  
```sql
select * from tab_dt;
```
结果  
```text
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)。  
示例，创建表  
```sql
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));
```
查询数据  
```sql
select * from tab_ts;
```
结果  
```text
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  
```sql
select hex(partnum) from systables where tabname = 'tab_dt';
```
结果  
```text
(expression) 

0x0060009F
```

通过oncheck -pp TBLSpace PAGENUM dump页  
```shell
oncheck -pp 0x60009f 0x1
```
结果  
```text
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    -- 返回当前系统日期，精确到天  
示例，创建表  
```sql
select
  sysdate,
  current,
  current year to second,
  today
from dual;
```
结果  
```text
(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分后的时间  
示例，创建表  
```sql
select 
  sysdate + 1,
  sysdate - 1/24,
  sysdate + 1 units day,
  sysdate - 1 units hour,
  sysdate + interval(1 02:05) day to minute
from dual;
```
结果  
```text
(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  
示例语句  
```sql
select 
  sysdate - date('2022-04-01') 
from dual;
```
结果  
```text            
(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')  
示例语句  
```sql
select 
  to_char(sysdate,'YYYY-MM-DD HH24:MI:SS.FF3'),
  gbase_to_char(sysdate,'%Y-%m-%d %H:%M:%S.%F3')
from dual;
```
结果  
```text
(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')  
示例语句  
```sql
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;
```
结果  
```text
(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表示分钟；  
示例语句  
```sql
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表示分钟；
示例语句  
```sql
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示例语句  
```sql
select 
  today, 
  trunc(today,'dd'), 
  trunc(today,'day') 
from dual;
```
结果  
```text
(expression)  2022-04-13
(expression)  2022-04-13
(expression)  2022-04-10
```
datetime示例语句  
```sql
select 
  sysdate, 
  trunc(sysdate,'dd'), 
  trunc(sysdate,'day') 
from dual;
```
结果  
```text
(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) -- 返回季数  
示例语句  
```sql
select 
  year(sysdate),
  month(sysdate),
  day(sysdate),
  weekday(sysdate),
  quarter(sysdate)
from dual;
```
结果  
```text
(expression)      2022
(expression)         4
(expression)        13
(expression)         3
(expression)         2
```

#### 3.5、ADD_MONTHS(DATE,MONTHS) --在DATE增加月份得到新日期  
ADD_MONTHS(sysdate,3) --当前日期加3个月  
ADD_MONTHS是一个比较有意思的函数，它会自动处理大小月及闰月，如下所示  
示例语句  
```sql
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;
```
结果  
```text
(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 类型，将返回本周最后一天的当前时间值；  
示例语句  
```sql
select 
  LAST_DAY(today),
  LAST_DAY(sysdate)
from dual;
```
结果  
```text
(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 类型，将返回下一个指定周几的当前时间值；  
示例语句  
```sql
select
  NEXT_DAY(today,'FRIDAY'),
  NEXT_DAY(sysdate,'FRI')
from dual;
```
结果  
```text
(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) --返回[天 时:分:秒]格式构成的时间间隔，一般用于日期加减运算  
示例语句  
```sql
select
  sysdate,
  sysdate + TO_YMINTERVAL('01-02'),
  sysdate + TO_DSINTERVAL('3 04:05:05')
from dual;
```
结果  
```text
(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  
示例语句  
```sql
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;  
```
结果  
```text
(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 8s 日期时间数据类型

GBase 8s 日期时间数据类型

评论

热门帖子