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);