sql実行時の週番号がおかしい

前提

cakephpは2.x系を使用しています。

該当のソースコード

cakephp
1$sql  = "select date_part('w', d.start) AS start, managed_work_start_time ";
2$sql .= "from ( ";
3$sql .= "       select LAG(managed_work_start_time) OVER W AS start, ";
4$sql .= "              LAG(managed_work_end_time) OVER W AS end, ";
5$sql .= "              LAG(actual_working_times) OVER W AS working_times, ";
6$sql .= "              LAG(reces_time) OVER W AS working_reces_time, ";
7$sql .= "              managed_work_start_time - LAG(managed_work_end_time) OVER W AS rest_time ";
8$sql .= "       from view_obc_work_histories as hist ";
9$sql .= "            LEFT JOIN mt_work_reasons as res on hist.mt_work_reason_id = res.id";
10$sql .= "       where     shift_pattern_id > 0 ";
11$sql .= "             AND (res.type <> 1 OR res.type IS NULL) ";
12$sql .= "             AND mt_employee_id = ? ";
13$sql .= "             AND managed_work_start_time <= ? ";
14$sql .= "             AND managed_work_end_time >= ? ";
15$sql .= "       WINDOW W AS (order by managed_work_start_time asc ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) ";
16$sql .= "      ) as d ";
17$sql .= "where     d.rest_time is not null ";
18$sql .= "      AND CAST(d.end AS DATE) - CAST(d.start AS DATE) > 0 ";
19$sql .= "      AND d.working_times < interval '24:00:00' ";
20$sql .= "      AND d.working_times >= interval '21:00:00' ";
21$sql .= "      AND d.working_reces_time >= interval '4:00:00' ";
22$result = $wHist->query($sql, [$employeeId, $toDate.' 23:59:59', $frDate.' 00:00:00'], false);

問題

上記のソースコードを実行すると、年月日とその週番号が返ってくるのですが、
2020-11-21 02:00:00　と　2020-11-24 02:00:00は47週
2020-11-27 02:00:00は48週
という結果になります。
自分は、24日は21日の次の週なので48週になると思っているのですが考え方が間違っているのでしょうか？

補足情報

必要な情報があれば、コメントにお願いします。
随時、追記します。

タイムゾーン　"Asia/Tokyo"
DB PostgresSQL 9.5

何を参考にしたか
→参考というよりは必要な構文を調べながら新しく作りました。

テーブル定義

■obc_work_histories

create table public.obc_work_histories (
  id integer default nextval('obc_work_histories_id_seq') PRIMARY KEY
  , mt_employee_id integer not null
  , work_day date not null
  , shift_pattern_id integer not null
  , mt_work_reason_id integer not null
  , work_start_time timestamp not null
  , work_end_time timestamp not null
  , reces_time time not null

  , normal_working_time time not null
  , daytime_over_time time not null
  , midnight_over_time time not null
  , total_over_time time not null

  , paid_recess_time time not null

  , leave_desk_1_datetime time not null
  , arrive_desk_1_datetime time not null
  , leave_desk_2_datetime time not null
  , arrive_desk_2_datetime time not null

  , note varchar(20) not null

  , created_user integer not null
  , created_datetime timestamp not null
  , modified_user integer not null
  , modified_datetime timestamp not null
);

■view_obc_work_histories

WITH owh AS (
         SELECT obc_work_histories.id,
            obc_work_histories.mt_employee_id,
            obc_work_histories.work_day,
            obc_work_histories.shift_pattern_id,
            obc_work_histories.mt_work_reason_id,
            obc_work_histories.work_start_time,
            obc_work_histories.work_end_time,
            obc_work_histories.reces_time,
            obc_work_histories.normal_working_time,
            obc_work_histories.daytime_over_time,
            obc_work_histories.midnight_over_time,
            obc_work_histories.total_over_time,
            obc_work_histories.paid_recess_time,
            obc_work_histories.note,
            obc_work_histories.created_user,
            obc_work_histories.created_datetime,
            obc_work_histories.modified_user,
            obc_work_histories.modified_datetime,
            obc_work_histories.leave_desk_1_datetime,
            obc_work_histories.arrive_desk_1_datetime,
            obc_work_histories.leave_desk_2_datetime,
            obc_work_histories.arrive_desk_2_datetime,
            obc_work_histories.work_start_time + '00:01:00'::interval *
                CASE date_part('minutes'::text, obc_work_histories.work_start_time)::integer % 15
                    WHEN 0 THEN 0
                    ELSE 15 - date_part('minutes'::text, obc_work_histories.work_start_time)::integer % 15
                END::double precision AS managed_work_start_time,
            obc_work_histories.work_end_time AS managed_work_end_time
           FROM obc_work_histories
        )
 SELECT owh.id,
    owh.mt_employee_id,
    owh.work_day,
    owh.shift_pattern_id,
    owh.mt_work_reason_id,
    owh.work_start_time,
    owh.work_end_time,
    owh.reces_time,
    owh.normal_working_time,
    owh.daytime_over_time,
    owh.midnight_over_time,
    owh.total_over_time,
    owh.paid_recess_time,
    owh.note,
    owh.created_user,
    owh.created_datetime,
    owh.modified_user,
    owh.modified_datetime,
    owh.leave_desk_1_datetime,
    owh.arrive_desk_1_datetime,
    owh.leave_desk_2_datetime,
    owh.arrive_desk_2_datetime,
    owh.managed_work_start_time,
    owh.managed_work_end_time,
    owh.arrive_desk_1_datetime - owh.leave_desk_1_datetime AS outing_1_time,
    owh.arrive_desk_2_datetime - owh.leave_desk_2_datetime AS outing_2_time,
    owh.managed_work_end_time - owh.managed_work_start_time - (owh.arrive_desk_1_datetime - owh.leave_desk_1_datetime) - (owh.arrive_desk_2_datetime - owh.leave_desk_2_datetime) AS actual_working_times
   FROM owh;

m.ts10806

2021/01/10 02:51

・タイムゾーンは何でしょうか・DBは何でしょうか。バージョンも記載してください。・テーブル定義を記載してください・何を参考にこのSQLを組んだのでしょうか。

kuuhaku4262

2021/01/10 03:20

追記しました。ご指摘、ありがとうございました。

yambejp

2021/01/12 03:34

質問にpostgreのタグを付けたほうが良いかもしれません

YT0014

2021/01/12 06:21

週の始まりは、WEEｋなら日曜から、ISOWEEKなら月曜からが、標準SQLの仕様のようです。ISOWEEKを試してみてはいかがでしょう？ https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions?hl=ja#extract