Github user HyukjinKwon commented on the issue:
https://github.com/apache/spark/pull/14788
I tested the functions related with `add`/`sub`/`trunc` on date/timestamp
and It seems generally option 2 or option 3.
**DB2 (`TRUNC`)**
- input: `TimestampType`, output: `TimestampType`
- input: `DateType`, output: `DateType`
*Note: DB2 has `TRUNC_TIMESTAMP` function too which always return
`TimestampType` as below:
```
db2 => SELECT TRUNC(DATE('1990-01-01'), 'DAY') FROM SYSIBM.SYSDUMMY1
1
----------
12/31/1989
1 record(s) selected.
db2 => SELECT TRUNC(TIMESTAMP('1990-01-01'), 'DAY') FROM SYSIBM.SYSDUMMY1
1
--------------------------
1989-12-31-00.00.00.000000
1 record(s) selected.
```
```
db2 => SELECT TRUNC_TIMESTAMP(DATE('1990-01-01'), 'DAY') FROM
SYSIBM.SYSDUMMY1
1
-------------------
1989-12-31-00.00.00
1 record(s) selected.
```
**Oracle (`TRUNC`)**
- input: `TimestampType/DateType`, output: `DateType`
```
> SELECT TRUNC(TO_DATE('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS'), 'HH')
FROM DUAL;
TRUNC(TO_DATE('1999-12-0111:00:00','YYYY-MM-DDHH:MI:SS'),'HH')
--------------------------------------------------------------
01-DEC-99
> SELECT TRUNC(TO_TIMESTAMP('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS'),
'HH') FROM DUAL;
TRUNC(TO_TIMESTAMP('1999-12-0111:00:00','YYYY-MM-DDHH:MI:SS'),'HH')
-------------------------------------------------------------------
01-DEC-99
```
**Postgres (`TRUNC`)**
- input: `TimestampType/DateType`, output: `TimestampType`
- input: `CalanderIntervalType`, output `CalanderIntervalType`
```
postgres=# SELECT DATE_TRUNC('day', CAST('2015-10-10' AS DATE));
date_trunc
------------------------
2015-10-10 00:00:00+00
(1 row)
postgres=# SELECT DATE_TRUNC('day', CAST('2015-10-10' AS TIMESTAMP));
date_trunc
---------------------
2015-10-10 00:00:00
(1 row)
postgres=# select DATE_TRUNC('hour', interval '2 days 3 hours 40 minutes');
date_trunc
-----------------
2 days 03:00:00
(1 row)
```
**MySQL (`DATE_SUB`)**
- input: `TimestampType`, output: `TimestampType`
- input: `DateType`, output: `DateType`
*Note: When the `unit` is less than `DAY` in `CalanderIntervalType`, it
seems converting this into `TimestampType`.
```
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 1997-12-02 |
+-----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('1998-01-02 00:00:00', INTERVAL 31 DAY);
+--------------------------------------------------+
| DATE_SUB('1998-01-02 00:00:00', INTERVAL 31 DAY) |
+--------------------------------------------------+
| 1997-12-02 00:00:00 |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 0 second);
+-------------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 0 second) |
+-------------------------------------------+
| 1998-01-02 00:00:00 |
+-------------------------------------------+
1 row in set (0.00 sec)
```
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
