How to get week start and end date string in PostgreSQL?
You can use
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
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
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:
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;
first | last
2019-10-15 | 2019-10-18 -- a Tuesday and a Thursday
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);
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
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
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
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
If you are using ISO week numbers use:
select to_date('201643', 'iyyyiw');
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
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
, 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
from subscriptions s
, (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
date_part() like there is
isoyear for year.
week is always the ISO week. But you can use this workaround:
For converting a date into a string using a certain pattern, you can use
iw - week and ISO week:
SELECT to_char('2021-01-02'::date, 'iyyy-iw');
2020-53, the ISO values. Whereas
SELECT to_char('2021-01-02'::date, 'yyyy-ww');
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
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
The day of the week as Monday (1) to Sunday (7)
So, you just need to subtract 1 from that result:
Type "help" for help.
postgres=> select extract(isodow from date '2016-12-12') - 1;