Storing UTC Time Zone Deviations In My Model?

[lin...@gmail.com]

All,
In my locations model, I'm trying to add UTC time zone deviations. I
have tried a number of things, but haven't had any success. I would
appriciate any suggestions on how to solve this problem(Note: I'm using
Postgresql + RLP Branch).

Model:

from django.db import models
from django.contrib.auth.models import User
from django.contrib.contenttypes.models import ContentType

# Create your models here.

COUNTRY = (
('United States', 'United States'),
('Canada', 'Canada'),
)

UTC_TIME_ZONE_DEVIATION = (
('-12:00', 'UTC-12:00 M|Y (IDLW, Baker Island, Bikini
Atolls)'),
('-11:00', 'UTC-11:00 X (American Samoa, Midway Atoll)'),
('-10:00', 'UTC-10:00 W (Hawaii-Aleutian Standard Time,
Aleutian Islands)'),
('-09:30', 'UTC-09:30 V* (French Polynesia, Marquesas
Islands)'),
('-09:00', 'UTC-09:00 V (Alaska Standard Time, French
Polynesia)'),
('-08:00', 'UTC-08:00 U (Pacific Standard Time, British
Columbia)'),
('-07:00', 'UTC-07:00 T (Mountain Standard Time, Arizona)'),
('-06:00', 'UTC-06:00 S (Central Standard Time, Belize)'),
('-05:00', 'UTC-05:00 R (Eastern Standard Time, Brazil)'),
('-04:00', 'UTC-04:00 Q (Atlantic Standard Time, Dominica)'),
('-03:30', 'UTC-03:00 P* (Newfoundland Standard Time)'),
('-03:00', 'UTC-03:00 P (Argentina,Uruguay)'),
('-02:00', 'UTC-02:00 O (Mid-Atlantic, South Georgia)'),
('-01:00', 'UTC-01:00 N (Cape Verde, Azores)'),
('+00:00', 'UTC 00:00 Z (UTC, GMT, Zulu)'),
('+01:00', 'UTC+01:00 A (Oslo,Zurich,Vienna)'),
('+02:00', 'UTC+02:00 B (South Africa, Ukraine)'),
('+03:00', 'UTC+03:00 C (Russia, Saudi Arabia)'),
('+03:30', 'UTC+03:30 C* (Iran)'),
('+04:00', 'UTC+04:00 D (Russia, United Arab Emirates)'),
('+04:30', 'UTC+04:30 D* (Afghanistan)'),
('+05:00', 'UTC+05:00 E (Tajikistan, Uzbekistan)'),
('+05:30', 'UTC+05:30 E* (Indian Standard Time, Sri Lanka)'),
('+06:00', 'UTC+06:00 F (Bangladesh, Kazakhstan)'),
('+06:30', 'UTC+06:30 F* (Cocos Islands, Myanmar)'),
('+07:00', 'UTC+07:00 G (Cambodia, Indonesia)'),
('+08:00', 'UTC+08:00 H (Australian Western Standard Time,
China)'),
('+08:45', 'UTC+08:45 H (Western Austrailia,
Caiguna-Eucla-Border)'),
('+09:00', 'UTC+09:00 I (Japan Standard Time, Korea Standard
Time)'),
('+09:30', 'UTC+09:30 I* (Australian Central Standard Time,
Northern Territory)'),
('+10:00', 'UTC+10:00 K (Australian Eastern Standard Time,
Queensland)'),
('+10:30', 'UTC+10:30 K* (New South Wales)'),
('+11:00', 'UTC+11:00 L (Solomon Islands, Magadan Oblast)'),
('+11:30', 'UTC+11:30 L* (Norfold Island)'),
('+12:00', 'UTC+12:00 M|Y (Fiji, Marshall Islands)'),
)

