Not able to convert week of year to a date

0

I have a requirement for weekly stats and I am getting data week-wise but with only a week-of-year instead of a date.

Timestream table:

userdurationtime
wayne1222024-01-01 10:30:45
steve422024-01-26 15:36:12
jack212024-01-31 07:21:31
shannon072024-02-01 10:52:16
harvey702024-02-06 22:42:01
harvey502024-02-07 04:18:34

Required output:

week_start_dateduration_sum
2024-01-01122
2024-01-2242
2024-01-2928
2024-02-05120

Current query and output:

SELECT WEEK(time) AS week_start_date, SUM(duration) as duration_sum  FROM "dbn"."table" GROUP BY 1 ORDER BY 1
week_start_dateduration_sum
1122
442
528
6120

Need help in converting that week to a date

NiteshR
질문됨 2달 전127회 조회
2개 답변
1

Here's a revised version of your query with improved formatting:

SELECT 
    DATE_ADD(DATE_FORMAT(time, '%Y-%m-%d'), INTERVAL (WEEK(time) - WEEK(DATE_FORMAT(time, '%Y-01-01'))) WEEK) AS week_start_date, 
    SUM(duration) as duration_sum  
FROM 
    "dbn"."table" 
GROUP BY 
    week_start_date
ORDER BY 
    week_start_date;

The output should look like this:

week_start_dateduration_sum
2023-12-2550
2024-01-01122
2024-01-2242
2024-01-2928
2024-02-05120
profile picture
전문가
답변함 2달 전
0

Hi Osvaldo, thanks for the reply. Did you get a chance to try the query in timestream? I got this error:

The query syntax is invalid at line 2:105

This error occurred when using WEEK as an interval

INTERVAL (WEEK(time) - WEEK(DATE_FORMAT(time, '%Y-01-01'))) WEEK

I did try to use this operator before as it is mentioned in the documentation as well but got the same error.

NiteshR
답변함 2달 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