What is the correct M-scale in this case? #3

New Issue

Open

opened 2023-08-24 08:24:59 +00:00 by gg · 1 comment

gg commented

2023-08-24 08:24:59 +00:00

Owner

M-scale Readings

SELECT * FROM mscale
 WHERE jst >= "2023-08-24T14:00:00+09:00"
   AND jst <= "2023-08-24T16:00:00+09:00";

val	jst
1	2023-08-24T14:00:00+09:00
2	2023-08-24T15:00:00+09:00
2	2023-08-24T16:00:00+09:00

My cron job is currently reading the mscale on the 1st and 31st minute of the hour, so the mscale didn't turn to 2 until at least 31 minutes after the show started. On the 2nd and 3rd hour of the show, mscale was recorded as 2. (FYI, I read the value twice an hour for redundancy. There was one instance where I missed an hour due to the API request randomly failing.)

Schedule (after joining)

SELECT seg.n      AS title,
       c.n        AS caster,
       s.video_id AS video_id,
       m.val      AS mscale,
       s.jst      AS jst
  FROM schedule s
       JOIN      segment seg ON seg.id = s.segment_id
       LEFT JOIN caster  c   ON c.id   = s.caster_id
       LEFT JOIN mscale  m   ON m.jst  = s.jst
 WHERE s.jst = "2023-08-24T14:00:00+09:00";

title	caster	video_id	mscale	jst
Afternoon	takayama	-VS7lMkVEhc	1	2023-08-24T14:00:00+09:00

Due to the way we join against the mscale table using the jst column, only the first hour matters when it comes to mscale.

Thumbnail

https://site.weathernews.jp/app/wnlive/thumb/-V/-VS7lMkVEhc.jpg?nocache=%221692857047%22

The thumbnail is yellow which implies mscale 2.

#### M-scale Readings ```sql SELECT * FROM mscale WHERE jst >= "2023-08-24T14:00:00+09:00" AND jst <= "2023-08-24T16:00:00+09:00"; ``` | val | jst | |-----|---------------------------| | 1 | 2023-08-24T14:00:00+09:00 | | 2 | 2023-08-24T15:00:00+09:00 | | 2 | 2023-08-24T16:00:00+09:00 | My cron job is currently reading the mscale on the 1st and 31st minute of the hour, so the mscale didn't turn to 2 until at least 31 minutes after the show started. On the 2nd and 3rd hour of the show, mscale was recorded as 2. (FYI, I read the value twice an hour for redundancy. There was one instance where I missed an hour due to the API request randomly failing.) #### Schedule (after joining) ```sql SELECT seg.n AS title, c.n AS caster, s.video_id AS video_id, m.val AS mscale, s.jst AS jst FROM schedule s JOIN segment seg ON seg.id = s.segment_id LEFT JOIN caster c ON c.id = s.caster_id LEFT JOIN mscale m ON m.jst = s.jst WHERE s.jst = "2023-08-24T14:00:00+09:00"; ``` | title | caster | video_id | mscale | jst | |-----------|----------|-------------|--------|---------------------------| | Afternoon | takayama | -VS7lMkVEhc | 1 | 2023-08-24T14:00:00+09:00 | Due to the way we join against the mscale table using the `jst` column, only the first hour matters when it comes to mscale. #### Thumbnail https://site.weathernews.jp/app/wnlive/thumb/-V/-VS7lMkVEhc.jpg?nocache=%221692857047%22 ![Nana with Yellow Background](https://site.weathernews.jp/app/wnlive/thumb/-V/-VS7lMkVEhc.jpg?nocache=%221692857047%22) The thumbnail is yellow which implies mscale 2.

gg commented

2023-09-05 04:44:37 +00:00

Author

Owner

It happened again.

 SELECT val, jst
   FROM mscale 
  WHERE jst BETWEEN "2023-09-04T14:00:00+09:00" AND "2023-09-04T19:00:00+09:00" 
  ORDER BY jst DESC;

val	jst
1	2023-09-04T19:00:00+09:00
1	2023-09-04T18:00:00+09:00
1	2023-09-04T17:00:00+09:00
2	2023-09-04T16:00:00+09:00
2	2023-09-04T15:00:00+09:00
1	2023-09-04T14:00:00+09:00

Hours 14 and 17 are the first hours of Afternoon and Evening respectively. According to the thumbnails, they're both M-scale 2. Maybe Afternoon could have been interpreted that way, but Evening would have been impossible to guess correctly. The data says M-scale 1 for all 3 hours of the show.

Evening 17:00

Afternoon 14:00

It happened again. ```sql SELECT val, jst FROM mscale WHERE jst BETWEEN "2023-09-04T14:00:00+09:00" AND "2023-09-04T19:00:00+09:00" ORDER BY jst DESC; ``` | val | jst | |-----|---------------------------| | 1 | 2023-09-04T19:00:00+09:00 | | 1 | 2023-09-04T18:00:00+09:00 | | 1 | 2023-09-04T17:00:00+09:00 | | 2 | 2023-09-04T16:00:00+09:00 | | 2 | 2023-09-04T15:00:00+09:00 | | 1 | 2023-09-04T14:00:00+09:00 | Hours 14 and 17 are the first hours of Afternoon and Evening respectively. According to the thumbnails, they're both M-scale 2. Maybe Afternoon could have been interpreted that way, but Evening would have been impossible to guess correctly. The data says M-scale 1 for all 3 hours of the show. #### Evening 17:00 ![Evening](https://site.weathernews.jp/app/wnlive/thumb/Ms/MstsGJbYmUM.jpg) #### Afternoon 14:00 ![Afternoon](https://site.weathernews.jp/app/wnlive/thumb/A1/A1L8diZSSVc.jpg)

No Label

No Milestone

No project

No Assignees

1 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: gg/WeatherNews.jl#3

No description provided.