0

I have a JSON column in MySQL 5.7 with object containing different timezones and I need to select everything adjusted to single timezone. I use

DATE_FORMAT(sb.CONTENT ->> "$.field5", "%m/%d/%Y") AS START_DATE

and this formats the date, but I need it adjusted for certain timezone

Tried this:

SELECT CONVERT_TZ('2019-07-02T00:10:00.000Z', '+00:00', '-7:00')

seems like a function for this, but it requires the initial timezone as parameter. Is there a proper way to extract just the timezone, or a function that would use he one from the timestamp ?

ILS
  • 21
  • 5
  • 1
    I don't think MySQL stores timezones in `datetime` values. If your times are in different timezones, you need to store the timezone in a column of your own. – Barmar May 24 '19 at 21:52
  • @Barmar, good point. Wrong wording. The column is actually JSON and I use DATE_FORMAT(sb.CONTENT ->> "$.field5", "%m/%d/%Y") AS `START_DATE` And I need to adjust the timezone – ILS May 24 '19 at 22:16
  • You still need to store the timezone explicitly in your table. – Barmar May 24 '19 at 22:26
  • it is in the string. Like this one: '2019-07-02T00:10:00.000Z' or in other records it looks like this "'2019-07-01T00:10:00-0700'. The information is there. I have no access to the code, so I need to use MySQL means to deal with this. – ILS May 24 '19 at 22:43
  • There's no built-in method to extract the timezone from a string like that. You can use `SUBSTR()`, but you'll need to handle the `Z` case specially. – Barmar May 25 '19 at 02:37
  • Why don't you use a consistent format for your times? – Barmar May 25 '19 at 02:38
  • @ILS take a look at this example stored function: https://stackoverflow.com/a/24312077/1695906 I don't think it handles timestamps with fractional seconds, but may give you a place to start. – Michael - sqlbot May 26 '19 at 01:08
  • @Barmar, I don't have access to the entire code. This it what I have to work with. I totally agree with you though. – ILS Jun 27 '19 at 02:42
  • Thanks for the link, @Michael-sqlbot! This looks good and I can live without the fractions of seconds. – ILS Jun 27 '19 at 02:47

0 Answers0