在使用时间和日期操作符时，需要使用明确的类型前缀修饰对应的操作数，以确保数据库解析准确。
例如，没有明确数据类型，就会出现异常错误。
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) |
