如何在MariaDB 10中为用户分配指定小时范围的随机UNIX时间戳？

针对你的需求（MariaDB 10环境，生成符合时间范围的随机UNIX时间戳，且已分配时间稳定），以下是修正后的完整方案：

核心问题修正 #

你之前的SQL存在两个关键问题：

• 时间差判断逻辑错误：random_hour是UNIX时间戳（整数），直接与CURRENT_TIMESTAMP() - 86400（datetime类型）比较会导致计算异常，需用UNIX_TIMESTAMP()统一转换为整数后计算时间差。
• 未限制时间范围：未过滤出GMT 8:00-16:00的时段，需先锁定目标时间区间再生成随机值。

最终UPDATE语句 #

UPDATE users
SET random_hour = (
    SELECT 
        @start + FLOOR(RAND() * (@end - @start + 1))
    FROM (
        -- 计算GMT时区下的关键时间点
        SELECT
            CONVERT_TZ(NOW(), @@session.time_zone, '+00:00') AS current_gmt,
            UNIX_TIMESTAMP(CONVERT_TZ(CURDATE(), @@session.time_zone, '+00:00') + INTERVAL 8 HOUR) AS today_8gmt,
            UNIX_TIMESTAMP(CONVERT_TZ(CURDATE(), @@session.time_zone, '+00:00') + INTERVAL 16 HOUR) AS today_16gmt,
            UNIX_TIMESTAMP() - 86400 AS past_24h_start
    ) AS time_points,
    -- 确定随机时间的起始区间
    (SELECT @start := CASE
        WHEN today_8gmt >= past_24h_start THEN today_8gmt
        ELSE today_8gmt - 86400
    END) AS set_start,
    -- 确定随机时间的结束区间
    (SELECT @end := CASE
        WHEN today_16gmt >= past_24h_start AND UNIX_TIMESTAMP() <= today_16gmt THEN UNIX_TIMESTAMP()
        WHEN today_16gmt >= past_24h_start THEN today_16gmt
        ELSE today_16gmt - 86400
    END) AS set_end
)
-- 仅更新未分配或已过期的用户（保证已分配时间稳定）
WHERE random_hour = 0 OR (UNIX_TIMESTAMP() - random_hour > 86400);

逻辑说明 #

1. 时区转换：用CONVERT_TZ将会话时区时间转换为GMT（+00:00），确保时间范围判断准确。
2. 区间锁定：
   • 计算过去24小时的起始时间（当前时间减86400秒）。
   • 根据当前GMT时间，自动判断目标区间是今日8:00-16:00还是昨日8:00-16:00；如果当前时间在今日8-16点之间，结束时间取当前时间（避免生成未来时间）。
3. 随机时间生成：在锁定的区间内生成整数UNIX时间戳，FLOOR(RAND() * (@end - @start +1))确保生成的时间戳落在区间内且为整数。
4. 稳定保证：WHERE条件仅更新random_hour=0（未分配）或已过期（距离当前时间超过24小时）的用户，已分配的有效时间戳不会被覆盖。

注意事项 #

• 若CONVERT_TZ返回NULL，说明MariaDB未加载时区数据，执行以下命令修复（需root权限）：

  mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
  

• 若你的会话时区已设为GMT，可简化CONVERT_TZ部分，直接用CURDATE() + INTERVAL 8 HOUR等。

内容的提问来源于stack exchange，提问作者kerstoff0mega