日期/时间/时间戳/时间间隔操作符¶
神通数据库中支持的日期/时间/时间戳操作符如下表：
表33神通数据库支持的日期/时间/时间戳/时间间隔操作符
| 名字 | 左操作数类型 | 右操作数类型 | 例子 | 结果 |
|---|---|---|---|---|
| DATE | INT | date '2001-09-28' + 1::int | DATE :2001-09-29 | |
| DATE | FLOAT | date '2001-09-28' + 1.2::float | TIMESTAMP: 2001-09-29 04:47:59 |
|
| DATE | INTERVAL YEAR TO MONTH | date '2001-09-28' + interval '1' year | DATE: 2002-09-28 | |
| DATE | INTERVAL DAY TO SECOND | date '2001-09-28' + interval '1' hour | DATE : 2001-09-28 | |
| DATE | TIME | date '2001-09-28' + time '1:2:3' | TIMESTAMP : 2001-09-28 01:02:03 |
|
| DATE | TIME WITH TIME ZONE | date '2001-09-28' + time with time zone '1:2:3 +2:00' | TIMESTAMP WITH TIME ZONE: 2001-09-28 07:02:03+08:00 | |
| TIME | DATE | time '1:2:3' + date '2001-09-28' | TIMESTAMP: 2001-09-28 01:02:03 |
|
| TIME WITH TIME ZONE | DATE | time with time zone '1:2:3 +2:00' + date '2001-09-28' | TIMESTAMP WITH TIME ZONE: 2001-09-28 07:02:03+08:00 |
|
| TIMESTAMP | INTERVAL YEAR TO MONTH | timestamp '2001-09-28 01:00' + interval '23'year | TIMESTAMP: 2024-09-28 01:00:00 |
|
| TIMESTAMP | INTERVAL DAY TO SECOND | timestamp '2001-09-28 01:00' + interval '23'hour | TIMESTAMP: 2001-09-29 00:00:00 |
|
| TIMESTAMP WITH TIME ZONE | INTERVAL YEAR TO MONTH | timestamp withtime zone '2001-09-28 01:00 +2:00' + interval '23'year | TIMESTAMP WITH TIME ZONE: 2024-09-2807:00:00+08:00 |
|
| TIMESTAMP WITH TIME ZONE | INTERVAL DAY TO SECOND | timestamp with time zone '2001-09-28 01:00 +2:00' + interval '23'hour | TIMESTAMP WITH TIME ZONE: 2001-09-29 06:00:00+08:00 |
|
| INTERVAL YEAR TO MONTH | TIMESTAMP | interval '23'year + timestamp '2001-09-28 01:00' | TIMESTAMP: 2024-09-28 01:00:00 |
|
| INTERVAL DAY TO SECOND | TIMESTAMP | interval '23'hour +timestamp '2001-09-28 01:00' | TIMESTAMP: 2001-09-29 00:00:00 |
|
| INTERVAL YEAR TO MONTH | INTERVAL YEAR TO MONTH | interval '1-11' year to month + interval '1'month | INTERVAL YEAR TO MONTH:+2-00 | |
| INTERVAL DAY TO SECOND | INTERVAL DAY TO SECOND | interval '1' day + interval '1 2:3:4' day to second | INTERVAL DAY TO SECOND:+2 02:03:04 | |
| DATE | INT | date '2001-09-28' - 1::int | DATE : 2001-09-27 | |
| DATE | FLOAT | date '2001-09-28' - 1.2::float | TIMESTAMP: 2001-09-26 19:12:01 |
|
| DATE | DATE | date '2001-09-28' - date '2002-11-12' | INT: -410 | |
| DATE | INTERVAL YEAR TO MONTH | date '2001-09-28' - interval '1' year | DATE: 2000-09-28 | |
| DATE | INTERVAL DAY TO SECOND | date '2001-09-28' - interval '1'hour | DATE : 2001-09-28 | |
| TIME | TIME | time '12:00:00' - time '1:2:3.123456' | INTERVAL DAY TO SECOND +0 10:57:56.876544 |
|
| TIMESTAMP | TIMESTAMP | timestamp '2010-1-1 12:00:00' - timestamp '2011-11-12 1:2:3.123456' | INTERVAL DAY TO SECOND -679 13:02:03.123456 |
|
| TIMESTAMP | INTERVAL YEAR TO MONTH | timestamp '2001-09-28 01:00' - interval '23'year | TIMESTAMP: 1978-09-28 01:00:00 |
|
| TIMESTAMP | INTERVAL DAY TO SECOND | timestamp '2001-09-28 01:00' - interval '23'hour | TIMESTAMP: 2001-09-27 02:00:00 |
|
| TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | timestamp with time zone '2010-1-1 12:00:00 +2:00' - timestamp with time zone '2011-11-12 1:2:3.123456 -3:00' | INTERVAL DAY TO SECOND -679 18:02:03.123456 |
|
| TIMESTAMP WITH TIME ZONE | INTERVAL YEAR TO MONTH | timestamp withtime zone '2001-09-28 01:00 +2:00' - interval '23'year | TIMESTAMP WITH TIME ZONE: 1978-09-28 07:00:00+08:00 |
|
| TIMESTAMP WITH TIME ZONE | INTERVAL DAY TO SECOND | timestamp with time zone '2001-09-28 01:00 +2:00' - interval '23'hour | TIMESTAMP WITH TIME ZONE: 2001-09-27 08:00:00+08:00 |
|
| INTERVAL YEAR TO MONTH | INTERVAL YEAR TO MONTH | interval '1-11' year to month - interval '1'month | INTERVAL YEAR TO MONTH: +1-10 | |
| INTERVAL DAY TO SECOND | INTERVAL DAY TO SECOND | interval '1' day - interval '1 2:3:4' day to second | INTERVAL DAY TO SECOND: -0 02:03:04 | |
| INTERVAL YEAR TO MONTH | FLOAT | interval '1-5'year to month * 1.89::float | INTERVAL YEAR TO MONTH: +2-08 | |
| INTERVAL DAY TO SECOND | FLOAT | interval '1 2:3:4.123456' day to second * 1.89::float | INTERVAL DAY TO SECOND: +2 01:14:11.993331 |
|
| FLOAT | INTERVAL YEAR TO MONTH | 1.89::float *interval '1-5'year to month | INTERVAL YEAR TO MONTH: +2-08 | |
| FLOAT | INTERVAL DAY TO SECOND | 1.89::float *interval '1 2:3:4.123456' day to second | INTERVAL DAY TO SECOND: +2 01:14:11.993331 |
|
| / | INTERVAL YEAR TO MONTH | FLOAT | interval '1-5'year to month / 1.89::float | INTERVAL YEAR TO MONTH: +0-08 |
| INTERVAL DAY TO SECOND | FLOAT | interval '1 2:3:4.123456' day to second / 1.89::float | INTERVAL DAY TO SECOND: +0 13:47:01.229341 |
|
| OVERLAPS | (DATE, DATE) | (DATE, DATE) | (date '1993-11-12', date '2001-11-12') OVERLAPS (date '1983-11-13', date '1995-11-12') | t |
| (DATE, INTERVAL YEAR TO MONTH) | (DATE, DATE) | (date '1993-11-12', interval '1-1'year to month) OVERLAPS (date '1983-11-13', date '1995-11-12') | t | |
| (DATE, DATE) | (DATE, INTERVAL YEAR TO MONTH) | (date '1993-11-12', date '2001-11-12') OVERLAPS (date '1983-11-13', interval '1-1'year to month) | f | |
| (DATE, INTERVAL YEAR TO MONTH) | (DATE, INTERVAL YEAR TO MONTH) | (date '1993-11-12', interval '1'year) OVERLAPS (date '1983-11-13', interval '1-1'year to month) | f | |
| (DATE, INTERVAL DAY TO SECOND) | (DATE, DATE) | (date '1993-11-12', interval '11'day) OVERLAPS (date '1983-11-13', date '1995-11-12') | t | |
| (DATE, DATE) | (DATE, INTERVAL DAY TO SECOND) | (date '1993-11-12', date '2001-11-12') OVERLAPS (date '1983-11-13', interval '11'day to hour) | f | |
| (DATE, INTERVAL DAY TO SECOND) | (DATE, INTERVAL DAY TO SECOND) | (date '1993-11-12', interval '10'day) OVERLAPS (date '1993-11-15', interval '-10' day) | t | |
| (TIME,TIME) | (TIME,TIME) | (time '2:00:00', time '3:00:00') OVERLAPS (time '4:00:00', time '6:00:00') | f | |
| (TIME WITH TIME ZONE) | (TIME WITH TIME ZONE) | (time with time zone '2:00:00 +2:00', time with time zone '3:00:00 +2:00') OVERLAPS (time with time zone '4:00:00 +5:00', time with time zone '6:00:00 +5:00') | t | |
| (TIMESTAMP, TIMESTAMP) | (TIMESTAMP, TIMESTAMP) | (timestamp '1993-11-12 0:0:0', timestamp '1993-11-12 2:0:0') OVERLAPS (timestamp '1993-11-12 3:0:0', timestamp '1993-11-12 4:0:0') | f | |
| (TIMESTAMP, INTERVAL YEAR TO MONTH) | (TIMESTAMP, TIMESTAMP) | (timestamp '1993-11-12 0:0:0', interval '1-1'year to month) OVERLAPS (timestamp '1993-11-12 3:0:0', timestamp '2003-11-12 4:0:0') | t | |
| (TIMESTAMP, TIMESTAMP) | (TIMESTAMP, INTERVAL YEAR TO MONTH) | (timestamp '1993-11-12 0:0:0', timestamp '1993-11-12 2:0:0') OVERLAPS (timestamp '1993-11-12 3:0:0', interval '1-1'year to month) | f | |
| (TIMESTAMP, INTERVAL YEAR TO MONTH) | (TIMESTAMP, INTERVAL YEAR TO MONTH) | (timestamp '1993-11-12 0:0:0', interval '1-1'year to month) OVERLAPS (timestamp '1993-11-12 3:0:0', interval '1-1'year to month) | t | |
| (TIMESTAMP, INTERVAL DAY TO SECOND) | (TIMESTAMP, TIMESTAMP) | (timestamp '1993-11-12 0:0:0', interval '11' day) OVERLAPS (timestamp '1993-11-12 3:0:0', timestamp '2003-11-12 4:0:0') | t | |
| (TIMESTAMP, TIMESTAMP) | (TIMESTAMP, INTERVAL DAY TO SECOND) | (timestamp '1993-11-12 0:0:0', timestamp '1993-11-12 2:0:0') OVERLAPS (timestamp '1993-11-12 3:0:0', interval '11'day) | f | |
| (TIMESTAMP, INTERVAL DAY TO SECOND) | (TIMESTAMP, INTERVAL DAY TO SECOND) | (timestamp '1993-11-12 0:0:0', interval '11'day) OVERLAPS (timestamp '1993-11-12 3:0:0', interval '11'day) | t | |
| (TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) | (TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) | (timestamp with time zone '1993-11-12 0:0:0 +1:00', timestamp with time zone '1993-11-12 2:0:0 +1:00') OVERLAPS (timestamp with time zone '1993-11-12 3:0:0 +3:00', timestamp with time zone '1993-11-12 4:0:0 +2:00'); | t | |
| (TIMESTAMP WITH TIME ZONE, INTERVAL YEAR TO MONTH) | (TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) | (timestamp with time zone '1993-11-12 0:0:0 +1:00', interval '1-1'year to month) OVERLAPS (timestamp with time zone '1993-11-12 3:0:0 +3:00', timestamp with time zone '1993-11-12 4:0:0 +2:00'); | t | |
| (TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) | (TIMESTAMP WITH TIME ZONE, INTERVAL YEAR TO MONTH) | (timestamp with time zone '1993-11-12 0:0:0 +1:00', timestamp with time zone '1993-11-12 4:0:0 +2:00') OVERLAPS (timestamp with time zone '1993-11-12 3:0:0 +3:00', interval '1-1'year to month); | t | |
| (TIMESTAMP WITH TIME ZONE, INTERVAL YEAR TO MONTH) | (TIMESTAMP WITH TIME ZONE, INTERVAL YEAR TO MONTH) | (timestamp with time zone '1993-11-12 0:0:0 +1:00', interval '1-1'year to month) OVERLAPS (timestamp with time zone '1993-11-12 3:0:0 +3:00', interval '1-1'year to month); | t | |
| (TIMESTAMP WITH TIME ZONE, INTERVAL DAY TO SECOND) | (TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) | (timestamp with time zone '1993-11-12 0:0:0 +1:00', interval '1'hour) OVERLAPS (timestamp with time zone '1993-11-12 3:0:0 +3:00', timestamp with time zone '1993-11-12 4:0:0 +2:00'); | f | |
| (TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) | (TIMESTAMP WITH TIME ZONE, INTERVAL DAY TO SECOND) | (timestamp with time zone '1993-11-12 0:0:0 +1:00', timestamp with time zone '1993-11-12 4:0:0 +2:00') OVERLAPS (timestamp with time zone '1993-11-12 3:0:0 +3:00', interval '1'hour); | t | |
| (TIMESTAMP WITH TIME ZONE, INTERVAL DAY TO SECOND) | (TIMESTAMP WITH TIME ZONE, INTERVAL DAY TO SECOND) | (timestamp with time zone '1993-11-12 0:0:0 +1:00', interval '1' hour) OVERLAPS (timestamp with time zone '1993-11-12 3:0:0 +3:00', interval '1' day); | f |
操作符OVERLAPS是SQL92标准规定的操作符，它可接收的操作数类型组合已全部列于上表中。操作数表示一个时间段，该时间段的起点为第一个时间，终点为第二个时间。如果操作数中存在INTERVAL YEAR TO MONTH或者INTERVAL DAY TO SECOND 类型，则该时间段的起点仍然是第一个时间，终点为第一个时间加上其后的INTERVAL YEAR TO MONTH或者INTERVAL DAY TO SECOND之后的时间。OVARLAPS返回一个布尔值，表示左操作数表示的时间段和右操作数表示的时间段是否有重叠的部分。