Issue
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
Solution
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 🇮🇳:
ZoneId z = ZoneId.of( "Asia/Kolkata" ) ;
LocalDate today = LocalDate.now( z ) ;
ZonedDateTime start = today.atStartOfDay( z ) ;
ZonedDateTime end = today.plusDays( 1 ).atStartOfDay( z ) ;
We are using the Half-Open approach to defining a span of time, usually best for date-time work.
Convert to OffsetDateTime before passing to your PreparedStatement, to use a data type appropriate to SQL.
OffsetDateTime startOdt = start.toOffsetDateTime() ;
You wrote:
Calendar startDate = (Calendar) Calendar.getInstance();
No need to cast here. That method already returns a Calendar object, so casting is pointless. (And, never use Calendar.)
I’ve been brief here, as this topic has already been addressed many many times on Stack Overflow. Search to learn more.
Answered By - Basil Bourque
