2

I'm using AWS RDS for a PostgreSQL 11 database with PGAdmin. I store creation date of every row using now()::timestamp to store date and time without time zone. The problem is the time stored is never the value I want (UTC+05:30).

If I set parameter timezone as UTC in RDS, then the time stored is 5:30 hours behind India's time but if I set UTC+05:30, it stores either 11 hours behind time or 1 hour more, this happens randomly in random rows. I haven't changed anything else, just this timezone parameter.

I searched a lot but didn't find any similar answer.

9

tl;dr

  • 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.

The 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

The 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.

Table of date-time types in Java (both legacy and modern) and in standard SQL

1
  • Thank you, sir, for a very detailed answer related to my question. As new to PostgreSQl, I needed this. I'm going to switch to timestamp with timezone to avoid the problem and will use the AT TIME ZONEfunction. Mar 16 '20 at 5:46
1

I think you should use this as the default value:

current_timestamp AT TIME ZONE '-05:30'

Then PostgreSQL will take the current point in time and format it to a timestamp as shown in India at that moment.

2
  • As @BasilBourque put it so well: 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. The American state of Indiana (just smaller than Hungary) has 3 time zones - with counties moving back and forth faster than a horse can gallop! Fascinating stuff - I was becoming bewildered by the end of the article!
    – Vérace
    Mar 16 '20 at 10:43
  • 1
    @Vérace I agree. But India has only a single time zone, and the question asked specifically for an offset of 5:30. Mar 16 '20 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.