[
https://issues.apache.org/jira/browse/SPARK-16423?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17110577#comment-17110577
]
Eric Sun commented on SPARK-16423:
----------------------------------
Just to clarify the issue again:
{code:scala}
scala> spark.sql("SELECT dayofweek('2020-05-18')").show()
+-----------------------------------+
|dayofweek(CAST(2020-05-18 AS DATE))|
+-----------------------------------+
| 2|
+-----------------------------------+
scala> spark.sql("SELECT date_trunc('week', from_unixtime(1589829639,
'yyyy-MM-dd'))").show()
+------------------------------------------------------------------------------------------+
|date_trunc(week, CAST(from_unixtime(CAST(1589829639 AS BIGINT), yyyy-MM-dd) AS
TIMESTAMP))|
+------------------------------------------------------------------------------------------+
|
2020-05-18 00:00:00|
+------------------------------------------------------------------------------------------+
{code}
* 2020-05-18 is Monday: the dayofweek() => 2, therefore the 1st day of this
week is Sunday, right?
* date_trunc('week', '2020-05-18') => Monday, the 1st of of this week if
Monday, see a tiny discrepancy?
{code:sql}
-- MySql behavior
SELECT ds, DAYOFWEEK(ds), DATE_FORMAT(ds, '%YW%U'), DATE_FORMAT(ds, '%YW%u')
FROM (SELECT FROM_UNIXTIME(1589829639) as ds) x;
ds |DAYOFWEEK(ds)|DATE_FORMAT(ds, '%YW%U')|DATE_FORMAT(ds,
'%YW%u')|DATE_FORMAT(ds, '%YW%V')|
-------------------|-------------|------------------------|------------------------|------------------------|
2020-05-18 19:20:39| 2|2020W20 |2020W21
|2020W20 |
{code}
The request for this JIRA is: should we allow date_trunc() and dayofweek()
support different 1st day of week option (SUNDAY or MONDAY)?
> Inconsistent settings on the first day of a week
> ------------------------------------------------
>
> Key: SPARK-16423
> URL: https://issues.apache.org/jira/browse/SPARK-16423
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Reporter: Yin Huai
> Priority: Major
> Labels: bulk-closed
>
> For the function {{WeekOfYear}}, we explicitly set the first day of the week
> to {{Calendar.MONDAY}}. However, {{FromUnixTime}} does not explicitly set it.
> So, we are using the default first day of the week based on the locale
> setting (see
> https://docs.oracle.com/javase/8/docs/api/java/util/Calendar.html#setFirstDayOfWeek-int-).
>
> Let's do a survey on what other databases do and make the setting consistent.
--
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
