主页 / dba / 问题 / 320400
Accepted
Rollo
Asked: 2022-12-01 06:55:45 +0800 CST

如何在 MySQL 中使用 GROUP BY 时创建变量

  • 772

我有这些功能查询，我需要将 UTC 中的时间戳更改为不同时区的时间戳

    SELECT max(user_id) AS user_id,        
            AVG((model_v3)*40) AS avg_score,
            SUM((model_v3)*40) AS sum_score,
            COUNT(*) AS total_inputs,
            min(`date_cet`) AS oldest_date,       
            max(`date_cet`) AS newest_date,
            case
                when TIME(`date_cet`) between '03:00:00' and '07:59:59' then 6 
                when TIME(`date_cet`) between '08:00:00' and '11:59:59' then 10
                when TIME(`date_cet`) between '12:00:00' and '15:59:59' then 14
                when TIME(`date_cet`) between '16:00:00' and '19:59:59' then 18 
                when TIME(`date_cet`) between '20:00:00' and '02:59:59' then 22
            end as 'time_intervals_hours'
    FROM phonation_features
    WHERE `date_cet` >= CURRENT_TIMESTAMP() - interval 10000 DAY AND        user_id = 5
    GROUP BY time_intervals_hours 
    ORDER BY time_intervals_hours ASC

我处理查询的唯一方法是使用CONVERT_TZ()如下：

    SELECT 
           max(user_id) AS user_id,        
           AVG((model_v3)*40) AS avg_score,
           SUM((model_v3)*40) AS sum_score,
           COUNT(*) AS total_inputs,
           min(convert_tz(date_cet, 'UTC', 'Europe/Paris')) AS oldest_date,       
           max(convert_tz(date_cet, 'UTC', 'Europe/Paris')) AS newest_date,
           case
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '00:00:00' and '05:59:59' then 3
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '06:00:00' and '09:29:59' then 8
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '09:30:00' and '12:29:59' then 11
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '12:30:00' and '15:29:59' then 14
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '15:30:00' and '18:29:59' then 17
               when TIME(convert_tz(date_cet, 'UTC', 'Europe/Paris')) between '18:30:00' and '23:59:59' then 20
           end as 'time_intervals'
    FROM phonation_features
    WHERE convert_tz(date_cet, 'UTC', 'Europe/Paris') >= CURRENT_TIMESTAMP() - interval 5000 DAY AND user_id = 5
    GROUP BY time_intervals 
    ORDER BY time_intervals ASC

有没有办法使用生成变量GROUP BY？

我想创建一个timestamp_timeZone可以在所有查询中重复使用的变量。

在此处输入图像描述

这可能吗？

我需要生成一个 POST/查询，允许使用时区（→欧洲/雅典、→欧洲/布鲁塞尔、→欧洲/伦敦或→欧洲/巴黎。）发送有效载荷，并将响应更新的时间戳+时区。

mysql

1 个回答

  1. Best Answer

    根据给定的数据，我可以考虑创建一个自定义函数

    CREATE FUNCTION timestamp_timezone (my_date datetime)
  RETURNS TIMESTAMP
  DETERMINISTIC
   BEGIN
       DECLARE my_new_date TIMESTAMP;
        SELECT convert_tz(my_date , '+00:00','+01:00') as timestamp_timeZone INTO my_new_date ;
       RETURN my_new_date;
   END

    '+00:00'UTC 和UTC +1'+01:00'在哪里。Europe/Paris

    考虑以下数据，

    CREATE TABLE test_tbl(
date_cet datetime );

insert into test_tbl values
('2022-01-01 09:00:00'),
('2022-01-01 12:00:00'),
('2022-01-01 12:00:00'),
('2022-01-02 16:00:00'),
('2022-01-02 16:00:00'),
('2022-01-03 18:00:00'),
('2022-01-03 18:00:00'),
('2022-01-04 22:00:00'),
('2022-01-05 22:00:00'),
('2022-01-06 23:00:00');

    该功能将作为：

    SELECT timestamp_timezone(date_cet),
       TIME(timestamp_timezone(date_cet)) as time_
