Assume 2022-10-01 and 2022-10-8 are Monday, and the original table looks like this:

I want to convert it to

Is there any simple ways to do this? Thanks!

I tried look up but seems not finding anything neat solutions

CodePudding user response：

You can use the date_trunc function. For example:

SELECT date_trunc('week', dt)
FROM (
    VALUES DATE '2022-12-01'
) t(dt)

output:

   _col0
------------
 2022-11-28
(1 row)

CodePudding user response：

You want to truncate the date. This will depend on your SQL dialect, but you tagged Presto so I looked it up in their documentation.

I think you want

    SELECT
        truncate_date(week, Date)   interval '1' day AS date,
        Value
      FROM
        table

(I've hedged a bit here and added a day, because I assumed it will truncate to the preceding Sunday. Do experiment with that!)