1

I have a Java application that inserts data into a database using prepared statements. In the preparedStamement date is set in UTC format.

preparedStatement.setDate(index, new java.sql.Date(date.getTime()), UTC);

I want to be sure that when read and write operations execute on the table, the response should ALWAYS be in UTC format. At the below query, when the data is read it will be converted to the client's timezone. I don't want TIME_COLUMN to be converted to any time zone. It should remain in the UTC time zone. How can I define TIME_COLUMN in that way?

Notes: I cannot edit the DB timezone. I cannot edit select queries using At time zone.

"TIME_COLUMN" TIMESTAMPTZ default (now() at time zone 'utc'),
yed2393
  • 262
  • 1
  • 12
  • I recommend you don’t use `java.sql.Date`. That class is poorly designed and long outdated. Instead use `LocalDate` from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). – Ole V.V. Sep 14 '21 at 20:17
  • Also `java.sql.Date` in its day was for an SQL `date`, never for an SQL `timestamptz`. I am sorry, I would not expect it to work what you are trying. – Ole V.V. Sep 14 '21 at 20:20

1 Answers1

2

You could set the timezone of your RDBMS to UTC, see https://medium.com/building-the-system/how-to-store-dates-and-times-in-postgresql-269bda8d6403

When that's done, whatever dates you store, they will be in UTC. Converting from UTC into something else can be done either in queries, like

select created_at at time zone 'utc' at time zone 'america/los_angeles'
from users;

Taken from https://popsql.com/learn-sql/postgresql/how-to-convert-utc-to-local-time-zone-in-postgresql

Or, you can convert the timezone at application level.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Hello Lajos, thanks for the comment. I edited the question added Notes section. I cannot set db's time zone and cannot change the select queries because they are written in application level and should not be changed. – yed2393 Sep 14 '21 at 09:57
  • 1
    @yed2393 If you are not allowed to change the database timezone, nor the queries, then I do not really see a way to cope with this situation. You will definitely need to be able to handle the timezone either at the RDBMS level or application level. – Lajos Arpad Sep 14 '21 at 10:24