【SQL】日付時間操作を完全マスター『どこよりも詳しく解説！』

select hiredate - interval 5 day   as hd_minus_5D,
       hiredate + interval 5 day   as hd_plus_5D,
       hiredate - interval 5 month as hd_minus_5M,
       hiredate + interval 5 month as hd_plus_5M,
       hiredate - interval 5 year  as hd_minus_5Y,
       hiredate + interval 5 year  as hd_plus_5Y
  from emp
 where deptno=10;

select date_add(hiredate,interval -5 day)   as hd_minus_5D,
       date_add(hiredate,interval  5 day)   as hd_plus_5D,
       date_add(hiredate,interval -5 month) as hd_minus_5M,
       date_add(hiredate,interval  5 month) as hd_plus_5M,
       date_add(hiredate,interval -5 year)  as hd_minus_5Y,
       date_add(hiredate,interval  5 year)  as hd_plus_5DY
  from emp
 where deptno=10;

select datediff(ward_hd, allen_hd)
  from (
select hiredate as ward_hd
  from emp
 where ename = 'WARD'
       ) x,
       (
select hiredate as allen_hd
  from emp
 where ename = 'ALLEN'
       ) y;

-- シーケンステーブルを作成(T500)
create table t500 (
	ID int 
);

drop procedure if exists loop_sample;
-- 区切り文字を一時的に「;」→「//」に変更する
delimiter //
-- loop_sampleというプロシージャーを作成する
create procedure loop_sample(in i int)
begin
  -- 変数cntの定義
  declare cnt int default 0;
  -- 繰り返し処理
  while cnt < i do
    set cnt = cnt + 1;
    insert into t500 values(cnt);
  end while;
end
//
-- プロシージャーを実行する
call loop_sample(500);

select sum(case when date_format(
                        date_add(jones_hd,
                                 interval t500.id-1 DAY),'%a')
                  in ('Sat','Sun')
                then 0 else 1
           end) as days
  from (
select max(case when ename = 'BLAKE'
                then hiredate
           end) as blake_hd,
       max(case when ename = 'JONES'
                then hiredate
            end) as jones_hd
  from emp
 where ename in ('BLAKE','JONES')
       ) x,
       t500
 where t500.id <= datediff(blake_hd,jones_hd)+1

select mnth, mnth/12
  from (
select (year(max_hd) - year(min_hd))*12 +
       (month(max_hd) - month(min_hd)) as mnth
  from (
select min(hiredate) as min_hd, max(hiredate) as max_hd
  from emp
       ) x
       ) y;

select datediff(day,allen_hd,ward_hd)*24 hr,
       datediff(day,allen_hd,ward_hd)*24*60 min,
       datediff(day,allen_hd,ward_hd)*24*60*60 sec
  from (
select max(case when ename = 'WARD'
                then hiredate
           end) as ward_hd,
       max(case when ename = 'ALLEN'
                then hiredate
           end) as allen_hd
  from emp
       ) x;

select date_format(
          date_add(
              cast(
            concat(year(current_date),'-01-01')  
                   as date),
                   interval t500.id-1 day),
                   '%W') day,
       count(*)
  from t500
 where t500.id <= datediff(
                         cast(
                       concat(year(current_date)+1,'-01-01')
                              as date),
                         cast(
                       concat(year(current_date),'-01-01')
                              as date))
group by date_format(
            date_add(
                cast(
              concat(year(current_date),'-01-01')
                     as date),
                     interval t500.id-1 day),
                     '%W');

select x.ename, x.hiredate, x.next_hd,
       datediff(x.next_hd, x.hiredate) as diff
  from (
select deptno, ename, hiredate,
       lead(hiredate)over(order by hiredate) as next_hd
   from emp e
       ) x
 where x.deptno=10;

select day(
       last_day(
       date_add(
       date_add(
       date_add(current_date,
                interval -dayofyear(current_date) day),
                interval 1 day),
                interval 1 month))) dy
  from t1;

SET @year = 2024;

SELECT
    @year AS Input_Year,
    CASE
        WHEN @year % 4 = 0 AND @year % 100 != 0 OR @year % 400 = 0 THEN 'うるう年です。'
        ELSE 'うるう年ではありません。'
    END AS Leap_Year_Status;

select datediff((curr_year + interval 1 year),curr_year)
  from (
select adddate(current_date,-dayofyear(current_date)+1) curr_year
  from t1
       ) x;

select date_format(current_timestamp,'%k') hr,
       date_format(current_timestamp,'%i') min,
       date_format(current_timestamp,'%s') sec,
       date_format(current_timestamp,'%d') dy,
       date_format(current_timestamp,'%m') mon,
       date_format(current_timestamp,'%Y') yr
  from t1;