class Location(models.Model):
created_by = models.ForeignKey(User)
created_on = models.DateTimeField(auto_now_add=True)
content_type = models.ForeignKey(ContentType)
country = models.CharField(maxlength=100, choices=COUNTRY,
default='United States', help_text="Example: United States, Canada,
Germany")
address_1 = models.CharField(maxlength=100, help_text="Example:
100N 1st Street.")
address_2 = models.CharField(maxlength=100, blank=True, null=True,
help_text="Example: 100N 1st Street.")
area = models.CharField(maxlength=100, help_text="Eample: A City,
Town, or Village.")
region = models.CharField(maxlength=100, help_text="Example: A
State, Provice, or Territory.")
postal_code = models.CharField(maxlength=50, blank=True, null=True,
help_text="Example: 82072")
latitude = models.FloatField(max_digits=11, decimal_places=6,
blank=True, null=True)
longitude = models.FloatField(max_digits=11, decimal_places=6,
blank=True, null=True)
show_on_map = models.BooleanField(default=False)
time_zone = models.TimeField('Time Zone',
choices=UTC_TIME_ZONE_DEVIATION, blank=True, null=True,
help_text="Optional: You can specify the UTC Time Zone for this
location.")

def __str__(self):
return "%s, %s, %s, %s" % (self.country, self.address_1,
self.region, self.area )

def get_absolute_url(self):
#return "/posts/%s/%s/" %
(self.date.strftime("%Y/%b/%d").lower(), self.slug)
return "/locations/%i/" % self.id

class Admin:
ordering = ['country']
list_display = ('country', 'address_1', 'address_2',
'region','area', 'postal_code', 'time_zone',)
#list_filter =
('created_on','last_modified','enable_comments','pub_state',)

Thanks!
--Nick Pavlica

[Malcolm Tredinnick]

On Fri, 2006-10-06 at 13:17 -0700, lin...@gmail.com wrote:
> All,
> In my locations model, I'm trying to add UTC time zone deviations. I
> have tried a number of things, but haven't had any success. I would
> appriciate any suggestions on how to solve this problem(Note: I'm using
> Postgresql + RLP Branch).

I think you left out a bit of necessary information here. What problems
are you seeing?

These sorts of questions are always easier to help with if you can say
"Here's what I tried. Here's what I expected to happen, instead this
happened."
Regards,
Malcolm

[lin...@gmail.com]

All,
I agree with Malcolm, I could have done a much better job
detailing my question(s), so I will give it another shot.

Synopsis:

I'm trying to develop a model for international locations that can
be associated with various content types such as an article,
advertisement, user, etc. I would like to strike a balance between
user effort and ease of use by balancing my workload and the users.
Additionally, I would like to give users the ability to show maps of
there locations, and localize some of there content based on there time
zone.
I have prototyped a number of possible solutions, but haven't quite
worked out all of the kinks. The question that I was initially trying
to pose was how to best store the UTC time zone data. I have been
experimenting with various data types starting with integers, until I
discovered a number of locations that don't fall on an even number like
India (UTC + 05:30). I moved to a float and stored the UTC offset
for India as (5.5). This didn't quite seem right so I attempted to
store the Time Zone data in a Time Field without success
(00:00:00+05:30). After my initial post, I continued to look for a
solution and discovered that when creating a time field you have to
specify the 'with time zone' parameter. I manually created a test
table and was able to successfully store a time with time zone data
(00:00:00+05:30). Upon further research I discovered the strip time
module (time.strptime) that allows you to convert strings to time.
With this new information it seems logical to store the UTC offset data
as string then convert it to time when needed. This would eliminate
the need for custom SQL, but would add the overhead of converting the
string to time. At this point I'm still trying to sort out the best
method of storing and dealing with with this data. What is the best
practice for this? I'm also trying to flush out this model as a whole
so any suggestions to improve it would be greatly appreciated.

Here is my most recent prototype model:
===================================================================

from django.db import models
from django.contrib.auth.models import User
from django.contrib.contenttypes.models import ContentType

# Create your models here.

# Populate this table for the user.
class Country(models.Model):
created_on = models.DateTimeField(auto_now_add=True)
iso_code = models.CharField(maxlength=10, help_text="US, GB, AT,
BZ")
name = models.CharField(maxlength=100, unique=True,

help_text="Example: United States, Canada, Germany")

flag = models.ImageField(upload_to='flags', null=True, blank=True)
def __str__(self):
return (self.name)

class Admin:
pass

#Populate this table for the user:
class TimeZone(models.Model):
created_on = models.DateTimeField(auto_now_add=True)
std_deviation = models.CharField(maxlength=10)
abbreviation = models.CharField(maxlength=10, help_text="Y, X, M")
description = models.CharField(maxlength=100)
observes_dst = models.BooleanField(default=True)
dst_begins = models.DateTimeField()
dst_end = models.DateTimeField()

def __str__(self):
return "%s, %s" %(self.std_deviation, self.description)

class Admin:
pass

# I ask the user to enter the finer details of the location. I just
provide them with Country and Time Zone Information.

class Location(models.Model):
created_by = models.ForeignKey(User)
created_on = models.DateTimeField(auto_now_add=True)
content_type = models.ForeignKey(ContentType)

country = models.ForeignKey(Country)

address_1 = models.CharField(maxlength=100, help_text="Example:
100N 1st Street.")
address_2 = models.CharField(maxlength=100, blank=True, null=True,
help_text="Example: 100N 1st Street.")
area = models.CharField(maxlength=100, help_text="Eample: A City,
Town, or Village.")
region = models.CharField(maxlength=100, help_text="Example: A
State, Provice, or Territory.")
postal_code = models.CharField(maxlength=50, blank=True, null=True,
help_text="Example: 82072")
latitude = models.FloatField(max_digits=11, decimal_places=6,
blank=True, null=True)
longitude = models.FloatField(max_digits=11, decimal_places=6,
blank=True, null=True)
show_on_map = models.BooleanField(default=False)

time_zone = models.ForeignKey(TimeZone)

def __str__(self):
return "%s, %s, %s, %s" % (self.country, self.address_1,
self.region, self.area )

def get_absolute_url(self):
#return "/posts/%s/%s/" %
(self.date.strftime("%Y/%b/%d").lower(), self.slug)
return "/locations/%i/" % self.id

class Admin:
ordering = ['country']
list_display = ('country', 'address_1', 'address_2',
'region','area', 'postal_code', 'time_zone',)
#list_filter =
('created_on','last_modified','enable_comments','pub_state',)

Thanks!
Nick Pavlica

[lin...@gmail.com]

All,
As with most things in this industry there are multiple ways of doing
the same thing. Since my last post I have been working with this model
and have come full circle in selecting what appears to be the best
method of storing the timezone offset data in my model. I'm currently
using the following filed in my time zone model:

std_offset = models.SmallIntegerField()

Here are some bits of information that may help you:

- There are 1440 Minutes in a day (24*60)

- You can calculate the offset in minutes by multiplying the offset
time as follows:
ex.( -7 hours * 60 minutes = -420)

Here is an example using the mx date time module:

>>> from mx.DateTime import *
>>> print now() + RelativeDateTime(minutes=-420)
2006-10-09 02:58:02.000

I hope this is helpful for those new to this problem. If you have a
better solution please let me know. If I make a new discovery I will
post it here or on the wiki.

Thanks!
-Nick Pavlica

[James Bennett]

On 10/9/06, lin...@gmail.com <lin...@gmail.com> wrote:
> std_offset = models.SmallIntegerField()

Keep in mind that some time zones are offset by partial hours, so this
doesn't actually give complete coverage. The problematic time zones
are:

French Polynesia (UTC - 9:30)
Labrador and Newfoundlad (UTC - 3:30)
Iran (UTC + 3:30)
Afghanistan (UTC + 4:30)
India, Sri Lanka (UTC + 5:30)
Nepal (UTC + 5:45)
Myanmar (UTC + 6:30)
Australia (various bits are on UTC + 9:30 and UTC + 10:30)
Norfolk Island (UTC + 11:30)
Chatham Islands (UTC + 12:45)

--
"May the forces of evil become confused on the way to your house."
-- George Carlin

[gkelly]

I have been struggling with timezones in my application as well. I've
looked into using the PyTZ module from http://pytz.sourceforge.net/
PyTZ is basically an implementation for the tzinfo parameter of the
datetime module.
It can do some pretty neat things, such as taking a naive datetime (a
datetime without a timezone) and localizing it for a given timezone
(takes care of daylight savings time automatically).

That's the most I have done with it so far, but I'm sure it can do more
so it might be worth looking into.

[lin...@gmail.com]

James,
Would it be valid to take the hours and multiply them by 60 then add
the remaining minutes? For example:

French Polynesia (UTC - 9:30)

offset = -((9 * 60 = 540) + 30)
offset = -570 minutes

>>> print now()
2006-10-09 20:31:02.000
>>> print now() + RelativeDateTime(minutes=-570)
2006-10-09 11:01:04.000

Chatham Islands (UTC + 12:45)

offset = ((12*60) + 45)
offset = 765 minutes

>>> print now()
2006-10-09 20:32:21.000
>>> print now() + RelativeDateTime(minutes=765)
2006-10-10 09:17:26.000

--Nick

[lin...@gmail.com]

I will take a look at that, thanks for pointing it out!

--Nick

[Malcolm Tredinnick]

On Mon, 2006-10-09 at 19:38 -0700, lin...@gmail.com wrote:
> James,
> Would it be valid to take the hours and multiply them by 60 then add
> the remaining minutes? For example:

I had thought that's what you were proposing in your most recent post.

Definitely store the number as minutes. It's what all the cool people
do.

Cheers,
Malcolm

[Ivan Sagalaev]

gkelly wrote:
> I have been struggling with timezones in my application as well. I've
> looked into using the PyTZ module from http://pytz.sourceforge.net/

BTW it weighs about a megabyte :-). The reason is that it's not enough
to just store a deviation from UTC to get the right local time.
Different countries have different rules for switching to and from
daylight saving time and may be there are other issues.