openGauss插件使用指南:函数和操作符—时间和日期处理函数和操作符（中）

最新推荐文章于 2024-08-21 11:43:07 发布

openGauss小助手

最新推荐文章于 2024-08-21 11:43:07 发布

阅读量810

点赞数 18

文章标签：数据库 openGauss

本文链接：https://blog.csdn.net/weixin_53596073/article/details/140172833

版权

MAKETIME()

函数原型:

TIME MAKETIME(int8 hour, int8 minue, Numeric second)

功能描述：

给定小时、分钟和秒参数，返回TIME类型值。

备注:
- 当参数满足如下任一条件时，函数返回NULL:
  1. minue < 0 or minue >= 60
  2. second < 0 or second >= 60
  3. 任一参数为NULL
- 返回的Time结果保留6位小数，若second超出六位小数，则按照四舍五入进位。
- 返回TIME类型值要求在[-838:59:59, 838:59:59]中。若超出范围，则根据hour的正负类型，来返回指定的边界值。
示例:

 openGauss=# SELECT MAKETIME(15, 15, 15.5);
  maketime  
------------
 15:15:15.5
(1 row)

-- 四舍五入进位
 openGauss=# SELECT MAKETIME(10, 15, 20.5000005);
    maketime     
-----------------
 10:15:20.500001
(1 row)

-- 超出边界值
 openGauss=# SELECT MAKETIME(839,0,0);
 maketime  
-----------
 838:59:59
(1 row)

microsecond(timestamp)

描述：获取日期/时间值中微秒的值。

返回值类型：double precision

示例：
```
openGauss=# SELECT microsecond(timestamp '2001-02-16 20:38:40.123');
 date_part
-----------
    123000
(1 row)
```
minute(timestamp)

描述：获取日期/时间值中分钟的值。

返回值类型：double precision

示例：
```
openGauss=# SELECT minute(timestamp '2001-02-16 20:38:40.123');
 date_part
-----------
        38
(1 row)
```

now(n)

描述：返回语句开始执行的时间戳，n为精度，最大值取6。

返回值类型：datetime

示例：

openGauss=# select now(3);
        now(3)
-------------------------
2022-07-21 17:30:18.037
(1 row)

openGauss=# select now();
        now()
---------------------
2022-07-21 17:30:51
(1 row)

PERIOD_ADD()

函数原型:

int8 PERIOD_ADD(int8 P, int8 N)

功能描述：

返回时期P(格式为YYYYMM或YYMM)加上N个月后的时期值，格式为YYYYMM。

备注:
- 当任一参数为NULL时，函数返回NULL。
- P = 0时，返回0。
- 参数的时期P与返回结果的时期中的年份小于100时，会以70为边界，将年份转为20XX年或19XX年。
- 若入参为小数格式的字符串时，本函数会按照四舍五入进位转为整数后进行处理，而在mysql中，会将参数的小数部分舍去。例如: period_add('202101.4', '10.5')在opengauss中结果与period_add(202101, 11)相同，而在mysql中，则视为period_add(202101, 10)
示例:

 openGauss=# SELECT PERIOD_ADD(202201, 2);
  period_add 
------------
     202203
(1 row)
 
 -- p = 0
 openGauss=# SELECT PERIOD_ADD(0, 2);
 period_add 
------------
          0
(1 row)

 -- 时期的年份处于[0,70) 或 [70, 100)范围内
 openGauss=# SELECT PERIOD_ADD(0101, 2), PERIOD_ADD(7001, 2);
 period_add | period_add 
------------+------------
     200103 |     197003
(1 row)

PERIOD_DIFF()

函数原型:

int8 PERIOD_DIFF(int8 P1, int8 P2)

功能描述：

返回两个时期参数P1与P2的月份数差值。

备注:
- 当任一参数为NULL时，函数返回NULL。
- 时期参数P1和P2中的年份小于100时，会以70为边界，将年份转为20XX年或19XX年。
- 若入参为小数格式的字符串时，本函数会按照四舍五入进位转为整数后进行处理，而在mysql中，会将参数的小数部分舍去。例如: period_diff('202101.4', '202102.5')在opengauss中结果与period_diff(202101, 202103)相同，而在mysql中，则被视为period_diff(202101, 202102)。
示例:

 openGauss=# SELECT PERIOD_DIFF(202201,202003);
  period_diff 
