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

Postgresql – Get specific date, from specific day last week

I’m planning to get date from specific day from last week in node.js

For example if today is Friday or any day, I want to know what date is Sunday from the last week

I’m thinking to have this solution in using JS library or PostgreSQL.

My goal were to be able to query Sunday last week to 13 weeks before that.

Tags:

5

Answers


  1. Chosen as BEST ANSWER
    0

    After research and trying I find this way will be the best way, by using moment.js:

    const moment = require('moment')

// by use moment subtract 1 week and with endOf means we want to take
// the last day date from the last week which is Sunday in this case

let expectedDate = moment().subtract(1, 'week').endOf().format('yyyy-mm-dd')

console.log(expectedDate)

  2. 0

    You can use this NPM Package for getting Dates

    Moment.js 2.29.4
    Parse, validate, manipulate,
    and display dates and times in JavaScript

    https://momentjs.com/

    Momentjs is most popular and powerful

    // Last week
console.log(moment().subtract(7, 'days').calendar());
console.log(moment().subtract(1, 'week').endOf().format('yyyy-MM-dddd')); // 2022-08-Sunday
console.log(moment().subtract(1, 'week').endOf().format('yyyy-MM-DD')); // 2022-08-Sunday
// formats
console.log(moment.locale());         // en
console.log(moment().format('LT'));   // 3:10 PM
console.log(moment().format('LTS'));  // 3:10:58 PM
console.log(moment().format('L'));    // 08/20/2022
console.log(moment().format('l'));    // 8/20/2022
console.log(moment().format('LL'));   // August 20, 2022
console.log(moment().format('ll'));   // Aug 20, 2022
console.log(moment().format('LLL'));  // August 20, 2022 3:10 PM
console.log(moment().format('lll'));  // Aug 20, 2022 3:10 PM
console.log(moment().format('LLLL')); // Saturday, August 20, 2022 3:10 PM
console.log(moment().format('llll')); // Sat, Aug 20, 2022 3:12 PM
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>  
<script src="https://momentjs.com/static/js/global.js"></script>
<script src="https://momentjs.com/static/js/core-home.js"></script>
    Login or Signup to reply.
  3. 0

    In advance: my PostgreSQL knowledge is very limited, so there might be better ways. Having said this, PostgreSQL provides a number of Date/Time Functions and Operators.

    One can extract the current day of week (ISO numbering) using EXTRACT(ISODOW FROM CURRENT_DATE), which would return 6 if executed on a saturday, and 7 if executed on a sunday. Therefore you simply need to subtract the value from 7 to get the number of days to the next sunday. Add this value to the current date and you’ll have the date of the upcoming sunday. From this you just need to subtract the desired interval, e. g.

    SELECT CAST(
         CURRENT_DATE + CAST(7 - EXTRACT(ISODOW FROM CURRENT_DATE) AS integer) -
         MAKE_INTERVAL(weeks => 2) 
       AS DATE)

    will return 2022-08-07 because it’s the sunday two weeks before the upcoming sunday at 2022-08-21.

    Login or Signup to reply.
  4. 0

    Here is the exact solution that you are looking for in vanilla JS.

    To find the Sunday of the previous week and "n" weeks before that:

    function getSunday(previousWeeks = 1) {
  return new Date(
    new Date().setDate(
      new Date().getDate() + (7 - new Date().getDay()) - 7 * previousWeeks
    )
  );
}


 // this function can be used as following -
 //assuming that sunday is last day of the week 
   
console.log(getSunday()); // get sunday of previous week 
console.log(getSunday(7));//get sunday before 7 weeks
console.log(getSunday(0)); //get sunday of this week
console.log(getSunday(-1)); // get sunday of upcoming week
    Login or Signup to reply.
  5. 0

    Postgres simple direct date manipulation routines: What you asking is accomplished by interval subtraction and the date_trunc() function. For your example of 2022-08-20 I assume you want 2022-08-07 (Sunday of the prior week). So

    select (date_trunc('week', (current_date - interval '1 week')) - interval '1 day')::date;

    How it works:

    1. current_date – interval ‘1 week’: Calculates the date 1 week prior.
    2. date_trunc(‘week’, (…): Calculates the Monday prior to result of #1
      above
    3. interval ‘1 day’: Calculates the Sunday prior to #2 above
    4. (…)::date: Casts the result of #3 above back to just a date. Needed since all interval calculations generate timestamps results.

    As far as getting start of week for several weeks back wrap the above in a SQL function with the number of desired weeks as a parameter and multiple the - '1 week' by that value. (See demo)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search