MySQL日期函数

1、已知出生日期,求年龄

SELECT
    '1992-04-10' as birthday,
    curdate(),
    (
        YEAR (curdate()) - YEAR ('1992-04-10')-1 + (
            DATE_FORMAT('1992-04-10', '%m%d') <= (
                DATE_FORMAT(curdate(), '%m%d')
            )
        )
    ) AS '年龄'

SELECT
    YEAR (curdate()) - YEAR (birthday)-1+ (RIGHT(curdate(),5) >= RIGHT(birthday,5));

 

2.出生日期求年龄函数

DROP FUNCTION countage;
DELIMITER //
CREATE FUNCTION countage(birthday VARCHAR(15)) //参数birthday 为日期格式
RETURNS INT(10)
LANGUAGE SQL DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN
DECLARE age INT(10);
IF birthday IS NULL THEN 
RETURN '';
END IF;
SET age = YEAR(CURDATE()) - YEAR(birthday) - 1;
IF MONTH(CURDATE()) = MONTH(birthday) THEN
IF DAY(CURDATE()) > DAY(birthday) THEN 
SET age = age + 1;
END IF;
ELSEIF MONTH(CURDATE()) > MONTH(birthday) THEN
SET age = age +1;
END IF;
RETURN age;
END//
DELIMITER ;

 3.时间戳:

select unix_timestamp(); //当前时间戳

select from_unixtime(unix_timestamp()); --将时间戳转成日期格式

select curdate(); -- 返回今天的时间日期

select now(); --取得当前时间

select year(now()); -- 年

select month(now()); --月

select day(now()) -- 日

select hour(now()) --小时

select minute(now()) --分钟

select second(now()) -- 秒

select datediff(now(),'1997-07-01'); -- 两个日期相距多少天

select date_sub(curdate(),interval 1 day); --取出昨天的日期

select date_add(curdate(),interval -1 day); -- 取出昨天的日期