Re: Why the extra minute in PostgreSQL when using time zone info?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCAAGBQJVaI0PAAoJEC0ft5FqUuEhATIP/2ex8c2x3I1N6RmkPz0JXJWT
8LbBkpJqLfw6kmrAnUmR2vEjnp9gYcr1nXx0sN7PNJFMYv9RjJGQ9z8OEZph6LJQ
DWguGfzODf5GRm6Xht2CPeXHPrGJxfnfqeqoy7uytzwzPa6Ajf7U+sqKhcI1p6l7
+RPHjWDr3eTr5zYKKTnSi9WIunM7lsZWhKpXmghLw1Il5sXVzYKXEwut5dZwE28M
P8V2cbadhqbg1ijEBvBfhQ5T4mk0yHh2wqIIRj3D9e/jz9CbzeixPMP+CXzyUeAT
bdeSwKfDrqkmkqTyTBNhlVN24c1OYSKpJ9OT294I00kKkyJxO6YdZo0SUCDBiD8/
nxgRycmMdQbJxNY8EFJKkGPlb+feJ8Nh86CGuGPNk05NRRIDjKzVbN9cFu+56LEF
A2JFvvvC6uCVmuh+T0gMusSG3bc9bAje72DtW6FnIDas1CYQMAURHSI7IkBc7e8a
mr402trW4nMD5xCKQtP+AsrWdZmkpU9jnqHUTkT5y8bffrZnqoNcFR4CD2XV81Bi
thLDSkBvyaYfvPrrz/4kj8/rYb/69H3r9RhfSKOTSOBHxuQBGwtEuSPflbejsAO4
FdOANaNIpsR0ok/3tCxi15v04erCIaPq0FeSZu6U5NMHN2Vi7RQTc1Jb5PjGIZq4
0iO0MA1VlKHjBDOC8LhD
=SWXP
-----END PGP SIGNATURE-----
Hi aRkadeFR,

On 05/29/2015 02:09 AM, aRkadeFR wrote:
> Indeed, the problem comes from the representation on PG,
> cause I have only good results (0/30 for the minutes) from
> the python function.
[snip]

The OP already found and posted the solution (see below) and it is not
related to Postgres. Here's a fuller explanation:

A pytz timezone class does not represent a single offset from UTC, it
represents a geographical area which, over the course of history, has
probably gone through several different UTC offsets. The oldest offset
for a given zone, representing the offset from before time zones were
standardized (in the late 1800s, most places) is usually called "LMT"
(Local Mean Time), and it is often offset from UTC by an odd number of
minutes.

When you create a timezone using `pytz.timezone('Europe/London')`, pytz
doesn't yet know what datetime you plan to attach the timezone object
to, so it doesn't know which historical period's offset it should use,
and it defaults to the first one in the zoneinfo database, which is
usually LMT:

>>> import pytz
>>> uktz = pytz.timezone('Europe/London')
>>> uktz
<DstTzInfo 'Europe/London' LMT-1 day, 23:59:00 STD>

You can see that this timezone object is Europe/London _LMT_, and you
can also see that it is one minute offset from UTC (that's the "23:59:00
STD" bit).

The _wrong_ way to attach a pytz timezone to a naive Python datetime is
by passing it to the `tzinfo` argument of the datetime constructor (or
by using `.replace(tzinfo=...)`). When you do this, pytz literally
attaches the exact timezone offset (London LMT, dating from the
mid-1800s) to your datetime:

>>> from datetime import datetime
>>> lmt_dt = datetime(2015, 6, 11, 13, 30, tzinfo=uktz)
datetime.datetime(2015, 6, 11, 13, 30, tzinfo=<DstTzInfo 'Europe/London'
LMT-1 day, 23:59:00 STD>)

If you convert this to UTC, you'll see the effect of that mysterious one
minute offset:

>>> lmt_dt.astimezone(pytz.utc)
datetime.datetime(2015, 6, 11, 13, 31, tzinfo=<UTC>)

The _right_ way to attach a pytz timezone to a naive Python datetime is
to call `tzobj.localize(dt)`. This gives pytz a chance to say "oh, your
datetime is in 2015, so I'll use the offset for Europe/London that's in
use in 2015, rather than the one that was in use in the mid-1800s":

>>> good_dt = uktz.localize(datetime(2015, 6, 11, 13, 30))
>>> good_dt
datetime.datetime(2015, 6, 11, 13, 30, tzinfo=<DstTzInfo 'Europe/London'
BST+1:00:00 DST>)

You can see that this datetime object is in BST (one hour offset from
UTC, at least in June) instead of LMT - a much better choice for this
century. And we can convert it to UTC and get a more sensible result:

>>> good_dt.astimezone(pytz.utc)
datetime.datetime(2015, 6, 11, 12, 30, tzinfo=<UTC>)

Since we're on the Django list here, I should also point out that
`django.utils.timezone` has a `make_aware` method which handles this
correctly for you automatically.

Interesting historical sidenote: the 1-minute offset for London LMT is
based on historical observations such as this [1]:

# From Peter Ilieve (1994-07-06):
#
# On 17 Jan 1994 the Independent, a UK quality newspaper, had a piece about
# historical vistas along the Thames in west London. There was a photo
# and a sketch map showing some of the sightlines involved. One paragraph
# of the text said:
#
# 'An old stone obelisk marking a forgotten terrestrial meridian stands
# beside the river at Kew. In the 18th century, before time and longitude
# was standardised by the Royal Observatory in Greenwich, scholars observed
# this stone and the movement of stars from Kew Observatory nearby. They
# made their calculations and set the time for the Horse Guards and
Parliament,
# but now the stone is obscured by scrubwood and can only be seen by walking
# along the towpath within a few yards of it.'
#
# I have a one inch to one mile map of London and my estimate of the stone's
# position is 51 degrees 28' 30" N, 0 degrees 18' 45" W. The longitude
should
# be within about +-2". The Ordnance Survey grid reference is TQ172761.
#
# [This yields GMTOFF = -0:01:15 for London LMT in the 18th century.]


Carl

[1] https://github.com/eggert/tz/blob/master/europe#L107


>> On Thu, May 28, 2015 at 11:42 PM, Daniel Grace <danwgrace@gmail.com
>> <mailto:danwgrace@gmail.com>> wrote:
>>
>> I used this function, seemed to tidy up the problem:
>>
>> from datetime import datetime
>> from pytz import timezone
>>
>> def utcDtTm(self, dt_tm):
>> tz = timezone('Europe/London')
>> return
>> tz.normalize(tz.localize(dt_tm)).astimezone(timezone('UTC'))

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/55688D0F.9040602%40oddbird.net.
For more options, visit https://groups.google.com/d/optout.