taiyang-li commented on issue #8446:
URL:
https://github.com/apache/incubator-gluten/issues/8446#issuecomment-2742227972
I tried hive, presto, and spark, `select date_format('2024-12-29 20:55:30',
'YYYY-MM-dd HH:00:00')` returns `2025-12-29 20:00:00`. Its underlying formatter
is `SimpleDateFormat`
```java
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;
import java.util.Locale;
public class SimpleFormatDemo {
public static void main(String[] args) throws Exception {
SimpleDateFormat sdp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
sdp.setTimeZone(TimeZone.getTimeZone("GMT+8")); // 东八区
{
SimpleDateFormat sdf1 = new SimpleDateFormat("YYYY-MM-dd HH:00:00",
Locale.US);
sdf1.setTimeZone(TimeZone.getTimeZone("GMT+8")); // 东八区
// 解析输入时间（2024-12-29 20:55:30）
Date date = sdp.parse("2024-12-28 20:55:30");
// 格式化输出（会得到 2025-12-29 20:00:00）
System.out.println(sdf1.format(date));
}
{
SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd HH:00:00",
Locale.CHINA);
sdf.setTimeZone(TimeZone.getTimeZone("GMT+8")); // 东八区
// 解析输入时间（2024-12-29 20:55:30）
Date date = sdp.parse("2024-12-28 20:55:30");
// 格式化输出（会得到 2025-12-29 20:00:00）
System.out.println(sdf.format(date));
}
}
}
```
Above demo outputs.
```
2025-12-29 20:00:00
2025-12-29 20:00:00
```
We can conclude that the week-based year is always 2025 for "2024-12-29"
whether with `Local.US` or `Local.CHINA`. It takes Sunday as the first day of
one week, thus "2024-12-29" is in the first week of year 2025. It is definitely
not following the rule of ISO week date standard (ISO-8601), which takes Monday
as the first day of one week.
Besides, if we refer to the documentations of spark, we will find
`date_format` doesn't support 'Y' in format:
https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-datetime-pattern
In a word, we should not use `date_format` to get the week-based year from
date or datetime. Instead we should use `extract`:
``` sql
0: jdbc:hive2://localhost:10000/> select EXTRACT(YEAROFWEEK from '2024-12-29
20:55:30');
+-----------------------------------------------+
| extract(YEAROFWEEK FROM 2024-12-29 20:55:30) |
+-----------------------------------------------+
| 2024 |
+-----------------------------------------------+
1 row selected (0.176 seconds)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
