Halo-MySQL 日期和时间函数功能测试

-- 返回当前日期，格式：yyyy-MM-ddSELECT CURDATE() AS curdate, CURRENT_DATE() AS current_date;

-- 返回当前时间，格式：HH:mm:ssSELECT CURTIME() AS curtime, CURRENT_TIME() AS current_time;

-- 返回当前日期和时间，格式：yyyy-MM-dd HH:mm:ssSELECT NOW() AS now, CURRENT_TIMESTAMP() AS current_timestamp,       LOCALTIME() AS localtime, SYSDATE() AS sysdate,        LOCALTIMESTAMP() AS localtimestamp;

-- 返回一个格林尼治标准时间1970-01-01 00:00:00(UTC)到指定时间的秒数,无参数输入时，返回一到当前时间的秒数。SELECT UNIX_TIMESTAMP() AS current_timestamp_unix,       UNIX_TIMESTAMP('1970-01-01 08:01:00') AS specific_datetime_unix;

-- 与UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间 格式：yyyy-MM-dd HH:mm:ssSELECT FROM_UNIXTIME(1721304279) AS specific_unix_datetime,        FROM_UNIXTIME(UNIX_TIMESTAMP()) AS current_timestamp;

-- UTC_DATE 返回当前UTC（世界标准时间）日期值，格式：yyyy-MM-dd -- UTC_TIME 返回当前UTC（世界标准时间）时间值，格式：HH:mm:ss-- UTC_TIMESTAMP 返回当前UTC（世界标准时间）日期时间，格式：YYYY-MM-DD hh:mm:ss-- UTC_TIMESTAMP(fsp) fsp 小数秒精度，值：0~6，默认为0SELECT UTC_DATE() AS utc_date, UTC_TIME() AS utc_time,        UTC_TIMESTAMP() AS utc_timestamp,        UTC_TIMESTAMP(6) AS utc_timestamp_6,       NOW() AS now;

-- YEAR 返回日期中的年份值-- YEARWEEK 返回日期中的年与周-- QUARTER 返回日期是第几季度，范围是1~4SELECT YEAR(NOW()) AS year_date,       YEARWEEK(NOW(),0) AS year_week_date,       QUARTER(NOW()) AS quarter_date,        NOW() AS now;

-- MONTH 返回日期中的月份值, 值：1~12-- MONTHNAME 返回日期中的月份名，例如：January、February、March、April等SELECT MONTH(NOW()) AS month_value, MONTHNAME(NOW()) AS month_name, NOW() AS now;

-- DAYOFYEAR 计算日期是本年的第几天-- DAYOFMONTH 计算日期是本月的第几天SELECT DAYOFYEAR(NOW()) AS day_of_year,       DAYOFMONTH(NOW()) AS day_of_month,       NOW() AS now;

-- DAYNAME 返回日期是星期几，如：Monday、Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday-- DAYOFWEEK 返回日期是星期几，用数值1~7表示，1表示星期日，2表示星期一，依此类推-- WEEKDAY 返回日期是星期几，用数值0~6表示，0表示星期一，1表示星期二，依此类推 SELECT DAYNAME(NOW()) AS day_name,       DAYOFWEEK(NOW()) AS day_of_week,       WEEKDAY(NOW()) AS weekday,        NOW() AS now;

-- WEEK() 计算日期是本年的第几个星期，值：0~53-- WEEKOFYEAR 计算日期是本年的第几个星期，值：1~53SELECT WEEK(NOW()) AS week_value,       WEEKOFYEAR(NOW()) AS week_of_year,        NOW() AS now;

-- HOUR、MINUTE、SECOND、MICROSECOND分别返回时间中的小时值、分钟值、秒钟值、微秒SELECT HOUR(NOW(6)) AS hour,       MINUTE(NOW(6)) AS minute,       SECOND(NOW(6)) AS second,        MICROSECOND(NOW(6)) AS microsecond,        NOW(6) AS now;

