How to Get Week Start and End Date String in Postgresql

How to get week start and end date string in PostgreSQL?

You can use date_trunc('week', ...).

For example:

SELECT date_trunc('week', '2012-07-25 22:24:22'::timestamp);
-> 2012-07-23 00:00:00

Then, you can convert this into a date, if you're not interested in a start time.

To get the end date too:

SELECT    date_trunc('week', '2012-07-25 22:24:22'::timestamp)::date
   || ' '
   || (date_trunc('week', '2012-07-25 22:24:22'::timestamp)+ '6 days'::interval)::date;

-> 2012-07-23 2012-07-29

(I've used the default formatting here, you can of course adapt this to use MM/DD/YYYY.)

Note that, if you want to make comparisons on timestamps, instead of using (date_trunc('week', ...) + '6 days'::interval, you might want to add an entire week and use a strict comparison for the end of the week.

This will exclude y timestamps on the last day of the week (since the cut-off time is midnight on the day).

    date_trunc('week', x)::date <= y::timestamp
AND y::timestamp <= (date_trunc('week', x) + '6 days'::interval)::date

This will include them:

    date_trunc('week', x)::date <= y::timestamp
AND y::timestamp < (date_trunc('week', x) + '1 week'::interval)

(That's in the rare cases when you can't use date_trunc on y directly.)

---

If your week starts on a Sunday, replacing date_trunc('week', x)::date with date_trunc('week', x + '1 day'::interval)::date - '1 day'::interval should work.

How to get week period (Week Start to Weekend) in PostgreSQL?

If you just need to format the date, to_char is what you need (https://www.postgresql.org/docs/current/static/functions-formatting.html):

SELECT to_char(your_date, 'Mon FMDD')     --> Oct 1    
SELECT to_char(your_date, 'FMDDth Mon')   --> 1st Oct

demo: db<>fiddle

Mon gives the three lettered month

DD gives the day

FM prefix removes leading zeros

th suppif adds the "st", "nd", "rd", "th" ending

First and Last day of the previous week with Postgresql

From what I'm reading, you're looking for the first and last stored day of the week, so:

WITH
  first_stored AS (SELECT min(stored_date) as first FROM stored WHERE stored_date > DATE_TRUNC('WEEK', NOW()) - INTERVAL '8 DAY'),
  last_stored AS (SELECT max(stored_date) as last FROM stored WHERE stored_date < DATE_TRUNC('WEEK', NOW()) - INTERVAL '1 DAY')
SELECT first_stored.first, last_stored.last FROM first_stored, last_stored;

This yields:

   first    |    last    
------------+------------
 2019-10-15 | 2019-10-18  -- a Tuesday and a Thursday
(1 row)

UPDATE:

Assuming "today" is 2019-10-26 (the timestamp of your latest edit to the question), here's a version that fits with the example you shared:

postgres=# create table my_table (id int, stored_date date);
CREATE TABLE
postgres=# insert into my_table values (1,'2019-10-07'),(2,'2019-10-08'),(3,'2019-10-09'),(4,'2019-10-10'),(5,'2019-10-11'),(6,'2019-10-15'),(7,'2019-10-16'),(8,'2019-10-18'),(9,'2019-10-21'),(10,'2019-10-22');
INSERT 0 10
postgres=# WITH
  first_stored AS (SELECT min(stored_date) as first FROM my_table WHERE stored_date > DATE_TRUNC('WEEK', '2019-10-26'::date) - INTERVAL '8 DAY'),
  last_stored AS (SELECT max(stored_date) as last FROM my_table WHERE stored_date < DATE_TRUNC('WEEK', '2019-10-26'::date) - INTERVAL '1 DAY')
SELECT id, stored_date FROM my_table mt, first_stored fs, last_stored ls WHERE mt.stored_date in (fs.first,ls.last) ORDER BY stored_date;
 id | stored_date 
----+-------------
  6 | 2019-10-15
  8 | 2019-10-18
(2 rows)

Disclosure: I work for EnterpriseDB (EDB)

How to change start of the week in PostgreSQL

After some research and thinking I used this code to get the week number like I needed

ceil(((cte.data::date -((CAST(EXTRACT (year FROM  cte.data) as
text)||'-01-04')::date - CAST
               (EXTRACT (isodow FROM  (CAST(EXTRACT (year FROM  cte.data) as
text)||'-01-04')::date)
               as integer)
               ))+1)/7.0)

and then I gave an alias of 'num' and calculated the first and last date this way:

'W' || num ||
            to_char(to_date('20200101','YYYYMMDD')+  (num*7)-10,' DD/MM - ')  ||
            to_char(to_date('20200101','YYYYMMDD')+  (num*7)-4, 'DD/MM') as WEEK

The final output was the week number with start and end date of the week using Sunday as the first day in the week

How to get week start date from week number in postgresql?

To get the start date, simply convert the week number to a date using to_date()

If you are using ISO week numbers use:

select to_date('201643', 'iyyyiw');

Otherwise use:

select to_date('201643', 'yyyyww');

To get the end date, just add 7 to resulting date: to_date('201643', 'iyyyiw') + 7

Generate series of weeks with their week number and year

Your whole issue stems from selecting the incorrect start date. You stated that monday as first day of week,however you start you calendar on 2020-01-01. That is NOT Monday, it is actually Wednesday. Thus your weeks run from Wednesday through Tuesday. That also gives raise to your week 9 issue as both 2020-02-24 and 2020-03-01 are both in ISO week 9. You correct by changing the start date from 2020-01-01 to 2019-12-30 (or programmatically as date_trunc('week',date '2020-01-01'). Also your row_number can be reduces to row_number() over(). So:

with weeks as (
    select generate_series(date_trunc('week',date '2020-01-01') , current_date, '1 week') as week_starting_date
)
select row_number() over () as id
     ,  extract(week from weeks.week_starting_date)         as week_number    -- ISO Week number 
     ,  extract(year from weeks.week_starting_date)         as week_year      -- ISO Year
     ,  weeks.week_starting_date::date                      as week_start_date
     ,  (weeks.week_starting_date + interval '6 day')::date as week_end_date
  from weeks;

See demo here. You may want to look at rows 1, and 53-54. The values for these columns week_number and week-year are correct via ISO 8601 date specification. If these do not work for you then you will likely need to build a user defined calendar table.

Generate custom start and end date series in postgresql and sum qty for a given billing cycle

The calendar is messy with months of varying days between the and even within the same month (Feb). Fortunately Postgres is aware of the irregularities (almost always). So work with it, let Postgres take care of them. With intervals of 1 month and 1 day can you make the irregularity of Feb and the 30/31 days per months simply disappear.

The first start_date is the subscription_date and its end date is the end of the start_date month or the end of following month. From there every thing follows a specific pattern: start_date is prior end_date + 1 day, end_date is the last day of that month. The issue in determining the last day of the month. But this is simply done: Truncate the Start_Date to the Month add 1 Month and subtract 1 day. Note this totally ignores the actual month and the number of days within it. You then wrap all that in a recursive CTE which steps from from 1 row to the next (iteratively). As follows:

with recursive billing( account_id, subscription_start, start_date, end_date) as  
     ( select s.account_id 
            , s.subscription_start
            , s.subscription_start::date
            , case when extract(day from s.subscription_start) > 28 
                   then (date_trunc('month', s.subscription_start+interval '2 month' ) - interval '1 day')::date
                   else (date_trunc('month', s.subscription_start+interval '1 month' ) - interval '1 day')::date
              end
         from subscriptions s
       union all
        select b.account_id 
             , b.subscription_start
             , (b.end_date + interval '1 day')::date
             , ((b.end_date + interval '1 day')::date + interval '1 month' - interval '1 day')::date
         from billing b
        where date_trunc('month',end_date) <= date_trunc('month',now())
     ) 
select * from billing 
order by account_id desc, start_date;

See demo here. Check difference between Feb 2020 and Feb 2021.

How can I get proper week number in the start of the year

From the docs:

The number of the week of the year that the day is in. By definition
(ISO 8601), the first week of a year contains January 4 of that year.
(The ISO-8601 week starts on Monday.) In other words, the first
Thursday of a year is in week 1 of that year.

Because of this, it is possible for early January dates to be part of
the 52nd or 53rd week of the previous year. For example, 2005-01-01 is
part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd
week of year 2005.

So, it's quite normal, that January 1st does not always lies in the first week. So, 53 is correct here because it means the 53rd week of the previous year.

---

Unfortunately there is nothing like isoweek for date_part() like there is isoyear for year. week is always the ISO week. But you can use this workaround:

demo:db<>fiddle

For converting a date into a string using a certain pattern, you can use ww and iw - week and ISO week:

SELECT to_char('2021-01-02'::date, 'iyyy-iw');

returns 2020-53, the ISO values. Whereas

SELECT to_char('2021-01-02'::date, 'yyyy-ww');

returns 2021-01, which is what you are expecting.

So you can use to_char to get your week number and than cast it into an int:

SELECT to_char('2021-01-02'::date, 'ww')::int;

Extract day of week from date field in PostgreSQL assuming weeks start on Monday

From the manual

isodow

    The day of the week as Monday (1) to Sunday (7)

So, you just need to subtract 1 from that result:

psql (9.6.1)
Type "help" for help.

postgres=> select extract(isodow from date '2016-12-12') - 1;
  ?column?
-----------
         0
(1 row)
postgres=>

---

Related Topics