Search code examples
postgresqlsql-date-functions

I need a query to create a week circle

I already write this query

    Select to_date(concat(to_char(kyc.kyc_verify_date,'YY-MM'),'-01'),'YY-MM-DD') AS "Month"
    Count (columnname)
    From kyc;

And the table result i given like this:

         Month                count

January 1, 2021, 12:00 AM     2324

February 1, 2021, 12:00 AM    23245

As you can see, the date always start from the 1st day of the month.

Now i need a query for a week, just like that query, it will start from Monday Can someone help me with this.

Thank you

Solution

  • Use the date_trunc function, it does exactly what you need (and it will also do the trick for months, so you can simplify your original example):

    SELECT date_trunc('week', current_timestamp);

       date_trunc       
════════════════════════
 2022-02-07 00:00:00+01
(1 row)