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:

{% code %}
curl ht​tp://localhost:5000/api/exports?...&created_at=<timestamp>&updated_at=<timestamp>
{% code-end %}

Response:

{% code %}
{}
{% code-end %}

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:

{% code %}
select created_at, updated_at from exports where id=<export_id>;
{% code-end %}

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:

{% code %}
insert into exports values (..., created_at=now(), updated_at=now());
{% code-end %}

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:

{% code %}
-Duser.timezone=UTC
{% code-end %}

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.

Jebin Philipose

Jebin is an ex-member of Fyle. We remember him being extremely fond of chai, building applications, and participating in competitive programming events.

More of our stories from

Engineering
How we created a Medium-like blurry background effect

Here's how we improved user experience, decreased load time and made Fyle accessible for users without a fast internet.

Read more...
Bye bye WordPress, welcome Webflow.

This blogpost documents our journey as we bid goodbye to WordPress and migrated to Webflow.

Read more...
How we reduced our website build time by 59%

I came up with five 3 second changes to reduce the build time by over 59%. Here's more about my experience.

Read more...
Hello, Web Technologies!

I’m a first-time entrepreneur and I’ll be recording my learnings and experiments over time. I am always eager to interac

Read more...
The Non-Boring Guide to OAuth 2.0

If you’re developing an application that needs access to a user’s Google / Facebook / LinkedIn information, you’ll need

Read more...
Dealing with Nested Objects in your Web Application

A couple of weeks ago, I ran into a peculiar problem that I think might be useful to talk about. It took me a bunch of

Read more...
Eliminate Boilerplate Java code with Lombok

I’ve been writing a lot of boilerplate Java code, lately — getters, setters, hashCode, equals and toString. Actually, I’

Read more...
Hello, Web Technologies! — Part II

This is a follow-up to my first post about technology choices I made while building out our product. I wanted to pen my

Read more...
Sharing Files using S3 Pre-signed URLs

Amazon’s S3 is a reliable, cheap way to store data. We use it to store user-uploaded images and documents as s3 objects

Read more...
JSON Web Token Concepts

There are many technical articles about JSON web tokens (JWT) on the interwebs, but I haven’t found one that explains...

Read more...

All Topics