JDBI and Timezones

I am an Engineering Intern at Fyle and lately, I have been working on a core feature involving the export of expense reports in PDF format. The problem was that we would routinely get requests for exporting the same set of expense reports. The idea was that we could return a previously exported PDF instead of regenerating it. The solution involved comparing timestamps of recent requests for exports and that’s where I ran into an interesting issue.

Our export service is written in Java using the Dropwizard framework. We use PostgreSQL as our database and JDBI as the lightweight ORM layer. We store all time-related information as timestamp without time zone data type and we treat it as UTC time. We ran into a strange issue - that is, the data was actually being recorded in local timezone. In this post, we cover this issue and the solution, in case someone else runs into it.

As part of the new feature, I had to create an API to check for duplicate exports created in the past. When we make an export request, the Java service connects to the DB via JDBI and updates the created and last updated times in created_at and updated_at columns (timestamp without time zone) respectively. So, let’s say if we have to check for duplicate exports that were created in the last 24 hours, we can do that with the help of these timestamp columns. After I was finished building my new API, I tested it on Postman but my API returned an empty response every time (though there were already some duplicate exports created in the past).

Example Request:

curl ht​tp://localhost:5000/api/exports?...&created_at=<timestamp>&updated_at=<timestamp>

Response:

{}

To further check on what went wrong, I checked the database records for the corresponding exports. There I saw that the timestamp columns were having a value according to local timezone, and not UTC.

Example Query:

select created_at, updated_at from exports where id=<export_id>;

What I did next was to check the timezone of local DB’s server, and guess what, it was UTC. Then, I logged into the psql console and tried creating a record manually for the corresponding exports using the insert command.

Example Query:

insert into exports values (..., created_at=now(), updated_at=now());

The values were now being stored correctly (in UTC), so there was no problem in the database setup itself. It had to do something with the Java service.

To debug further, I dived deep into the Java service, specifically in the DAO where this creation of a record was handled. I found out these things:

• The timestamps are being used in our Java service as java.sql.Timestamp objects

• This class just like its base class java.util.Date, does not store timezone information

• JDBI uses this Timestamp object and stores it in the DB as a timestamp column

• If no explicit timezone is specified, JDBI is going to assume the local timezone

• So, ultimately, when API tries creating a record in the DB via JDBI, the timezone of the timestamp columns is in local timezone

So, how did we finally fix this issue? We just changed the default JVM timezone to UTC. By the way, the value of the timezone can be altered with the below JVM argument:

-Duser.timezone=UTC

Also, this problem didn’t arise in any of the staging/prod environments, because the whole OS and the corresponding databases of the server where these environments were hosted are configured to be in UTC timezone as default.

Here’s a classic YouTube video discussing the problems associated with Time and Timezones:

Hope this post was insightful. If you really like it, please upvote/comment and share your thoughts. If you would like to connect with me on LinkedIn, please do.