0

I have a field `opened_at` in MySQL table that stores datetimes in string format with different timezones:

2021-01-30T18:07:40+02:00
2021-01-31T01:32:40+05:00
2021-01-31T21:17:40+03:00
etc.

I need to SELECT rows from this table with values in `opened_at` within the specified local date of local timezone, for example 2021-01-31 +02:00.
The problem is that when I cast `opened_at` to datetime I loose timezone offsets, so I can't do correct comparison. For example:

mysql> SELECT CAST("2021-01-30T18:07:40+02:00" AS DATETIME) = CAST("2021-01-30T18:07:40+00:00" AS DATETIME);
+-----------------------------------------------------------------------------------------------+
| CAST("2021-01-30T18:07:40+02:00" AS DATETIME) = CAST("2021-01-30T18:07:40+00:00" AS DATETIME) |
+-----------------------------------------------------------------------------------------------+
|                                                                                             1 |
+-----------------------------------------------------------------------------------------------+

But "2021-01-30T18:07:40+02:00" is not equal to "2021-01-30T18:07:40+00:00". There's a two-hour difference between them!

I've read this, but the way in winning answer is too hard for SELECT from big number of rows.
Is there some quick and processor-efficient way to cast strings with times in different timezones for further comparison with some datetime interval?

6
  • Store all times in the same UTC tz and convert only when really needed.
    – Kondybas
    Feb 3 at 8:38
  • @Kondybas, this is very good tactics but it's not my database and I can do nothing with it's schema, I can do only SELECT. Moreover, I simplified the task a little: in reality, the opened_at field is not a database field, but one of the elements of the JSON type field (`checkbox_shifts`.`data`->>'$.opened_at' AS `opened_at`), and this JSON absolutely cannot be changed. Feb 3 at 8:52
  • 1
    It depends. Should noon here be treated as equal to noon there? Or how should I tell you "I will call you at noon"? Bottom line: You probably should have used TIMESTAMP instead of DATETIME. (Too late now.)
    – Rick James
    Feb 8 at 23:19
  • @RickJames, It seems to me that storing time with a timezone other than the SQL server timezone is a bad idea anyway. But sometimes you have to start from this as a given and look for ways to do the job in such a situation. Feb 9 at 4:29
  • 1
    @IvanOlshansky It's a bad idea to store time in any timezone but UTC. UTC has no daysavings transitions, no governmental intrusions etc. It's a pure time in a common reason meaning. UTC can be converted to an arbitraty TZ having proper data for conversion. And what is most valuable, UTC times/timestamps can be compared/added/substracted by plain arithmetic operations with no conversion.
    – Kondybas
    Feb 14 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.