You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Eric Sun (Jira)" <ji...@apache.org> on 2020/05/18 20:12:00 UTC
[jira] [Comment Edited] (SPARK-16423) Inconsistent settings on the
first day of a week
[ https://issues.apache.org/jira/browse/SPARK-16423?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17110577#comment-17110577 ]
Eric Sun edited comment on SPARK-16423 at 5/18/20, 8:11 PM:
------------------------------------------------------------
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 is *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)?
was (Author: ericsun2):
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-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org