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

[Daniel Grace]

I am creating some test data for a DateTimeField using the following function:

    def rndDtTm(self):

        uktz = timezone('Europe/London')

        year = 2015

        month = 6

        day = randint(1, 30)

        hour = randint(0, 23)

        minute = 30 * randint(0, 1)

        return datetime(year, month, day, hour, minute, 0, 0, tzinfo = uktz)

When I look at the PostgreSQL field in pgAdminIII the times are either 1 or 31 minutes past the hour:

2015-06-10 22:01:00+01

2015-06-12 21:31:00+01

I was expecting them to be 0 or 30 minutes past the hour.  Why the extra minute?

[Daniel Grace]

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'))

[Avraham Serour]

this is not an extra minute, it is a representation of the timezone offset

--
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...@googlegroups.com.
To post to this group, send email to django...@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/12a0ab90-8108-46b4-8e90-5aadedbb3d2c%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[aRkadeFR]

Hey,

Indeed, the problem comes from the representation on PG,
cause I have only good results (0/30 for the minutes) from
the python function.

I don't get though why it is showing the wrong datetime.
@Avraham: It's not the representation of the timezone offset.
It's a false representation. He is not talking about the *+01*
at the end, but why it is 31 or 1 minute on the datetime.

Correct me if I'm wrong.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAFWa6tJp2CLSSqzkiOMyGysGzxk%3DyhP2OD23toS1FOb43%3D%3D3Dg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

-- 
aRkadeFR

[Carl Meyer]

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

[aRkadeFR]

awesome explanation.

I was only aware of time zone information, and got to
understand the daylight saving time not that long ago.

Glad to know, after reading from you, the historical
differences on the time offset of the same geographical
area.

Have a good weekend :)

--
aRkadeFR