I have a date field in our PostgreSQL table where date is being saved including time stamp and zone (using "localdatetime" in java side when capturing). But requirement is to pull up record checking only the date part (ignore the time and zone), so that all users from all over the world can see the same result if filter by date.

Please let me know should I provide more explanation.

CodePudding user response：

Wrong data types means lost information

postgres table where date is being saved including time stamp and zone ( using "localdatetime" in java side when capturing )

That is a contradiction in types.

The TIMESTAMP WITH TIME ZONE type in PostgreSQL does not save a time zone. Read the documentation carefully. It explains that any time zone or offset information provided along with the date and time-of-day is used to adjust to an offset from UTC of zero hours-minutes-seconds. In other words, the date and time are adjusted “to UTC”, as we say in shorthand.

That zone or offset information you may have provided is then discarded. Postgres does not remember the original offset or zone. Any value retrieved from a column of type TIMESTAMP WITH TIME ZONE is always in UTC. If you care about the original zone or offset, you must store that yourself in a second column.

But you have a problem. You did not provide any indicator of time zone or offset when sending the date-time to the database. You incorrectly used the LocalDateTime class which purposely lacks any indicator of time zone or offset. The LocalDateTime class represents a date with time-of-day, and nothing else. Thus the contradiction mentioned above. You provided two things (date, time-of-day) to a column that needs three things (date, time-of-day, and zone/offset).

I presume that Postgres took your LocalDateTime value and stored it as if it it were a date and time in UTC. So you have lost information. For example, if one row’s input was meant to be noon on the 23rd of January 2022 in Tokyo Japan