select date_add(current_date,
                interval -day(current_date)+1 day) firstday,
       last_day(current_date) lastday
  from t1;

with recursive cal (dy,yr)
  as
    (
    select dy, extract(year from dy) as yr
  from
    (select adddate(adddate(current_date, interval - dayofyear(current_date) + 1 day), interval 1 day) as dy) as tmp1
  union all
    select date_add(dy, interval 1 day), yr
 from cal
 where extract(year from date_add(dy, interval 1 day)) = yr
 )
    select dy from cal
    where dayofweek(dy) = 6;

WITH RECURSIVE x(dy, dm, mth, dw, wk) AS (
  SELECT
    dy,
    DAY(dy) AS dm,
    MONTH(dy) AS mth,
    DAYOFWEEK(dy) AS dw,
    CASE
      WHEN DAYOFWEEK(dy) = 1 THEN WEEK(dy) - 1
      ELSE WEEK(dy)
    END AS wk
  FROM (
    SELECT DATE_ADD(CURRENT_DATE, INTERVAL -DAY(CURRENT_DATE) + 1 DAY) AS dy
    FROM t1
  ) x
  UNION ALL
  SELECT
    DATE_ADD(dy, INTERVAL 1 DAY),
    DAY(DATE_ADD(dy, INTERVAL 1 DAY)),
    mth,
    DAYOFWEEK(DATE_ADD(dy, INTERVAL 1 DAY)),
    CASE
      WHEN DAYOFWEEK(DATE_ADD(dy, INTERVAL 1 DAY)) = 1 THEN WEEK(DATE_ADD(dy, INTERVAL 1 DAY)) - 1
      ELSE WEEK(DATE_ADD(dy, INTERVAL 1 DAY))
    END
  FROM x
  WHERE MONTH(DATE_ADD(dy, INTERVAL 1 DAY)) = mth
)
SELECT
  MAX(CASE WHEN dw = 2 THEN dm END) AS Mo,
  MAX(CASE WHEN dw = 3 THEN dm END) AS Tu,
  MAX(CASE WHEN dw = 4 THEN dm END) AS We,
  MAX(CASE WHEN dw = 5 THEN dm END) AS Th,
  MAX(CASE WHEN dw = 6 THEN dm END) AS Fr,
  MAX(CASE WHEN dw = 7 THEN dm END) AS Sa,
  MAX(CASE WHEN dw = 1 THEN dm END) AS Su
FROM x
GROUP BY wk
ORDER BY wk;

SET @YEAR = 2023;
SET @MONTH = 6;
SET @START_DATE = CONVERT(CONCAT(@YEAR, '-', @MONTH, '-01'), DATE);
SET @END_DATE = LAST_DAY(@START_DATE);
SET @FIRST_WEEK_START_DATE = DATE_ADD(@START_DATE, INTERVAL ((DAYOFWEEK(@START_DATE) - 1) * -1) DAY);
SET @LAST_WEEK_END_DATE = DATE_ADD(@END_DATE, INTERVAL (7 - (DAYOFWEEK(@END_DATE))) DAY);

WITH RECURSIVE DATE_TABLE(DATE_VALUE) AS (
  SELECT @FIRST_WEEK_START_DATE
  UNION ALL
  SELECT DATE_ADD(DATE_VALUE, INTERVAL 1 DAY)
  FROM DATE_TABLE
  WHERE DATE_VALUE < @LAST_WEEK_END_DATE
)
SELECT
  WEEK(DATE_VALUE) AS WEEK_NO,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 1 THEN DATE_VALUE END) AS SUNDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 2 THEN DATE_VALUE END) AS MONDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 3 THEN DATE_VALUE END) AS TUESDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 4 THEN DATE_VALUE END) AS WEDNESDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 5 THEN DATE_VALUE END) AS THURSDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 6 THEN DATE_VALUE END) AS FRIDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 7 THEN DATE_VALUE END) AS SATURDAY
FROM DATE_TABLE
GROUP BY WEEK_NO
ORDER BY WEEK_NO;

SET @YEAR = 2023;

