1

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.

enter image description here

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.

4
  • That CASE could be done with a formula. Meanwhile, there is a comma missing immediately before what the "near" points to.
    – Rick James
    Nov 30, 2022 at 15:19
  • Do you have an example about the formula way of doing i never heared about it.. What do you mean about "near" ? The queries are working. just the last one i used to express the error.
    – Rollo
    Nov 30, 2022 at 15:27
  • I still see "Syntax error near 'max(...'..." -- because of the missing comma before "max".
    – Rick James
    Nov 30, 2022 at 19:09
  • The formula would be something like 4*round(time_to_sec(time(tt))/(4*3600))
    – Rick James
    Nov 30, 2022 at 19:19

1 Answer 1

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
3
  • Wow i didn't know we could create a function like these in mysql.. I will check that with my CTO tomorrow these is beyong my knowledge, thanks a lot for your time.
    – Rollo
    Nov 30, 2022 at 18:29
  • Hi i'm checking your interesting function.. But if i get it right.. 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) ?
    – Rollo
    Dec 1, 2022 at 5:49
  • Thanks a lot you shawed me a new posible world in MySQL
    – Rollo
    Dec 1, 2022 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.