-------------
          22
(1 row)

 openGauss=# SELECT PERIOD_DIFF(0101,7001);
 period_diff 
-------------
         372
(1 row)

quarter(timestamp)

描述：获取日期/时间值中的季度数，从1到4。

返回值类型：double precision

示例：
```
openGauss=# SELECT quarter(timestamp '2001-02-16 20:38:40.123');
 date_part
-----------
        1
(1 row)
```

second(timestamp)

描述：获取日期/时间值中的秒数。

返回值类型：double precision

示例：

openGauss=# SELECT second(timestamp '2001-02-16 20:38:40.123');
 date_part
-----------
       40
(1 row)

SEC_TO_TIME()

函数原型:

TIME SEC_TO_TIME(Numeric second)

功能描述：

给定秒数，将其转为小时、分钟与秒，返回TIME类型值。

备注:
- 当任一参数为NULL，函数返回NULL。
- 返回TIME类型值只保留小数点后6位，超出部分按照四舍五入规则进位。
- 返回TIME类型值要求在[-838:59:59, 838:59:59]中。若超出范围，则根据second的正负类型，来返回指定的边界值。
示例:

 openGauss=# SELECT SEC_TO_TIME(4396);
 sec_to_time 
-------------
 01:13:16
(1 row)

-- 四舍五入进位
 openGauss=# SELECT SEC_TO_TIME(2378.2222225);
   sec_to_time   
-----------------
 00:39:38.222223
(1 row)

-- 返回结果超出范围
 openGauss=# SELECT SEC_TO_TIME(3888888);
 sec_to_time 
-------------
 838:59:59
(1 row)

SUBDATE(expr, interval)

函数原型:

Text SUBDATE(text date, INTERVAL expr unit)

Text SUBDATE(text date, int8 days)

功能描述：

该函数执行日期运算。参数date指定开始DATE或DATETIME类型值。指定要从开始日期减去的INTERVAL值，返回相减后的结果日期值。若第二参数为整数，则将其视为减去的天数值。

备注:
- 函数返回格式为DATE或DATETIME。一般情况下，返回类型与第一参数的类型相同。当第一参数的类型为DATE时且INTERVAL的单位包含HOUR、MINUTE、SECOND部分，则返回结果为DATETIME。
- 本函数所支持的interval单位与openGauss支持的interval单位保持一致，包括YEAR，MONTH，DAY，HOUR，MINUTE，SECOND，DAY TO HOUR，DAY TO MINUTE，DAY TO SECOND，HOUR TO MINUTE，HOUR TO SECOND，MINUTE TO SECOND。
- 任一参数为NULL，函数返回NULL。
- 在下列情况中，函数报错（该特性兼容此函数在mysql的insert语句中的行为）：
  - 参数date的日期超出范围[0000-1-1, 9999-12-31]
  - 当interval单位为仅与年或月相关时，返回结果的日期超出范围[0000-1-1, 9999-12-31]
  - 其他interval单位，返回结果的日期超出范围[0001-1-1, 9999-12-31]
示例:

 openGauss=# SELECT SUBDATE('2022-01-01', INTERVAL 31 DAY), SUBDATE('2022-01-01', 31);
  subdate   |  subdate   
------------+------------
 2021-12-01 | 2021-12-01
(1 row)

-- 第一参数为DATE
 openGauss=# SELECT SUBDATE('2022-01-01', INTERVAL 1 YEAR);
  subdate   
------------
 2021-01-01
(1 row)

-- 第一参数为DATETIME
 openGauss=# SELECT SUBDATE('2022-01-01 01:01:01', INTERVAL 1 YEAR);
       subdate       
---------------------
 2021-01-01 01:01:01
(1 row)

-- 第一参数为DATE但是INTERVAL的单位包含TIME部分
 openGauss=# SELECT SUBDATE('2022-01-01', INTERVAL 1 SECOND);
       subdate       
---------------------
 2021-12-31 23:59:59
(1 row)

SUBDATE(TIME, interval)

函数原型:

TIME SUBDATE(TIME time, INTERVAL expr unit)

TIME SUBDATE(TIME time, int8 days)

功能描述：

