- Instead of the data type
TIMESTAMP WITHOUT TIME ZONE, define your column for the data type
TIMESTAMP WITH TIME ZONE.
- Set the default time zone of your session within PgAdmin to UTC.
- To adjust the retrieved UTC value into a particular time zone in SQL, call
AT TIME ZONE 'Asia/Kolkata'. (But better to leave that zone adjustment to your application code rather than your SQL.)
TIMESTAMP WITHOUT TIME ZONE
to store date and time without time zone.
You are using the wrong data type when defining your column.
TIMESTAMP WITHOUT TIME ZONE data type cannot represent a moment.
This type purposely lacks the context of a time zone or offset-from-UTC. So you have a date and a time-of-day, such as noon on the 23rd of next January. But we have no way to know if you meant noon in Tokyo Japan, noon in Toulouse France, or noon in Toledo Ohio US, all very different moments several hours apart.
Any time zone or offset information passed along with an input is ignored when reaching a column of type
TIMESTAMP WITHOUT TIME ZONE.
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE type can track a moment, a specific point on the timeline.
Any time zone or offset information passed along with the date and time-of-day is used to adjust into UTC. The zone/offset is then discarded. So if you care about the original zone/offset, you need to explicitly store that in an extra column.
When retrieving a
TIMESTAMP WITH TIME ZONE value, the value will always be in UTC (an offset of zero hours-minutes-seconds). However, beware of tools, middleware, or drivers carrying the anti-feature of injecting some default time zone or offset onto the retrieved value. This creates the illusion of that zone/offset having been stored when in fact Postgres always stores in UTC values of the data type
TIMESTAMP WITH TIME ZONE.
PgAdmin is one of the tools with this unfortunate anti-feature. I suggest always setting the default zone of the session to UTC to see your retrieved values as they were stored.
Adjusting to time zone
I suggest you learn to think and work as a programmer in UTC. Most of your business logic in programming should be in UTC. Adjust into a time zone only for presentation to the user or where business logic demands. So this means that in most cases you should adjust to time zone only in your app code rather than in your SQL and database admin tools.
AT TIME ZONE function
But if you insist on adjusting to time zone with your SQL, use the
AT TIME ZONE function.
You mentioned the offset of five and a half hours ahead of UTC,
+05:30. I assume you mean time in India. Always prefer a real time zone name rather than a particular offset. Politicians frequently change the offset used by their jurisdictions. So hard-coding an offset could lead to faulty results.
Keep the tzdata file in your Postgres installation up-to-date, and then let Postgres determine the offset appropriate to the named time zone for that particular moment.
… AT TIME ZONE 'Asia/Kolkata' …
Various date-time data types
This chart I made for Java programmers may also be of use here, to better understand the various data types defined by the SQL standard.