One of our team members reached out to me for help: a bug in the code she’s working on and she’s not sure what’s causing it.

Here’s what happened

There is a query that is running periodically to fetch data, and the logs are recording how many times it fetches data, but it keeps returning zero data as a result of the query.

Strangely, when I run that query directly through the database tool, I get two data hits.

**This means that the same query would get 0 results if executed at runtime on the server and 2 results if executed directly in the database tool.

My first suspicion was that the query was misconfigured, which caused the object binding through the ORM to fail. However, an error should have been returned if the object binding failed. There were no errors in the code in question.

My next point of suspicion was that the code that was running was a different version of the actual code. I asked a member of the team, and it wasn’t the case.

I needed more clues.

I first sshed directly to the pod where the code was running, wondering if it was an environmental issue. I installed the mysql cli tool and ran a query on the pod directly, and it returned two normal results. In other words, it wasn’t an environmental issue.

I tried this and that for a while, but didn’t see anything unusual. I pulled up the query in question in the IDE and started to take a closer look.

The table referenced by the query had the following columns: start_time, end_time, and timezone. The columns start_time and end_time stored time data in the format hhMM. The query was to retrieve rows whose current time falls between start_time and end_time based on the corresponding timezone.

The part that changes the current time to timezone is shown below.

... CONVERT_TZ(NOW(), 'GMT', timezone), ...

Since I was unfamiliar with the CONVERT_TZ function itself, I checked the MySQL official documentation for a definition.

CONVERT_TZ(*dt*,*from_tz*,*to_tz*)

CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value.

In other words, the function assumes that the timezone at dt is from_tz and changes it to to_tz. There’s nothing wrong with using this function. However, it suddenly occurred to me that if the value returned by NOW() is not GMT, the query might not return the rows it should return.

I went back to the MySQL documentation and found that the `NOW()’ function is affected by the session’s timezone setting. On the backend, you don’t want to change the session’s timezone in the middle of a query. However, you can set the session’s timezone in the connection string at the initial time of connecting to the database. I looked up the connection string and sure enough, the timezone was set to Asia/Seoul.

parseTime=true&charset=utf8&loc=Local&time_zone=%27Asia%2FSeoul%27

Gotcha.

I removed the timezone setting from the connection string and ran the code again, and finally saw that it was fetching the two data sets correctly.