How to create a variable while using GROUP BY in MySQL

Question

I have these functional queries where I need to change timestamp in UTC to timestamp with different time zones

    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

The only way I reach to work with my queries is using CONVERT_TZ() as below:

    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

Is there a way to generate a variable using GROUP BY?

I would like to create a variable timestamp_timeZone where I can reuse in all my queries.

Is this possible?

I need to generate a POST/query that allows to send a payload with the timeZone(→Europe/Athens, →Europe/Brussels, →Europe/London or →Europe/Paris.) and will responde the updated timestamsp+timezone.

Answer 1

Based on the given data I can think of creating a custom function

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

Where '+00:00' is UTC and '+01:00' the Europe/Paris UTC +1.

Consider the following data ,

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

the function would work as :

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

Result:

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

Your final query would look something like:

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

Edit

We can't change de timeZone or time region like →Europe/Athens, →Europe/Brussels, →Europe/London or →Europe/Paris. can we add CREATE FUNCTION timestamp_timezone (my_date datetime, timeZone) ?

The function need a little modification. For this requirement you need two input variables the date and the time zone.

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

When calling the function you need to give two arguments, your final query would look like:

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