该函数是为兼容MySql中subdate函数的第一参数类型可以为TIME的情况。该情况下，第一参数的输入必须为原始的TIME数据，而非由字符串的隐式转换而来。参数time指定开始TIME类型的时间值，第二参数指定要从开始时间减去的INTERVAL值，返回相减后的结果日期值。若第二参数为整数，则将其视为减去的天数值。

备注:
- 第一参数必须为原始的TIME类型，而非由字符串的隐式转换而来。如SUBDATE('1:1:1', 1)并不会进入此函数。需改为SUBDATE(time'1:1:1', 1)。
- 在如下情况下，函数报错（该特性兼容此函数在mysql的insert语句中的行为）：
  - 第二参数的INTERVAL单位包含年或月部分
  - 函数返回TIME值超出[-838:59:59, 838:59:59]
示例:

 openGauss=# SELECT SUBDATE(time'10:15:20', INTERVAL '1' DAY), SUBDATE(time'10:15:20', 1);
   subdate  |  subdate  
-----------+-----------
 -13:44:40 | -13:44:40
(1 row)

-- 第二参数的INTERVAL单位不能包含年或月部分
 openGauss=# SELECT SUBDATE(time'838:00:00', INTERVAL '1' MONTH);
ERROR:  time field value out of range
CONTEXT:  referenced column: subdate

-- 结果超出范围
 openGauss=# SELECT SUBDATE(time'838:59:59', INTERVAL '-1' SECOND);
ERROR:  time field value out of range
CONTEXT:  referenced column: subdate

SUBTIME()

函数原型:

TEXT SUBTIME(TIME time1, TIME time2)

TEXT SUBTIME(DATETIME time1, TIME time2)

功能描述：

该函数执行日期运算，返回DATETIME或TIME表达式time1减去TIME表达式time2的结果。返回结果类型与time1传入类型保持一致。

备注:
- 在如下情况下，函数报错：
  - time1为不为合法的TIME格式或者DATETIME格式
  - time2为不为合法的TIME格式
  - 返回DATETIME值时，结果超过[0000-01-01 00:00:00.000000, 9999-12-31 23:59:59.999999]
  - 返回TIME值时，结果超过[-838:59:59, 838:59:59]
示例:
```
openGauss=# select subtime('11:22:33','10:20:30');
subtime  
----------
01:02:03
(1 row)

openGauss=# select SUBTIME('2020-03-04 11:22:33', '-10:20:30');
    subtime       
---------------------
2020-03-04 21:43:03
(1 row)
```

sysdate(n)

描述：返回系统实时时间戳，n为精度，最大值取6。

返回值类型：datetime

示例：

openGauss=# select sysdate(3);
    sysdate(3)
-------------------------
2022-07-21 17:38:23.442
(1 row)

openGauss=# select sysdate();
    sysdate()
---------------------
2022-07-21 17:39:02
(1 row)

time(expr)

功能描述：

参数指定一个TIME或DATETIME表达式expr，提取其中的time表达式并返回为字符串。

备注:
- 返回的时间表达式中最多保留六位小数，并且不显示小数部分尾部的0
- 对于异常的日期或时间格式或是域溢出的日期或时间(如1:60:60、2022-12-32 等)，本函数兼容mysql中该函数于insert语句中的表现，即报错。
- date格式字符串将会报错，而date类型参数将会返回"00:00:00"。
示例:
```
openGauss=# select time('2022-1-1 1:1:1.1111116'), time('25:25:25');
      time       |   time   
-----------------+----------
01:01:01.111112 | 25:25:25
(1 row)

openGauss=# select time(date'2022-1-1');
  time   
----------
00:00:00
(1 row)
```
TIMEDIFF()

函数原型:

TIME TIMEDIFF(TIME time1, TIME time2)

TIME TIMEDIFF(DATETIME datetime1, DATETIME datetime2)

功能描述：

该函数执行DATETIME或TIME 类型值间的减法运算，计算DATETIME或TIME 间的时间差值，运算结果以TIME类型值返回。

备注:
- time1, time2所对应类型需要一致，否则返回NULL。
- 若出现下列情况，函数报错（该特性兼容此函数在mysql的insert语句中的行为）：
  - TIME类型入参超出[-838:59:59, 838:59:59]范围或格式不合法
  - DATETIME类型入参超出[0000-01-01 00:00:00.000000, 9999-12-31 23:59:59.999999]范围或格式不合法
  - 对于DATETIME格式的字符串入参，本函数支持0值日期，如'2000-0-1 1:1:1'
  - 返回值超出[-838:59:59, 838:59:59]范围
