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
Demystifying Class Variables In Python

Understanding class variables in python

Read more...
Interview Experience: Backend Engineering Internship at Fyle

Wanna know the secret to crack backend engineering interviews? Learn them here and intern at Fyle!

Read more...
The curse of being a Senior Engineer, how to deal with timelines, frustrations, etc

Being a good developer is 50% skill and 50% emotional support; here's my secret to balancing both at the right amount!

Read more...
How did I build cropping of receipts in the mobile app?

Follow Yash's journey of what it takes to reduce manual work for our customers when receipts come in all shapes and sizes!

Read more...
How did we increase Data Extraction accuracy by a whopping ~50%?

Wanna know the secret of data extraction, the complex machine learning models we use, the experiments we did? Read on...

Read more...
The not so secret sauce of my work

From chaos to clarity, follow Chethan's not so secret sauce to excelling at work!

Read more...
From Zero to Hero: The Policy Tests Journey!

The story of policy tests at Fyle

Read more...
How Fyle changed my life from a naive intern to a confident Engineering Lead

A blogpost that documents Shwetabh's journey at Fyle.

Read more...
Vikas Prasad @ Fyle

This document is a user guide to Vikas at work.

Read more...
Gokul K's README

This document is a user guide to Gokul at work.

Read more...

All Topics