[SPARK-28437] Different format when casting interval type to string type

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Yuming Wang
• Created: 18/Jul/19 10:22
• Updated: 29/Mar/20 02:23

Description

Spark SQL:

spark-sql> select cast(INTERVAL '10' SECOND as string);
interval 10 seconds

PostgreSQL:

postgres=# select substr(version(), 0, 16), cast(INTERVAL '10' SECOND as text);
     substr      |   text
-----------------+----------
 PostgreSQL 11.3 | 00:00:10
(1 row)

Vertica:

dbadmin=> select version(), cast(INTERVAL '10' SECOND as varchar(255));
              version               | ?column?
------------------------------------+----------
 Vertica Analytic Database v9.1.1-0 | 10
(1 row)

Presto:

presto> select cast(INTERVAL '10' SECOND as varchar(255));
     _col0
----------------
 0 00:00:10.000
(1 row)

Oracle:

SQL> select cast(INTERVAL '10' SECOND as varchar(255)) from dual;

CAST(INTERVAL'10'SECONDASVARCHAR(255))
--------------------------------------------------------------------------------
INTERVAL'+00 00:00:10.000000'DAY TO SECOND

Issue Links

• is related to: SPARK-29783 Support SQL Standard output style for interval type