10

Postgres – Get ISO Year Start & ISO Year End

 3 years ago
source link: https://cwestblog.com/2020/01/30/postgres-get-iso-year-start-iso-year-end/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Today I had to get the ISO year start and the ISO year end for a given date. For instance, according to ISO8601 the week number for January 30, 2020 is 5:

SELECT extract(week FROM '2020-01-30'::DATE);

Year Limits By Date In Year

Let’s say that we want to determine the first date of the year and the last date of the year. The following gives us just that for today’s date:

SELECT
d.this_year - (extract(week FROM d.this_year) * 7 - 8 + extract(isodow FROM d.this_year))::INT AS year_start,
d.next_year - (extract(week FROM d.next_year) * 7 - 7 + extract(isodow FROM d.next_year))::INT AS year_end
FROM (
SELECT
(d.y || '-01-04')::DATE AS this_year,
(d.y + 1 || '-01-04')::DATE AS next_year
FROM (SELECT extract(isoyear FROM CURRENT_DATE)::INT AS y) d
) d;

Year Limits By Year Number

Now let’s say that we want to determine the first date of the year and the last date of the year from the number of the year. The following gives us just that for the year 2025:

SELECT
d.this_year - (extract(week FROM d.this_year) * 7 - 8 + extract(isodow FROM d.this_year))::INT AS year_start,
d.next_year - (extract(week FROM d.next_year) * 7 - 7 + extract(isodow FROM d.next_year))::INT AS year_end
FROM (
SELECT
(d.y || '-01-04')::DATE AS this_year,
(d.y + 1 || '-01-04')::DATE AS next_year
FROM (SELECT 2025 AS y) d
) d;

All Weeks In 2020

Here is a list of the 53 weeks for the year 2020:

WeekDatesOptions1Mon, Dec 30, 2019—Sun, Jan 5, 2020See SQL2Mon, Jan 6, 2020—Sun, Jan 12, 2020See SQL3Mon, Jan 13, 2020—Sun, Jan 19, 2020See SQL4Mon, Jan 20, 2020—Sun, Jan 26, 2020See SQL5Mon, Jan 27, 2020—Sun, Feb 2, 2020See SQL6Mon, Feb 3, 2020—Sun, Feb 9, 2020See SQL7Mon, Feb 10, 2020—Sun, Feb 16, 2020See SQL8Mon, Feb 17, 2020—Sun, Feb 23, 2020See SQL9Mon, Feb 24, 2020—Sun, Mar 1, 2020See SQL10Mon, Mar 2, 2020—Sun, Mar 8, 2020See SQL11Mon, Mar 9, 2020—Sun, Mar 15, 2020See SQL12Mon, Mar 16, 2020—Sun, Mar 22, 2020See SQL13Mon, Mar 23, 2020—Sun, Mar 29, 2020See SQL14Mon, Mar 30, 2020—Sun, Apr 5, 2020See SQL15Mon, Apr 6, 2020—Sun, Apr 12, 2020See SQL16Mon, Apr 13, 2020—Sun, Apr 19, 2020See SQL17Mon, Apr 20, 2020—Sun, Apr 26, 2020See SQL18Mon, Apr 27, 2020—Sun, May 3, 2020See SQL19Mon, May 4, 2020—Sun, May 10, 2020See SQL20Mon, May 11, 2020—Sun, May 17, 2020See SQL21Mon, May 18, 2020—Sun, May 24, 2020See SQL22Mon, May 25, 2020—Sun, May 31, 2020See SQL23Mon, Jun 1, 2020—Sun, Jun 7, 2020See SQL24Mon, Jun 8, 2020—Sun, Jun 14, 2020See SQL25Mon, Jun 15, 2020—Sun, Jun 21, 2020See SQL26Mon, Jun 22, 2020—Sun, Jun 28, 2020See SQL27Mon, Jun 29, 2020—Sun, Jul 5, 2020See SQL28Mon, Jul 6, 2020—Sun, Jul 12, 2020See SQL29Mon, Jul 13, 2020—Sun, Jul 19, 2020See SQL30Mon, Jul 20, 2020—Sun, Jul 26, 2020See SQL31Mon, Jul 27, 2020—Sun, Aug 2, 2020See SQL32Mon, Aug 3, 2020—Sun, Aug 9, 2020See SQL33Mon, Aug 10, 2020—Sun, Aug 16, 2020See SQL34Mon, Aug 17, 2020—Sun, Aug 23, 2020See SQL35Mon, Aug 24, 2020—Sun, Aug 30, 2020See SQL36Mon, Aug 31, 2020—Sun, Sep 6, 2020See SQL37Mon, Sep 7, 2020—Sun, Sep 13, 2020See SQL38Mon, Sep 14, 2020—Sun, Sep 20, 2020See SQL39Mon, Sep 21, 2020—Sun, Sep 27, 2020See SQL40Mon, Sep 28, 2020—Sun, Oct 4, 2020See SQL41Mon, Oct 5, 2020—Sun, Oct 11, 2020See SQL42Mon, Oct 12, 2020—Sun, Oct 18, 2020See SQL43Mon, Oct 19, 2020—Sun, Oct 25, 2020See SQL44Mon, Oct 26, 2020—Sun, Nov 1, 2020See SQL45Mon, Nov 2, 2020—Sun, Nov 8, 2020See SQL46Mon, Nov 9, 2020—Sun, Nov 15, 2020See SQL47Mon, Nov 16, 2020—Sun, Nov 22, 2020See SQL48Mon, Nov 23, 2020—Sun, Nov 29, 2020See SQL49Mon, Nov 30, 2020—Sun, Dec 6, 2020See SQL50Mon, Dec 7, 2020—Sun, Dec 13, 2020See SQL51Mon, Dec 14, 2020—Sun, Dec 20, 2020See SQL52Mon, Dec 21, 2020—Sun, Dec 27, 2020See SQL53Mon, Dec 28, 2020—Sun, Jan 3, 2021See SQL

Logic Behind ISO8601

When using extract(week FROM …) PostgreSQL always gets the ISO week. You can use extract(isodow FROM …) to get the ISO day of the week (1-7). You can use extract(isoyear FROM …) to get the ISO year.

Using this logic, the first week of the year has January 4th in it. The last week in the year contains December 28th in it. In other words the ISO year ranges from the week of January 4th to the week of December 28th. In essence the first week of the year must have the majority of the days of the first week (Sunday to Saturday) in it and the same goes for the last week of the year.

Hopefully the logic behind of this and maybe even a portion of the queries will help you too. Happy coding!!! 😎


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK