1

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?

Ali
  • 3,373
  • 5
  • 42
  • 54
Desarrollo Desafio de Guerrero
  • 129
  • 1
  • 2
  • 12
  • If you have a value which is effectively at 12:00 in UTC, how do you expect to tell whether the original time zone was 12 hours ahead or 12 hours behind? Fundamentally it sounds like you haven't got enough information - and it sounds like you should be using a DATE type for the result. – Jon Skeet Feb 02 '17 at 11:29
  • @JonSkeet I expect to do something like this: `SELECT CONVERT_TZ(FROM_UNIXTIME( birthday / 1000 ), CALCULATE_DIFFERENCE( birthday, '+00:00' ), '+00:00') as datetime FROM users`, now the question is HOW? – Desarrollo Desafio de Guerrero Feb 02 '17 at 11:34
  • @JonSkeet Another way a bit more complicated is: `... CALCULATE_DIFFERENCE( EXTRACT_TIMEZONE( birthday ), '+00:00' ) ...` – Desarrollo Desafio de Guerrero Feb 02 '17 at 11:36
  • I think you've missed my point. Suppose the value you've been given is the unix timestamp for 1976-06-19T12:00:00. What's the right birthday in that case? It could be 1976-06-19 (in a time zone with an offset of +12:00:00 at that point) or 1976-06-20 (in a time zone with an offset of -12:00:00 at that point). Heck, the Pacific/Kwajalein time zone has observed both of those offsets... – Jon Skeet Feb 02 '17 at 11:37
  • @JonSkeet If you can, look at here https://stackoverflow.com/questions/41994176/how-to-match-milliseconds-dates-with-different-timezones-in-mysql, that's the initial point of this question and is the same you are saying. I believe there's no problem with that because the timezones difference is very small (2 hours). – Desarrollo Desafio de Guerrero Feb 02 '17 at 11:43
  • If you're happy for it to only work for some time zones, you should state that in the question. (You should also explain that the birthday value is the Unix time of a local midnight - that's what I've been assuming, anyway.) – Jon Skeet Feb 02 '17 at 11:45

0 Answers0