from  test_tbl ;

    结果：

    timestamp_timezone(date_cet)      time_
    2022-01-01 10:00:00              10:00:00
    2022-01-01 13:00:00              13:00:00
    2022-01-01 13:00:00              13:00:00
    2022-01-02 17:00:00              17:00:00
    2022-01-02 17:00:00              17:00:00
    2022-01-03 19:00:00              19:00:00
    2022-01-03 19:00:00              19:00:00
    2022-01-04 23:00:00              23:00:00
    2022-01-05 23:00:00              23:00:00
    2022-01-07 00:00:00              00:00:00

    https://dbfiddle.uk/g6rYOHbk

    您的最终查询类似于：

    SELECT   MAX(user_id) AS user_id,        
         AVG((model_v3)*40) AS avg_score,
         SUM((model_v3)*40) AS sum_score,
         COUNT(*) AS total_inputs,
         MIN(timestamp_timezone(date_cet)) AS oldest_date,       
         MAX(timestamp_timezone(date_cet)) AS newest_date,       
           CASE
                WHEN TIME(timestamp_timezone(date_cet)) BETWEEN '03:00:00' AND '07:59:59' THEN 6 
                WHEN TIME(timestamp_timezone(date_cet)) BETWEEN '08:00:00' AND '11:59:59' THEN 10
                WHEN TIME(timestamp_timezone(date_cet)) BETWEEN '12:00:00' AND '15:59:59' THEN 14
                WHEN TIME(timestamp_timezone(date_cet)) BETWEEN '16:00:00' AND '19:59:59' THEN 18 
                WHEN TIME(timestamp_timezone(date_cet)) BETWEEN '20:00:00' AND '02:59:59' THEN 22
           END AS 'time_intervals_hours'
FROM phonation_features
WHERE timestamp_timezone(date_cet) >= CURRENT_TIMESTAMP() - interval 10000 DAY 
AND user_id = 5
GROUP BY time_intervals_hours 
ORDER BY time_intervals_hours ASC

    编辑

    我们无法更改时区或时区，例如 →Europe/Athens、→Europe/Brussels、→Europe/London 或 →Europe/Paris。我们可以添加 CREATE FUNCTION timestamp_timezone (my_date datetime, timeZone) 吗？

    该功能需要稍作修改。对于此要求，您需要两个输入变量，即日期和时区。

    CREATE FUNCTION timestamp_timezone (my_date datetime , my_time_zone varchar(25))
  RETURNS TIMESTAMP
  DETERMINISTIC
   BEGIN
       DECLARE my_new_date TIMESTAMP;
        SELECT convert_tz(my_date , '+00:00',my_time_zone) as timestamp_timeZone INTO my_new_date ;
       RETURN my_new_date;
   END

    Europe/London --- UTC +1

    SELECT timestamp_timezone(date_cet,'+01:00'),
           TIME(timestamp_timezone(date_cet,'+01:00')) as time_
FROM  test_tbl ;

    https://dbfiddle.uk/a-OF8pxF

    调用函数时需要提供两个参数，最终查询如下所示：

    SELECT   MAX(user_id) AS user_id,        
         AVG((model_v3)*40) AS avg_score,
         SUM((model_v3)*40) AS sum_score,
         COUNT(*) AS total_inputs,
         MIN(timestamp_timezone(date_cet,'+01:00')) AS oldest_date,       
         MAX(timestamp_timezone(date_cet,'+01:00')) AS newest_date,       
           CASE
                WHEN TIME(timestamp_timezone(date_cet,'+01:00')) BETWEEN '03:00:00' AND '07:59:59' THEN 6 
                WHEN TIME(timestamp_timezone(date_cet,'+01:00')) BETWEEN '08:00:00' AND '11:59:59' THEN 10
                WHEN TIME(timestamp_timezone(date_cet,'+01:00')) BETWEEN '12:00:00' AND '15:59:59' THEN 14
                WHEN TIME(timestamp_timezone(date_cet,'+01:00')) BETWEEN '16:00:00' AND '19:59:59' THEN 18 
                WHEN TIME(timestamp_timezone(date_cet,'+01:00')) BETWEEN '20:00:00' AND '02:59:59' THEN 22
           END AS 'time_intervals_hours'
FROM phonation_features
WHERE timestamp_timezone(date_cet,'+01:00') >= CURRENT_TIMESTAMP() - interval 10000 DAY 
AND user_id = 5
GROUP BY time_intervals_hours 
ORDER BY time_intervals_hours ASC
    • 1

相关问题