openGauss 时间日期操作符示例

最新推荐文章于 2024-11-08 08:46:48 发布

m0_61937892

最新推荐文章于 2024-11-08 08:46:48 发布

阅读量920

点赞数

分类专栏： openGauss 文章标签：数据库

本文链接：https://blog.youkuaiyun.com/m0_61937892/article/details/128077547

版权

34 篇文章

订阅专栏

用户在使用时间和日期操作符时，对应的操作数请使用明确的类型前缀修饰，以确保数据库在解析操作数的时候能够与用户预期一致，不会产生用户非预期的结果。

比如下面示例没有明确数据类型就会出现异常错误。

SELECT date '2001-10-01' - '7' AS RESULT;

时间和日期操作符： 

"+"

openGauss=# SELECT date '2001-9-28' + integer '7' AS RESULT;
result
\------------
2001-10-05 00:00:00
(1 row)

openGauss=# SELECT date '2001-09-28' + interval '1 hour' AS RESULT;
result
\---------------------
2001-09-28 01:00:00
(1 row)

openGauss=# SELECT date '2001-09-28' + time '03:00' AS RESULT;
result
\--------------------
2001-09-28 03:00:00
(1 row)

openGauss=# SELECT interval '1 day' + interval '1 hour' AS RESULT;
result
\----------------
1 day 01:00:00
(1 row)

openGauss=# SELECT timestamp '2001-09-28 01:00' + interval '23 hours' AS RESULT;
result
\---------------------
2001-09-29 00:00:00
(1 row)

openGauss=# SELECT time '01:00' + interval '3 hours' AS RESULT;
result
\----------
04:00:00
(1 row)

"-"

openGauss=# SELECT date '2001-10-01' - date '2001-09-28' AS RESULT;
result
\--------
3 days
(1 row)

openGauss=# SELECT date '2001-10-01' - integer '7' AS RESULT;
result
\---------------------
2001-09-24 00:00:00
(1 row)

openGauss=# SELECT date '2001-09-28' - interval '1 hour' AS RESULT;
result
\---------------------
2001-09-27 23:00:00
(1 row)

openGauss=# SELECT time '05:00' - time '03:00' AS RESULT;
result
\----------
02:00:00
(1 row)

openGauss=# SELECT time '05:00' - interval '2 hours' AS RESULT;
result
\----------
03:00:00
(1 row)

openGauss=# SELECT timestamp '2001-09-28 23:00' - interval '23 hours' AS RESULT;
result
\---------------------
2001-09-28 00:00:00
(1 row)

openGauss=# SELECT interval '1 day' - interval '1 hour' AS RESULT;
result
\----------
23:00:00
(1 row)

openGauss=# SELECT timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' AS RESULT;
result
\----------------
1 day 15:00:00
(1 row)

"*"

openGauss=# SELECT 900 * interval '1 second' AS RESULT;
result
\----------
00:15:00
(1 row)

openGauss=# SELECT 21 * interval '1 day' AS RESULT;
result
\---------
21 days
(1 row)

openGauss=# SELECT double precision '3.5' * interval '1 hour' AS RESULT;
result
\----------
03:30:00
(1 row)

"/"

openGauss=# SELECT interval '1 hour' / double precision '1.5' AS RESULT;
result
\----------
00:40:00
(1 row)