How to convert all datetimes to +00:00 depending on their timezones in MySQL?

231 Views Asked by At

I have a bunch of datetime rows in MySQL like:

Birthday

422859600000
418705200000
8132400000
869886000000
422859600000

If I convert this datetimes to the format yyy-mm-dd hh:mm:ss.0000 I get this:

1983-05-27 00:00:00.0000
1983-04-08 22:00:00.0000 (how to convert this to 00:00:00.000)
1970-04-04 04:00:00.0000 (...)
1997-07-25 21:00:00.0000 (...)
1983-05-27 00:00:00.0000 

Then I do an attempt to convert the timezones with this query:

SELECT CONVERT_TZ(FROM_UNIXTIME( birthday / 1000 ), '-02:00', '+00:00') as datetime  FROM users

And finally I get this:

1983-05-27 02:00:00.0000 (wrong!)
1983-04-09 00:00:00.0000
1970-04-05 00:00:00.0000
1997-07-26 00:00:00.0000
1983-05-27 02:00:00.0000 (wrong!)

As you can see the solution is to use CONVERT_TZ but the second parameter may be calculated, how can I calculate the difference of the timezone for each row?

0

There are 0 best solutions below