-- TIME_TO_SEC 指定的时间值转为秒数-- SEC_TO_TIME 指定的秒数转为一个格式为 HH:MM:SS 的时间值 SELECT TIME_TO_SEC('01:30:30') AS total_seconds, SEC_TO_TIME(5430) AS time_value;

-- TO_DAYS 计算日期,距0000年1月1日的天数-- FROM_DAYS 计算从0000年1月1日开始指定天数后的日期SELECT TO_DAYS(CURDATE()) AS total_days,      FROM_DAYS(739450) AS date,     CURDATE() AS curdate;

-- DATEDIFF(end_date, start_date) 计算计算日期end_date与start_date之间相隔的天数SELECT DATEDIFF('2022-03-20', '2022-03-15') AS day_difference1,        DATEDIFF('2022-03-15', '2022-03-20') AS day_difference2;

-- TIMEDIFF(time1, time2) 返回两个时间之间的差值SELECT TIMEDIFF('16:16:16', '10:10:10') AS time_diff;

-- TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) 计算两个日期和时间之间的差异-- unit：指定时间间隔的单位，如 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 等-- 返回 datetime_expr1 - datetime_expr2 的差异SELECT TIMESTAMPDIFF(YEAR, NOW(), '2024-09-01') AS year_diff,        TIMESTAMPDIFF(MONTH, NOW(), '2024-09-01') AS month_diff,       TIMESTAMPDIFF(DAY, NOW(), '2024-09-01') AS day_diff,       TIMESTAMPDIFF(HOUR, NOW(), '2024-09-01') AS hour_diff,       TIMESTAMPDIFF(MINUTE, NOW(), '2024-09-01') AS minute_diff,       TIMESTAMPDIFF(SECOND, NOW(), '2024-09-01') AS second_diff,       NOW() AS now;

-- ADDDATE(date,day) 计算起始日期date加上day天的日期-- ADDDATE(date,INTERVAL expr type)、DATE_ADD(date,INTERVAL expr type) 两者相同，均是计算起始日期date加上一个时间段后的日期-- TIMESTAMPADD(unit, interval, datetime_expr) 用于在日期和时间上进行加法操作，unit：指定时间间隔的单位，如 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND 等。interval：指定要添加的时间间隔SELECT ADDDATE(NOW(), 1) AS new_day_date,        ADDDATE(NOW(), INTERVAL 1 MONTH  ) AS new_month_date1,        DATE_ADD(NOW(), INTERVAL 2 MONTH  ) AS new_month_date2,        TIMESTAMPADD(WEEK,1,NOW()) AS new_week_date,        NOW() AS now;

-- SUBDATE(date,day) 计算起始日期date减去day天的日期-- SUBDATE(date,INTERVAL expr type) 计算起始日期date减去一个时间段后的日期SELECT SUBDATE(NOW(), 1) AS new_reduce_day_date,        SUBDATE(NOW(), INTERVAL 1 MONTH) AS new_reduce_month_date,        NOW() AS now;

-- ADDTIME(date1,date2) 返回(date1 + date2)的时间-- SUBTIME(date1,date2) 返回(date1 - date2)的时间SELECT ADDTIME(NOW(), '01:00:00') AS new_add_time,        SUBTIME(NOW(), '01:00:00') AS new_sub_time,         NOW() AS now;

-- DATE_FORMAT 设置指定日期的格式-- STR_TO_DATE(str, format) 将指定的字符串根据指定日期格式转为日期/时间-- TIME_FORMAT 设置指定时间的格式SELECT DATE_FORMAT(CURDATE(), '%Y年%m月%d日') AS formatted_date,       STR_TO_DATE('2024/06/06 06:06:06', '%Y/%m/%d %H:%i:%S') AS str_to_date,       TIME_FORMAT(CURTIME(),'%H时%i分%s秒') AS formatted_time;