How to change Java Date timezone from server local timezone to a different timezone for a spring boo-CodePudding

I have records in a db with a column created_date saved as timestamp without time zone. Front-end users are based in 05.30 timezone. Back-end is hosted in Singapore. So the created_date date values are 5.30 hours behind the user's time.

How do i properly select a set of records that got created within a given day.

Eg: if the user is requesting data on 16-oct-2021, server needs to return the data with the created_date between 2021-10-16 00:00:00 and 2021-10-16 23:59:59:999 in their local time which is 05.30

I have a native query written for this as below,

select * from records rr where rr.created_date at time zone 'utc' at time zone 'Asia/Calcutta' between ?1 and ?2

I get the date 1 and 2 with the following code, which is giving the required data set only after 05.30am in users's timezone ( 05.30)

Calendar startDate = (Calendar) Calendar.getInstance();
startDate.set(Calendar.HOUR_OF_DAY, 0);
startDate.set(Calendar.MINUTE, 0);
startDate.set(Calendar.SECOND, 0);
startDate.set(Calendar.MILLISECOND, 0);
                        
Calendar endDate = (Calendar) startDate.clone();
endDate.set(Calendar.HOUR_OF_DAY, 23);
endDate.set(Calendar.MINUTE, 59);
endDate.set(Calendar.SECOND, 59);
endDate.set(Calendar.MILLISECOND, 999);

Date fromDate = startDate.getTime();
Date toDate = endDate.getTime();

So, Assuming that i need to convert the startDate and endDate to the user's local timezone before passing as parameters for the query, i did the following,

Calendar startDate = (Calendar) Calendar.getInstance();
startDate.setTimeZone(TimeZone.getTimeZone("Asia/Calcutta")); // convert from server timezone to user's local timezone
startDate.set(Calendar.HOUR_OF_DAY, 0);
startDate.set(Calendar.MINUTE, 0);
startDate.set(Calendar.SECOND, 0);
startDate.set(Calendar.MILLISECOND, 0);   
                        
Calendar endDate = (Calendar) startDate.clone();
endDate.set(Calendar.HOUR_OF_DAY, 23);
endDate.set(Calendar.MINUTE, 59);
endDate.set(Calendar.SECOND, 59);
endDate.set(Calendar.MILLISECOND, 999);

Date fromDate = startDate.getTime();
Date toDate = endDate.getTime();

This still doesn't return me the correct data set. If i directly run the below query on pgadmin i can get the results

select * from records rr where rr.created_date at time zone 'utc' at time zone 'Asia/Calcutta' between '2021-10-16 00:00:00' and '2021-10-16 23:59:59'

How do i get the same data set with my code? Any help would be much appreciated. TIA

CodePudding user response：

You said:

column created_date saved as timestamp without time zone

That is the wrong data type for recording moments. Specific points on the timeline must be written to a column of the type TIMESTAMP WITH TIME ZONE, not WITHOUT.

Postgres uses the offset-from-UTC or time zone info supplied with any input to adjust to UTC. Values in a TIMESTAMP WITH TIME ZONE column in Postgres are always in UTC, always.

You said:

run the below query on pgadmin

Unfortunately, many tools including pgAdmin have the anti-feature where they apply some default time zone to adjust the value retrieved from the database. This gives the false illusion of the value having been stored with a particular time zone. But as I said, values in a TIMESTAMP WITH TIME ZONE column in Postgres are always in UTC, always. I suggest always setting the pgAdmin session default time zone to UTC.

Never use Calendar or Date. Use only java.time classes with JDBC 4.2 or later.

The time zone Asia/Calcutta has been renamed to Asia/Kolkata.

If you want the start and end of a day as seen in modern India