in the Spark SQL example, `year("1912")` means, first cast "1912" to date
type, and then call the "year" function.
in the Postgres example, `date_part('year',TIMESTAMP '2017')` means, get a
timestamp literal, and call the "date_part" function.
Can you try date literal in Postgres?
On Mon, Feb 18, 2019 at 11:21 AM Xiao Li <gatorsm...@gmail.com> wrote:
> It is hard to say this is a bug. In the existing Spark applications, the
> current behavior might be already considered as a feature instead of a bug.
> I am thinking if we should introduce a strict mode to throw an exception
> for these type casting, like what Postgres behaves.
>
> Darcy Shen <sad...@zoho.com> 于2019年2月17日周日 下午6:22写道：
>
>> For PostgreSQL:
>>
>> postgres=# SELECT date_part('year',TIMESTAMP '2017-01-01');
>> date_part
>> -----------
>> 2017
>> (1 row)
>>
>> postgres=# SELECT date_part('year',TIMESTAMP '2017');
>> ERROR: invalid input syntax for type timestamp: "2017"
>> LINE 1: SELECT date_part('year',TIMESTAMP '2017');
>> ^
>> postgres=# SELECT date_part('month',TIMESTAMP '2017-01-01');
>> date_part
>> -----------
>> 1
>> (1 row)
>>
>> postgres=# SELECT date_part('year',TIMESTAMP '2017-1-1');
>> date_part
>> -----------
>> 2017
>> (1 row)
>>
>>
>> We'd better follow the Hive semantics. And removing support for yyyy and
>> yyyy-d[d] will simplify the routine.
>>
>> I'll create a Pull Request later.
>>
>>
>> ---- On Sat, 16 Feb 2019 00:51:43 +0800 *Xiao Li <gatorsm...@gmail.com
>> <gatorsm...@gmail.com>>* wrote ----
>>
>> We normally do not follow MySQL. Check the commercial database [like
>> Oracle]? or the open source PostgreSQL?
>>
>> Sean Owen <sro...@gmail.com> 于2019年2月15日周五 上午5:34写道：
>>
>> year("1912") == 1912 makes sense; month("1912") == 1 is odd but not
>>> wrong. On the one hand, some answer might be better than none. But
>>> then, we are trying to match Hive semantics where the SQL standard is
>>> silent. Is this actually defined behavior in a SQL standard, or, what
>>> does MySQL do?
>>>
>>> On Fri, Feb 15, 2019 at 2:07 AM Darcy Shen <sad...@zoho.com.invalid>
>>> wrote:
>>> >
>>> > See https://issues.apache.org/jira/browse/SPARK-26885 and
>>> https://github.com/apache/spark/blob/71170e74df5c7ec657f61154212d1dc2ba7d0613/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala
>>> >
>>> >
>>> >
>>> >
>>> > stringToTimestamp, stringToDate support yyyy, as a result:
>>> >
>>> > select year("1912") => 1912
>>> >
>>> > select month("1912") => 1
>>> >
>>> > select hour("1912") => 0
>>> >
>>> >
>>> >
>>> > In Presto or Hive,
>>> >
>>> > select year("1912") => null
>>> >
>>> > select month("1912") => null
>>> >
>>> > select hour("1912") => null
>>> >
>>> >
>>> >
>>> > It is not a good idea to support yyyy for a Date/DateTime. As well as
>>> yyyy-[d]d.
>>> >
>>> >
>>> > What's your opinion?
>>> >
>>> >
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe e-mail: dev-unsubscr...@spark.apache.org
>>>
>>>
>>
>>
