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

Postgresql – Retrieving previous thursday and previous wednesday

In PostgreSQL, I am attempting to fetch the previous Thursday and the previous Wednesday based on a thursday date (for example 2024-01-18). I have tried the following queries:

For the previous Thursday:

SELECT 
  date_trunc('week', '2024-01-18'::date) - INTERVAL '5 days' AS previous_wednesday, -- not working
  date_trunc('week', '2024-01-18'::date) - INTERVAL '4 days' AS previous_thursday;

db fidle: https://www.db-fiddle.com/f/9GFNcJ81QfspGezYxhMib1/0

Which gives me

previous_wednesday previous_thursday
2024-01-10T00:00:00.000Z 2024-01-11T00:00:00.000Z

Expected result :

previous_wednesday previous_thursday
2024-01-17T00:00:00.000Z 2024-01-11T00:00:00.000Z

I would like assistance to find the desired result.

Tags:

2

Answers


  1. 0

    If the given date is always a thursday:

    SELECT 
      '2024-01-18'::date - INTERVAL '1 days' AS previous_wednesday,
      '2024-01-18'::date - INTERVAL '7 days' AS previous_thursday;

    No need for date_trunc, which would set the date to the start of the given week before adding/subtracting the interval.

    If the given date isn’t always a thursday, you may want to use date_trunc to start your calulcation always from monday of the given date.

        SELECT 
      date_trunc('week','2024-01-16'::date) + INTERVAL '2 days' AS current_wednesday, --wednesday of the given week
      date_trunc('week','2024-01-17'::date) + INTERVAL '3 days' AS current_thursday, -- thursday of the given week
      date_trunc('week','2024-01-17'::date) - INTERVAL '4 days' AS previous_thursday; -- thursday of the previous week
    Login or Signup to reply.
  2. 0

    The extract() function can give you the numeric day of the week. You can use it in a case statement to move back accordingly, or combine with modulo % operator: demo

    SELECT 
  to_char(my_date,'YYYY-MM-DD, Day'),
  -((extract(dow from my_date)+3)%7+1)::int+my_date::date AS previous_wednesday,
  -((extract(dow from my_date)+2)%7+1)::int+my_date::date AS previous_thursday
FROM generate_series('2024-01-15'::date,'2024-01-21','1d')_(my_date);
    to_char previous_wednesday previous_thursday
    2024-01-15, Monday 2024-01-10 2024-01-11
    2024-01-16, Tuesday 2024-01-10 2024-01-11
    2024-01-17, Wednesday 2024-01-10 2024-01-11
    2024-01-18, Thursday 2024-01-17 2024-01-11
    2024-01-19, Friday 2024-01-17 2024-01-18
    2024-01-20, Saturday 2024-01-17 2024-01-18
    2024-01-21, Sunday 2024-01-17 2024-01-18
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search