I want to save date & time & time zone of user when he signup.
I have a 'signup_date' column in account table that its type in Java is OffsetDateTime and in liquibase is Datetime and in PostgreSQL is timestamp with time zone.
In pgadmin, I wrote an insert query with '2019-08-16T20:42:00+03:30' but in PostgreSQL it saves as '2019-08-16 17:12:00+00' and when I get it through api it returns '2019-08-16T21:42:00+04:30'
Why does this happen?
In PostgreSQL, TIMESTAMP WITH TIMEZONE is normalized to UTC (offset 0) when stored. The value doesn't retain the original offset. The value returned or displayed will depend on the PostgreSQL session time zone, the time zone of the application, and possibly on some other factors.
The Answer by Rotteveel is correct and should be accepted. You are being mislead by the well-intentioned but confusing feature of the console session dynamically applying a default time zone after retrieving the value from the database and during the generation of the text you see presented in the console.
You’ll have no such confusion if using JDBC.
Retrieval.
OffsetDateTime myOffsetDateTime = myResultSet.getObject( … , OffsetDateTime.class ) ;
Storage.
OffsetDateTime myOffsetDateTime = OffsetDateTime.parse( "2019-08-16T20:42:00+03:30" ) ;
myPreparedStatement.setObject( … , myOffsetDateTime ) ;