Develop
월요일 6시 기준으로 주차를 결정함
작은이야기
2013. 6. 18. 18:07
SET @now='2013-06-16 05:00:00';
SET @day_of_week=DAYOFWEEK(@now);
-- select @day_of_week;
SELECT IF(
ABS(@day_of_week-2)=0,
IF(
(DATE(@now) + INTERVAL 6 HOUR)>@now,
WEEK(@now)-1,
WEEK(@now)),
IF(
@day_of_week < 2,
WEEK(@now)-1,
WEEK(@now))
);
---------------------------------------------------------------
---------------------------------------------------------------
DELIMITER $$
DROP FUNCTION IF EXISTS dev_trunk.get_week_by_date$$
CREATE FUNCTION dev_trunk.get_week_by_date( now_date DATETIME, standard_day_of_week INT, standard_hour INT) RETURNS INT
BEGIN
DECLARE day_of_week INT;
DECLARE return_week INT;
SET day_of_week = DAYOFWEEK(now_date);
SET return_week = WEEK(now_date);
IF day_of_week = 2 THEN
IF (DATE(now_date) + INTERVAL 6 HOUR) > now_date THEN
SET return_week = return_week - 1;
END IF;
ELSE
IF day_of_week < 2 THEN
SET return_week = return_week - 1;
END IF;
END IF;
RETURN return_week;
END $$
DROP FUNCTION IF EXISTS dev_trunk.get_week_by_unixtime$$
CREATE FUNCTION dev_trunk.get_week_by_unixtime( now_time INT, standard_day_of_week INT, standard_hour INT ) RETURNS INT
BEGIN
RETURN dev_trunk.get_week_by_date(FROM_UNIXTIME(now_time), standard_day_of_week, standard_hour);
END $$
DELIMITER ;
SELECT dev_trunk.get_week_by_date('2013-06-17 06:00:00', 2, 6);
SELECT dev_trunk.get_week_by_unixtime(UNIX_TIMESTAMP('2013-06-17 05:59:59'), 2, 6);