GBase 8c
其他
文章

GBase 8c 数据类型——时间日期操作符

GBase用户13139
发表于2023-12-07 12:57:4022次浏览1个评论

在使用时间和日期操作符时，需要使用明确的类型前缀修饰对应的操作数，以确保数据库解析准确。
 

 

例如，没有明确数据类型，就会出现异常错误。

gbase=# SELECT date '2001-10-01' - '7' AS RESULT;

ERROR:  invalid input syntax for type timestamp: "7"

LINE 1: SELECT date '2001-10-01' - '7' AS RESULT

                                   ^

CONTEXT:  referenced column: result

表  时间和日期操作符

操作符

示例

+

gbase=# SELECT date '2001-9-28' + integer '7' AS RESULT;

       result

---------------------

 2001-10-05 00:00:00

(1 row)

gbase=# SELECT date '2001-09-28' + interval '1 hour' AS RESULT;

       result

---------------------

2001-09-28 01:00:00

(1 row)

gbase=# SELECT date '2001-09-28' + time '03:00' AS RESULT;

       result

---------------------

2001-09-28 03:00:00

(1 row)

gbase=# SELECT interval '1 day' + interval '1 hour' AS RESULT;

       result

---------------------

1 day 01:00:00

(1 row)

gbase=# SELECT timestamp '2001-09-28 01:00' + interval '23 hours' AS RESULT;

       result

---------------------

2001-09-29 00:00:00

(1 row)

gbase=# SELECT time '01:00' + interval '3 hours' AS RESULT;

  result

----------

 04:00:00

(1 row)

-

gbase=# SELECT date '2001-10-01' - date '2001-09-28' AS RESULT;

result

--------

3days

(1 row)

gbase=# SELECT date '2001-10-01' - integer '7' AS RESULT;

       result

---------------------

 2001-09-24 00:00:00

(1 row)

gbase=# SELECT date '2001-09-28' - interval '1 hour' AS RESULT;

       result

---------------------

 2001-09-27 23:00:00

(1 row)

gbase=# SELECT time '05:00' - time '03:00' AS RESULT;

result

----------

02:00:00

(1 row)

 

gbase=# SELECT time '05:00' - interval '2 hours' AS RESULT;

result

----------

03:00:00

(1 row)

gbase=# SELECT timestamp '2001-09-28 23:00' - interval '23 hours' AS RESULT;

       result

---------------------

 2001-09-28 00:00:00

(1 row)

gbase=# SELECT interval '1 day' - interval '1 hour' AS RESULT;

result

----------

23:00:00

(1 row)

gbase=# SELECT timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' AS RESULT;

     result

----------------

 1 day 15:00:00

(1 row)

*

gbase=# SELECT 900 * interval '1 second' AS RESULT;

result

----------

00:15:00

(1 row)

gbase=# SELECT 21 * interval '1 day' AS RESULT;

result

---------

21 days

(1 row)

gbase=# SELECT double precision '3.5' * interval '1 hour' AS RESULT;

result

----------

03:30:00

(1 row)

/

gbase=# SELECT interval '1 hour' / double precision '1.5' AS RESULT;

result

----------

00:40:00

(1 row)

 

gbase=# SELECT time '05:00' - interval '2 hours' AS RESULT;

result

----------

03:00:00

(1 row)

 

评论

登录后才可以发表评论
崔哥发表于 1个月前
阴阴溪曲绿交加，小雨翻萍上浅沙。鹅鸭不知春去尽，争随流水趁桃花。

热门帖子