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 >>> >>> >> >>