Postgresql query cast timestamp without time zone adding a second to passed time

Question

We have a procedure which accepts a timestamp without time zone.

When I pass say 'Sun Dec 03 11:02:19 GMT 2023'

To Postgresql procedure from java by setting date which is java.util.date

The procedure has a cast timestamp with out timezone like IN run_date timestamp without time zone.

The expected date field value should be 2023-12-03 11:03:19 But 2023-12-03 11:03:20 is stored in db

Why is a second getting added?

We have a logic of fetching the row using the exact date time .so no row is returned because of the seconds mismatch

Answer 1

The Java timestamp you are storing probably has fractional seconds, like 2023-04-01 12:30:00.8888. Then one of these things can happen:

1. the database column is defined as data type timestamp(0), so PostgreSQL rounds the value up

2. the database column is a normal timestamp(6), and the timestamp is stored as it is, but the program you use to visualize the database contents rounds to seconds

Use a database client that does not lie, like psql, and examine the stored values and the table definition.