Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

convert_tz is not compatible with MySQL #9776

Closed
qw4990 opened this issue Mar 18, 2019 · 3 comments
Closed

convert_tz is not compatible with MySQL #9776

qw4990 opened this issue Mar 18, 2019 · 3 comments
Labels
good first issue Denotes an issue ready for a new contributor, according to the "help wanted" guidelines. help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. type/bug The issue is confirmed as a bug. type/compatibility

Comments

@qw4990
Copy link
Contributor

qw4990 commented Mar 18, 2019

Bug Report

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?
    If possible, provide a recipe for reproducing the error.
select convert_tz(now(),'UTC', 'Universal');
select convert_tz(now(),'utc', 'UTC');
select convert_tz('1917-11-07 12:00:00', 'MET', 'UTC'); 
select convert_tz('1970-01-01 01:00:00', 'MET', 'UTC'); 
select convert_tz('1970-01-01 01:00:01', 'MET', 'UTC'); 
select convert_tz('2003-03-01 00:00:00', 'MET', 'UTC');
select convert_tz('2003-03-30 01:59:59', 'MET', 'UTC');
select convert_tz('2003-03-30 02:30:00', 'MET', 'UTC');
select convert_tz('2003-03-30 03:00:00', 'MET', 'UTC');
select convert_tz('2003-05-01 00:00:00', 'MET', 'UTC');
select convert_tz('2003-10-26 01:00:00', 'MET', 'UTC');
select convert_tz('2003-10-26 02:00:00', 'MET', 'UTC');
select convert_tz('2003-10-26 02:59:59', 'MET', 'UTC');
select convert_tz('2003-10-26 04:00:00', 'MET', 'UTC');
select convert_tz('2038-01-19 04:14:07', 'MET', 'UTC');
select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC');
select convert_tz('2103-01-01 04:00:00', 'MET', 'UTC');
  1. What did you expect to see?
    In Mysql
mysql> select convert_tz(now(),'UTC', 'Universal');
+--------------------------------------+
| convert_tz(now(),'UTC', 'Universal') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz(now(),'utc', 'UTC');
+--------------------------------+
| convert_tz(now(),'utc', 'UTC') |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('1917-11-07 12:00:00', 'MET', 'UTC'); 
+-------------------------------------------------+
| convert_tz('1917-11-07 12:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('1970-01-01 01:00:00', 'MET', 'UTC'); 
+-------------------------------------------------+
| convert_tz('1970-01-01 01:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('1970-01-01 01:00:01', 'MET', 'UTC'); 
+-------------------------------------------------+
| convert_tz('1970-01-01 01:00:01', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-03-01 00:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-03-01 00:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-03-30 01:59:59', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-03-30 01:59:59', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-03-30 02:30:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-03-30 02:30:00', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-03-30 03:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-03-30 03:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-05-01 00:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-05-01 00:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-10-26 01:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-10-26 01:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-10-26 02:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-10-26 02:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-10-26 02:59:59', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-10-26 02:59:59', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-10-26 04:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-10-26 04:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2038-01-19 04:14:07', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2038-01-19 04:14:07', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2038-01-19 04:14:08', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2103-01-01 04:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2103-01-01 04:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)
  1. What did you see instead?
    In TiDB
mysql> select convert_tz(now(),'UTC', 'Universal');
+--------------------------------------+
| convert_tz(now(),'UTC', 'Universal') |
+--------------------------------------+
| 2019-03-18 17:42:10.000000           |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz(now(),'utc', 'UTC');
+--------------------------------+
| convert_tz(now(),'utc', 'UTC') |
+--------------------------------+
| 2019-03-18 17:42:10.000000     |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('1917-11-07 12:00:00', 'MET', 'UTC'); 
+-------------------------------------------------+
| convert_tz('1917-11-07 12:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| 1917-11-07 11:00:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('1970-01-01 01:00:00', 'MET', 'UTC'); 
+-------------------------------------------------+
| convert_tz('1970-01-01 01:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| 1970-01-01 00:00:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('1970-01-01 01:00:01', 'MET', 'UTC'); 
+-------------------------------------------------+
| convert_tz('1970-01-01 01:00:01', 'MET', 'UTC') |
+-------------------------------------------------+
| 1970-01-01 00:00:01                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-03-01 00:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-03-01 00:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| 2003-02-28 23:00:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-03-30 01:59:59', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-03-30 01:59:59', 'MET', 'UTC') |
+-------------------------------------------------+
| 2003-03-30 00:59:59                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-03-30 02:30:00', 'MET', 'UTC');
ERROR 1292 (22007): invalid time format: '{2003 3 30 2 30 0 0}'
mysql> select convert_tz('2003-03-30 03:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-03-30 03:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| 2003-03-30 01:00:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-05-01 00:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-05-01 00:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| 2003-04-30 22:00:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-10-26 01:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-10-26 01:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| 2003-10-25 23:00:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-10-26 02:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-10-26 02:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| 2003-10-26 01:00:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-10-26 02:59:59', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-10-26 02:59:59', 'MET', 'UTC') |
+-------------------------------------------------+
| 2003-10-26 01:59:59                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2003-10-26 04:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2003-10-26 04:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| 2003-10-26 03:00:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2038-01-19 04:14:07', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2038-01-19 04:14:07', 'MET', 'UTC') |
+-------------------------------------------------+
| 2038-01-19 03:14:07                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2038-01-19 04:14:08', 'MET', 'UTC') |
+-------------------------------------------------+
| 2038-01-19 03:14:08                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select convert_tz('2103-01-01 04:00:00', 'MET', 'UTC');
+-------------------------------------------------+
| convert_tz('2103-01-01 04:00:00', 'MET', 'UTC') |
+-------------------------------------------------+
| 2103-01-01 03:00:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)
  1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-227-g06183393e-dirty
Git Commit Hash: 06183393ed536aa248248b72d4bf9f5010947f66
Git Branch: master
UTC Build Time: 2019-03-15 08:22:53
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
@qw4990 qw4990 added type/bug The issue is confirmed as a bug. help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. good first issue Denotes an issue ready for a new contributor, according to the "help wanted" guidelines. component/expression labels Mar 18, 2019
@qw4990 qw4990 mentioned this issue Mar 18, 2019
Closed
30 tasks
@Debiancc
Copy link
Contributor

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz

@qw4990
Copy link
Contributor Author

qw4990 commented Mar 21, 2019

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_convert-tz

Thanks for your comment and I have made a mistake here.
This issue should be closed now.

@qw4990
Copy link
Contributor Author

qw4990 commented Mar 21, 2019

This issue is not necessary because it's a mistake that I forget to take Time Zone Tables into account.

@qw4990 qw4990 closed this as completed Mar 21, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
good first issue Denotes an issue ready for a new contributor, according to the "help wanted" guidelines. help wanted Denotes an issue that needs help from a contributor. Must meet "help wanted" guidelines. type/bug The issue is confirmed as a bug. type/compatibility
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@Debiancc @qw4990