WITH RECURSIVE MonthCalendar AS (
  SELECT 1 AS Month
  UNION ALL
  SELECT Month + 1
  FROM MonthCalendar
  WHERE Month < 12
),
CalendarDates AS (
  SELECT
    CONCAT(@YEAR, '-', LPAD(Month, 2, '00'), '-01') AS StartDate,
    LAST_DAY(CONCAT(@YEAR, '-', LPAD(Month, 2, '00'), '-01')) AS EndDate
  FROM MonthCalendar
),
CalendarTable AS (
  SELECT
    StartDate,
    EndDate,
    DATE_ADD(StartDate, INTERVAL ((DAYOFWEEK(StartDate) - 1) * -1) DAY) AS FirstWeekStartDate,
    DATE_ADD(EndDate, INTERVAL (7 - DAYOFWEEK(EndDate)) DAY) AS LastWeekEndDate
  FROM CalendarDates
),
DateTable AS (
  SELECT
    DATE_VALUE
  FROM CalendarTable
  CROSS JOIN (
    SELECT StartDate + INTERVAL (n - 1) DAY AS DATE_VALUE
    FROM (
      SELECT ROW_NUMBER() OVER (ORDER BY a.n) AS n
      FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
            UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
            UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) a
      CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
                  UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) b
    ) AS numbers
    CROSS JOIN CalendarTable
    WHERE StartDate + INTERVAL (n - 1) DAY BETWEEN FirstWeekStartDate AND LastWeekEndDate
  ) AS dates
)
SELECT
  WEEK(DATE_VALUE) AS WEEK_NO,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 1 THEN DATE_VALUE END) AS SUNDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 2 THEN DATE_VALUE END) AS MONDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 3 THEN DATE_VALUE END) AS TUESDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 4 THEN DATE_VALUE END) AS WEDNESDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 5 THEN DATE_VALUE END) AS THURSDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 6 THEN DATE_VALUE END) AS FRIDAY,
  MAX(CASE WHEN DAYOFWEEK(DATE_VALUE) = 7 THEN DATE_VALUE END) AS SATURDAY
FROM DateTable
GROUP BY WEEK_NO
ORDER BY WEEK_NO;

 with recursive x (dy,cnt)
    as (
               select
        adddate(current_date,(-dayofyear(current_date))+1) dy
          ,id
           from t1
         union all
               select adddate(dy, interval 3 month ), cnt+1
               from x
        where cnt+1 <= 4
       )

      select quarter(adddate(dy,-1)) QTR
   ,  date_add(dy, interval -3 month) Q_start
   ,  adddate(dy,-1)  Q_end
    from x
    order by 1;

with recursive x (start_date,end_date)
          as
         (
      select
          adddate(min(hiredate),
          -dayofyear(min(hiredate))+1)  start_date
          ,adddate(max(hiredate),
          -dayofyear(max(hiredate))+1)  end_date
          from emp
       union all
          select date_add(start_date,interval 1 month)
          , end_date
          from x
          where date_add(start_date, interval 1 month) < end_date
      )

       select x.start_date mth, count(e.hiredate) num_hired
           from x left join emp e
           on (extract(year_month from start_date)
               =
               extract(year_month from e.hiredate))
           group by x.start_date
           order by 1;

select ename
  from emp
where monthname(hiredate) in ('February','December')
   or dayname(hiredate) = 'Tuesday';

select concat(a.ename,
       ' と、同月同平日に採用 ',
       b.ename) msg
  from emp a, emp b
 where date_format(a.hiredate,'%w%M') =
       date_format(b.hiredate,'%w%M')
   and a.empno < b.empno
order by a.ename;

CREATE TABLE `emp_project` (`empno` INT NOT NULL , `ename` VARCHAR(20) NOT NULL , `proj_id` INT NOT NULL , `proj_start` DATE NOT NULL , `proj_end` DATE NOT NULL )) ;


INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7782, 'CLARK', 1, '2020-06-16', '2020-06-18');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7782, 'CLARK', 4, '2020-06-19', '2020-06-24');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7782, 'CLARK', 7, '2020-06-22', '2020-06-25');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7782, 'CLARK', 10, '2020-06-25', '2020-06-28');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7782, 'CLARK', 13, '2020-06-28', '2020-07-02');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7839, 'KING', 2, '2020-06-17', '2020-06-21');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7839, 'KING', 8, '2020-06-23', '2020-06-25');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7839, 'KING', 14, '2020-06-29', '2020-06-30');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7839, 'KING', 11, '2020-06-26', '2020-06-27');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7839, 'KING', 5, '2020-06-20', '2020-06-24');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7934, 'MILLER', 3, '2020-06-18', '2020-06-22');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7934, 'MILLER', 12, '2020-06-27', '2020-06-28');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7934, 'MILLER', 15, '2020-06-30', '2020-07-03');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7934, 'MILLER', 9, '2020-06-24', '2020-06-27');
INSERT INTO `emp_project`(`empno`, `ename`, `proj_id`, `proj_start`, `proj_end`) VALUES (7934, 'MILLER', 6, '2020-06-21', '2020-06-23');

select a.empno,a.ename,
       concat('プロジェクト ',b.proj_id,
        ' 重複しているプロジェクト ',a.proj_id) as msg
  from emp_project a,
       emp_project b
 where a.empno = b.empno
   and b.proj_start >= a.proj_start
   and b.proj_start <= a.proj_end 
   and a.proj_id != b.proj_id;