Compare and insert date and calendar week with two different tables

Question

I have two tables in my database. In the first table is a column named date where i insert a date period in a 1 day interval. In the second table are the calendar weeks of the same date period and an autoincrement column weekid.

For example I have the calendar week 25 with the weekid 145 (saved in the second table). The date area is from 21.06-27.06 and is saved in the first table. Now i want to insert the weekid into the first table for every day (date) matching the calendar weeks.

Here are my tables:

CREATE TABLE `day` (
  `dayid` int(255) NOT NULL AUTO_INCREMENT,
  `userid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`dayid`)

CREATE TABLE `week` (
  `weekid` int(255) NOT NULL AUTO_INCREMENT,
  `userid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `calendar week` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `year` year(4) NOT NULL,
  PRIMARY KEY (`weekid`

Example output for table "day": weekid: 145 userid: 589 date: 2021-05-06

Example output for table "week": weekid: 145 userid: 589 calendar week: 25 year: 2021

Does anyone have an idea how to do the date comparison?

Answer 1

If in the second table you don't have the dates of each week you can just calculate it on the first table right?

SELECT (EXTRACT(DOY FROM date_in_table1)/7)

should produce the work week of a certain date.