4

In Django documentation for setting timezone, the list of available choices for timezone are actually postgres timezone parameter strings. So it seems Django uses Postgres for retrieving time.

If so, then the problem is that IST is used to denote both Indian & Israel Standard Time, but postgres uses IST for Israel Standard Time (potentially confusing over a 6th of world's population) and there is NO timezone string for Indian Standard Time.

Not just that, Postgres also misses timezone for some other countries like Nepal (GMT+5:30) and some Pacific Islands.

So, is there any way by which I can set custom timezone string (like GMT+5:30 for India, GMT+5:45 for Nepal, etc.) in Postgres or Django?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user1144616
  • 1,201
  • 2
  • 15
  • 16

1 Answers1

7

For India:

SELECT now() AT TIME ZONE 'Asia/Calcutta';
SELECT now()::timestamp AT TIME ZONE 'Asia/Kolkata';
SELECT now()::timestamp AT TIME ZONE '5:30';

For Nepal:

SELECT now()::timestamp AT TIME ZONE 'Asia/Katmandu';
SELECT now()::timestamp AT TIME ZONE 'NPT';

To set the time zone for the whole session:

SET time zone 'Asia/Calcutta';

To reset it (to the time zone set in postgresql.conf:

RESET time zone;

Find more in the system views pg_timezone_names and pg_timezone_abbrevs

SELECT *
FROM   pg_timezone_names
WHERE  utc_offset BETWEEN '05:00:00' AND '06:00:00'
ORDER  BY utc_offset;

SELECT *
FROM   pg_timezone_abbrevs
WHERE  utc_offset BETWEEN '05:00:00' AND '06:00:00'
ORDER  BY utc_offset;

PostgreSQL manual about AT TIME ZONE construct. About time zones.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I see, the name of the city was changed from `Calcutta` to `Kolkata` back in 2001. But postgres seems to be in no mood to keep up with changes. That worked by the way. Thanks! – user1144616 Mar 22 '12 at 05:40
  • @user1144616: Cool that it works. You may have missed the `Asia/Kolkata` in my post. – Erwin Brandstetter Mar 22 '12 at 05:58
  • postgres does track changes to tzdata, but if you're seriously using 8.1 as you linked to in your question, that's no longer maintained. – araqnid Mar 22 '12 at 23:41
  • @ErwinBrandstetter: Oh yes, I did missed `Asia/Kolkata` there. @araqnid: I am using 9.1, but I couldn't find the relevant documentation for 9.1, so posted 8.1's instead. – user1144616 Mar 23 '12 at 02:42
  • @user1144616: I fixed the link. [More about PostgreSQL documentation versioning here](http://meta.stackexchange.com/q/108714/169168). – Erwin Brandstetter Mar 23 '12 at 12:09