[
https://issues.apache.org/jira/browse/SPARK-31597?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17094560#comment-17094560
]
Kent Yao commented on SPARK-31597:
----------------------------------
work log manually [https://github.com/apache/spark/pull/28396]
> extracting day from intervals should be interval.days + days in
> interval.microsecond
> ------------------------------------------------------------------------------------
>
> Key: SPARK-31597
> URL: https://issues.apache.org/jira/browse/SPARK-31597
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 3.0.0, 3.1.0
> Reporter: Kent Yao
> Priority: Major
>
> checked with both Presto and PostgresSQL, one is implemented intervals with
> ANSI style year-month/day-time, the other is mixed and Non-ANSI. They both
> add the exceeded days in interval time part to the total days of the
> operation which extracts day from interval values
>
> ```sql
> presto> SELECT EXTRACT(DAY FROM (cast('2020-01-15 00:00:00' as timestamp) -
> cast('2020-01-01 00:00:00' as timestamp)));
> _col0
> -------
> 14
> (1 row)
> Query 20200428_135239_00000_ahn7x, FINISHED, 1 node
> Splits: 17 total, 17 done (100.00%)
> 0:01 [0 rows, 0B] [0 rows/s, 0B/s]
> presto> SELECT EXTRACT(DAY FROM (cast('2020-01-15 00:00:00' as timestamp) -
> cast('2020-01-01 00:00:01' as timestamp)));
> _col0
> -------
> 13
> (1 row)
> Query 20200428_135246_00001_ahn7x, FINISHED, 1 node
> Splits: 17 total, 17 done (100.00%)
> 0:00 [0 rows, 0B] [0 rows/s, 0B/s]
> presto>
> ```
> ```scala
> postgres=# SELECT EXTRACT(DAY FROM (cast('2020-01-15 00:00:00' as timestamp)
> - cast('2020-01-01 00:00:00' as timestamp)));
> date_part
> -----------
> 14
> (1 row)
> postgres=# SELECT EXTRACT(DAY FROM (cast('2020-01-15 00:00:00' as timestamp)
> - cast('2020-01-01 00:00:01' as timestamp)));
> date_part
> -----------
> 13
> ```
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
