MySQL MIN and сonvert_tz

Question

I'm trying to select MIN date after converting datetime to users timezone with convert_tz same goes with COUNT:

MIN(CONVERT_TZ(dateTime,'+00:00', '+03:00')) AS dateTime,
COUNT(CONVERT_TZ(dateTime,'+00:00', '+03:00')) AS daygroup,

and it doesn't seem to work as expected.

CREATE TABLE sql_test_a 
( 
    ID         VARCHAR(255), 
    dateTime TIMESTAMP

); 

INSERT INTO sql_test_a (ID, dateTime) VALUES ('1', '2019-04-08 19:51:00');
INSERT INTO sql_test_a (ID, dateTime) VALUES ('2', '2019-04-08 19:52:00');
INSERT INTO sql_test_a (ID, dateTime) VALUES ('3', '2019-04-10 19:53:00');

SELECT 
MIN(CONVERT_TZ(dateTime,'+00:00', '+03:00')) AS dateTime,
COUNT(CONVERT_TZ(dateTime,'+00:00', '+03:00')) AS daygroup,
MIN(dateTime) AS dateTime1,
COUNT(dateTime) AS daygroup1
FROM sql_test_a
ORDER BY dateTime

when I tested it no http://sqlfiddle.com seems like converted MIN outputs 2019-04-08 22:51:00 and not converted MIN outputs 2019-04-08T19:51:00Z Is it possible to output timestamp with T and Z from converted MIN ?

How is the min date *after* converting different from the min date *before* converting? — Strawberry, Aug 23 '19 at 12:10
Could you clarify what you expect and provide some table representation with sample data? — Vadim Kharitonov, Aug 23 '19 at 12:32
I guess it should be +3 hours because of time zone difference. Datetime is stored as timestamp. — Atis, Aug 23 '19 at 12:33

score 0 · Answer 1 · answered Aug 23 '19 at 12:49

0

Something to think about...

Here's a data set of just 131072 rows...

SELECT MIN(CONVERT_TZ(dateTime,'+00:00', '+03:00')) AS dateTime FROM my_table;
+---------------------+
| dateTime            |
+---------------------+
| 2001-01-01 03:00:00 |
+---------------------+
1 row in set (0.56 sec)

SELECT CONVERT_TZ(MIN(dateTime),'+00:00', '+03:00') AS dateTime FROM my_table;
+---------------------+
| dateTime            |
+---------------------+
| 2001-01-01 03:00:00 |
+---------------------+
1 row in set (0.04 sec)

answered Aug 23 '19 at 12:49

Strawberry

33,750
13
40
57

ok, that is a BIG difference.. But what about `DATE(CONVERT_TZ(dateTime,'+00:00', '+03:00') ` ? Will it slow down DATE call as well? I cannot group by day if the time zones are different – Atis Aug 23 '19 at 13:37
I don't understand the question. But perhaps it's something you could test for yourself? – Strawberry Aug 23 '19 at 13:40

MySQL MIN and сonvert_tz

1 Answers1