示例:
```
openGauss=# select TIMEDIFF(time'23:59:59',time'01:01:01'), TIMEDIFF(datetime'2008-12-31 23:59:59',datetime'2008-12-30 01:01:01');
 timediff | timediff
----------+----------
 22:58:58 | 46:58:58
(1 row)

-- 两参数对应类型不一致
opengauss=# select timediff('2000-1-1 0:0:0', '0:0:0'), timediff(time'0:0:0', datetime'2000-1-1 0:0:0');
 timediff | timediff 
----------+----------
          | 
(1 row)

-- 返回值超出范围时报错。
openGauss=# select timediff(time'-830:00:00', time'10:20:30');
ERROR:  time field value out of range
CONTEXT:  referenced column: timediff
```

TIMESTAMP()

函数原型:

DATETIME TIMESTAMP(TEXT expr)

DATETIME TIMESTAMP(TEXT expr, TIME time)

功能描述：

只有一个参数时，函数将DATE或DATETIME表达式expr转为DATETIME值返回。

有两个参数时，函数计算DATE或DATETIME表达式expr加上TIME类型值time的结果并以DATETIME值返回。

备注:

expr为不存在的date或datetime表达式时。如'2000-12-32'，'2000-1-1 24:00:00'等，函数报错。
入参或返回值超出指定时间范围，函数报错
包含两个参数且第二参数time为非TIME格式字符串时，函数报错。

示例:

openGauss=# select TIMESTAMP('2022-01-01'), TIMESTAMP('20220101');
      timestamp      |      timestamp      
---------------------+---------------------
2022-01-01 00:00:00 | 2022-01-01 00:00:00
(1 row)

openGauss=# select TIMESTAMP('2022-01-31 12:00:00.123456'), TIMESTAMP('20000229120000.1234567');
        timestamp          |         timestamp          
----------------------------+----------------------------
2022-01-31 12:00:00.123456 | 2000-02-29 12:00:00.123457
(1 row)

openGauss=# select TIMESTAMP('2022-01-31','12:00:00.123456'), TIMESTAMP('2022-01-31 12:00:00','-32:00:00');
        timestamp          |      timestamp      
----------------------------+---------------------
2022-01-31 12:00:00.123456 | 2022-01-30 04:00:00
(1 row)

openGauss=# select TIMESTAMP('20000229','100:00:00'), TIMESTAMP('20000229120000.123','100:00:00');
     timestamp      |       timestamp
---------------------+-------------------------
 2000-03-04 04:00:00 | 2000-03-04 16:00:00.123
(1 row)

timestampadd(unit, span, expr)

函数原型：

TEXT TIMESTAMPADD(text unit, interval span, text datetime)

功能描述：

将一段时间加到已知的时间点上。其中第一个参数unit是时间单位，第二个参数span是具体数值，第三个参数datetime是已知时间点。

备注：

unit支持的单位如下：

单位	对应的输入
年	year, years, y, yr, yrs, yyyy
季度	quarter, qtr, q
月	month, months, mm, mon, mons
周	week, weeks, w
日	day, days, d, dd, ddd, j
时	hour, hours, h, hh, hr, hrs
分	minute, minutes, m, mi, min, mins
秒	second, seconds, s, sec, secs
微秒	microsecond, microsecon, us, usec, usecond, useconds, usecs

span支持小数。当unit为秒时，span根据第七位小数，四舍五入到六位小数；否则span四舍五入到整数。
datetime的输入类型可以是字符串、date、datetime、time。
datetime的输入范围和函数的计算结果均需落在区间[0001-01-01 00:00:00.000000, 9999-12-31 23:59:59.999999]内，否则报错(兼容mysql中该函数于insert语句的表现)。
对于异常的日期或时间格式或是域溢出的日期或时间(如1:60:60、2022-12-32 等)，本函数兼容mysql中该函数于insert语句中的表现，即报错。

示例：

openGauss=# select timestampadd(day, 1, '2022-09-01');
 timestampadd 
--------------
 2022-09-02
(1 row)

openGauss=# select timestampadd(hour, 1, '2022-09-01 08:00:00');
    timestampadd     
---------------------
 2022-09-01 09:00:00
(1 row)