Question 340896 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Need a variable to refer back to Friday of last week

In PostGresql, I’m looking for something that simply gets the date of Friday of the previous week, no matter what day I run the program on.

I’ve tried using the DATE_TRUNC() function:

DATE_TRUNC('DAY',NOW()) - INTERVAL '5 DAY' as LAST_CRED_DATE_CYCLE

But with this I have to change the INTERVAL depending on what day I run it. I’ve tried other forms of the DATE_TRUNC and other functions but I just can’t get there. Is there a way to get last Friday’s date no matter what day I run this code?

Thanks.

Tags:

2

Answers


  1. 0

    You can extract last sunday and then remove 2 days to get last friday

    select current_date - extract(dow from current_date)::integer -2 as LAST_CRED_DATE_CYCLE
    last_cred_date_cycle
    2024-02-09 
    SELECT 1

    SELECT DATE_TRUNC('DAY',NOW()) - INTERVAL '1 DAY'
  * ((extract(dow from current_date)::integer ) + 2 ) as LAST_CRED_DATE_CYCLE
    last_cred_date_cycle
    2024-02-09 00:00:00+00 
    SELECT 1

    fiddle

    Login or Signup to reply.
  2. 0

    Date_Trunc() is the function you are loooking for, you just have the wrong field. `Date_Trunc(‘week’, <target_date>) always returns Monday of the week containing the targer_date. From that subtracting 3 (for date) of ‘interval 3 days) will the give the prior Friday. Try

    select date_trunc('week', current_date)::date - 3;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search