Converting from local -> UTC

[Keith Hughitt]

Hi,

I am having a little trouble figuring out how to convert a python
datetime to UTC. I have a UTC date (e.g. 2008-07-11 00:00:00). I would
like to create a UTC date so that when I send it to MySQL (which
treats all dates at local dates by default), it will already have
incorporated the proper UTC offset. I've tried looking through the
docs http://python.active-venture.com/lib/datetime-datetime.html), but
have not had any luck.

Does anyone have any suggestions? Any help would be greatly
appreciated.

Thanks,
Keith

 

[Gabriel Genellina]

En Fri, 11 Jul 2008 15:42:37 -0300, Keith Hughitt

I am having a little trouble figuring out how to convert a python
datetime to UTC. I have a UTC date (e.g. 2008-07-11 00:00:00). I would
like to create a UTC date so that when I send it to MySQL (which
treats all dates at local dates by default), it will already have
incorporated the proper UTC offset. I've tried looking through the
docs http://python.active-venture.com/lib/datetime-datetime.html), but
have not had any luck.

You have to use a "timezone aware" datetime object. If all you want is to
store an UTC date, the tzinfo demo classes that you can find in the Python
docs at <http://docs.python.org/lib/datetime-tzinfo.html> may be enough.
A more complete implementation is at <http://pytz.sourceforge.net/>

If you pass a "timezone aware" datetime object as a SQL parameter, the
database should store it correctly (but I don't have a MySQL instance at
hand to test it)

 

[Sebastian \lunar\ Wiesner]

En Fri, 11 Jul 2008 15:42:37 -0300, Keith Hughitt


You have to use a "timezone aware" datetime object. If all you want is to
store an UTC date, the tzinfo demo classes that you can find in the Python
docs at <http://docs.python.org/lib/datetime-tzinfo.html> may be enough.
A more complete implementation is at <http://pytz.sourceforge.net/>

The python-dateutil package also provide classes to deal with timezone-aware
datetime objects. See <http://labix.org/python-dateutil>

 

[Keith Hughitt]

En Fri, 11 Jul 2008 15:42:37 -0300, Keith Hughitt  


You have to use a "timezone aware" datetime object. If all you want is to  
store an UTC date, the tzinfo demo classes that you can find in the Python  
docs at <http://docs.python.org/lib/datetime-tzinfo.html> may be enough.
A more complete implementation is at <http://pytz.sourceforge.net/>

If you pass a "timezone aware" datetime object as a SQL parameter, the  
database should store it correctly (but I don't have a MySQL instance at  
hand to test it)

Thanks for advice Gabriel. I downloaded the tzinfo demo class, saved
it as
UTC.py and imported it. I'm still not exactly sure how to use it
though. It looks like
the file already creates an instance of the UTC tzinfo class (line 20:
"utc = UTC()"),
however, when I try to test it out in the interpreter, it cannot be
found. I'm new
to python, and there is probably something obvious I'm missing, but do
you have any ideas?
Here is what I'm attempting:

============ output begin =============

Python 2.5.2 (r252:60911, Apr 21 2008, 11:12:42)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.Traceback (most recent call last):
Traceback (most recent call last):
Traceback (most recent call last):

============ output begin =============

Any ideas?

Thanks,
Keith

 

[Gabriel Genellina]

En Mon, 14 Jul 2008 12:06:30 -0300, Keith Hughitt

Thanks for advice Gabriel. I downloaded the tzinfo demo class, saved
it as
UTC.py and imported it. I'm still not exactly sure how to use it
though. It looks like
the file already creates an instance of the UTC tzinfo class (line 20:
"utc = UTC()"),
however, when I try to test it out in the interpreter, it cannot be
found. I'm new
to python, and there is probably something obvious I'm missing, but do
you have any ideas?

The import statement in Python doesn't behave the same way as similar
statements in other languages - and it may be confusing you. I'll try to
explain it using this example.
You have:
- a *file* UTC.py, containing the source code for the *module* UTC. It
contains:
- a *class* definition (UTC) and
- an *instance* of that class, utc.

--- begin UTC.py ---
class UTC(tzinfo):
...
utc = UTC()
....
--- end UTC.py ---

Here is what I'm attempting:

============ output begin =============

Python 2.5.2 (r252:60911, Apr 21 2008, 11:12:42)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.

Here you have imported the *module* UTC. That is, the name UTC now refers
to a newly created module just loaded from the UTC.py file.

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: 'module' object is not callable

The error comes from UTC(): UTC is a module, UTC() is attempting to "call"
it, and since modules are not callable objects, we get a TypeError.

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
NameError: name 'utc' is not defined

The *only* name we have imported so far is UTC - the module. Lowercase utc
isn't defined.

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: 'module' object is not callable

Same as above...

Ok, how to solve it? We know that UTC refers to the *module* with the same
name. To get the *class* inside that module, use UTC.UTC - try again in
the interpreter. To create a new instance of that class, you can use
UTC.UTC(). To obtain the instance already created in the UTC module, use
UTC.utc

**OR**

Import those names explicitely:

py> from UTC import UTC

In this case the name UTC refers to the *class* inside the module.
In this particular example it may be confusing - both have the same name.
Another example from the standard library: the poplib module contains a
POP3 class, so after executing this line:

py> from poplib import POP3

the name POP3 refers to that class. The poplib module itself isn't
directly available.
Back to the UTC module, you could use:

py> from UTC import utc

and now utc refers to the *instance* already created inside the module.
This last form may be the most convenient in your case:

py> import datetime
py> from UTC import utc
py> print datetime.datetime(2008, 7, 14, 20, 30, 0, 0, utc)
2008-07-14 20:30:00+00:00

 

[Keith Hughitt]

Thanks Gabriel!

That helps clear things up for me. The above method works very well. I
only have one remaining question:
How can I pass a datetime object to MySQL?'

So far, what I've been doing is building the query as a string, for
example:

query = "INSERT INTO image VALUES(%d, %d, %s, '%s')" % (id, meas,
date, 'jpg')
cursor.execute(query)

This works fine for regular datetime objects, which are passed as
strings similar
to: "2003-10-01 00:00:00." When incorporating a timezone, however, the
resulting string
is of the form "2003-10-01 00:00:00+00:00." Unfortunately, MySQL does
not recognize
the offset.

I know you said you don't use MySQL, but how would you do something
execute a similar query
on the database you normally interface with?


Thanks,
Keith

En Mon, 14 Jul 2008 12:06:30 -0300,KeithHughitt  
<[email protected]> escribió:

Thanks for advice Gabriel. I downloaded the tzinfo demo class, saved
it as
UTC.py and imported it. I'm still not exactly sure how to use it
though. It looks like
the file already creates an instance of the UTC tzinfo class (line 20:
"utc = UTC()"),
however, when I try to test it out in the interpreter, it cannot be
found. I'm new
to python, and there is probably something obvious I'm missing, but do
you have any ideas?

The import statement in Python doesn't behave the same way as similar  
statements in other languages - and it may be confusing you. I'll try to  
explain it using this example.
You have:
- a *file* UTC.py, containing the source code for the *module* UTC. It  
contains:
- a *class* definition (UTC) and
- an *instance* of that class, utc.

--- begin UTC.py ---If you pass a "timezone aware" datetime object as a SQL parameter
class UTC(tzinfo):
   ...
utc = UTC()
...
--- end UTC.py ---

Here is what I'm attempting:

============ output begin =============

Python 2.5.2 (r252:60911, Apr 21 2008, 11:12:42)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.

import datetime, UTC

Here you have imported the *module* UTC. That is, the name UTC now refers  
to a newly created module just loaded from the UTC.py file.

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: 'module' object is not callable

The error comes from UTC(): UTC is a module, UTC() is attempting to "call"  
it, and since modules are not callable objects, we get a TypeError.

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'utc' is not defined

The *only* name we have imported so far is UTC - the module. Lowercase utc  
isn't defined.

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: 'module' object is not callable

Same as above...

Ok, how to solve it? We know that UTC refers to the *module* with the same  
name. To get the *class* inside that module, use UTC.UTC - try again in  
the interpreter. To create a new instance of that class, you can use  
UTC.UTC(). To obtain the instance already created in the UTC module, use  
UTC.utc

**OR**

Import those names explicitely:

py> from UTC import UTC

In this case the name UTC refers to the *class* inside the module.
In this particular example it may be confusing - both have the same name.  
Another example from the standard library: the poplib module contains a  
POP3 class, so after executing this line:

py> from poplib import POP3

the name POP3 refers to that class. The poplib module itself isn't  
directly available.
Back to the UTC module, you could use:

py> from UTC import utc

and now utc refers to the *instance* already created inside the module.  
This last form may be the most convenient in your case:

py> import datetime
py> from UTC import utc
py> print datetime.datetime(2008, 7, 14, 20, 30, 0, 0, utc)
2008-07-14 20:30:00+00:00

 

[Lawrence D'Oliveiro]

In message

I have a UTC date (e.g. 2008-07-11 00:00:00). I would like to create a
UTC date ...

1215734400

ldo@theon> TZ=NZ date -d "00:00:00 01-Jan-1970Z +1215734400 seconds"
Fri Jul 11 12:00:00 NZST 2008

The general form of the call is "calendar.timegm((Y, M, D, hh, mm, ss, 0,
0, -1))".

See also <http://docs.python.org/lib/module-time.html>,

... so that when I send it to MySQL (which treats all dates at local dates
by default) ...

I don't like to use MySQL's date/time types. Instead, I usually have a
simple integer field containing seconds since some origin time in UTC. If
the time is that of some event, an origin of 00:00:00 01-Jan-1970 lets you
use Unix/Linux system times directly. For recording dates/times that might
go further back (e.g. dates of birth, historical events), I have used the
Julian day origin, 1st January 4713 BC.

 

[M.-A. Lemburg]

Thanks Gabriel!

That helps clear things up for me. The above method works very well. I
only have one remaining question:
How can I pass a datetime object to MySQL?'

So far, what I've been doing is building the query as a string, for
example:

query = "INSERT INTO image VALUES(%d, %d, %s, '%s')" % (id, meas,
date, 'jpg')
cursor.execute(query)

Use binding parameters and it should work:

query = "INSERT INTO image VALUES(%d, %d, %s, '%s')"
cursor.execute(query, (id, meas, date, 'jpg'))

Database interfaces typically do not support timezones, so I'm not
sure why you are making things more complicated by adding a timezone
to the date/time value.

This works fine for regular datetime objects, which are passed as
strings similar
to: "2003-10-01 00:00:00." When incorporating a timezone, however, the
resulting string
is of the form "2003-10-01 00:00:00+00:00." Unfortunately, MySQL does
not recognize
the offset.

I know you said you don't use MySQL, but how would you do something
execute a similar query
on the database you normally interface with?

Thanks,
Keith

En Mon, 14 Jul 2008 12:06:30 -0300,KeithHughitt
<[email protected]> escribió:

On Jul 12, 12:52 am, "Gabriel Genellina" <[email protected]>
wrote:
En Fri, 11 Jul 2008 15:42:37 -0300,KeithHughitt
<[email protected]> escribió:
I am having a little trouble figuring out how to convert a python
datetime to UTC. I have a UTC date (e.g. 2008-07-11 00:00:00). I would
like to create a UTC date so that when I send it to MySQL (which
treats all dates at local dates by default), it will already have
incorporated the proper UTC offset. I've tried looking through the
docshttp://python.active-venture.com/lib/datetime-datetime.html), but
have not had any luck.
You have to use a "timezone aware" datetime object. If all you want is
to
store an UTC date, the tzinfo demo classes that you can find in the
Python
docs at <http://docs.python.org/lib/datetime-tzinfo.html> may be enough.
Thanks for advice Gabriel. I downloaded the tzinfo demo class, saved
it as
UTC.py and imported it. I'm still not exactly sure how to use it
though. It looks like
the file already creates an instance of the UTC tzinfo class (line 20:
"utc = UTC()"),
however, when I try to test it out in the interpreter, it cannot be
found. I'm new
to python, and there is probably something obvious I'm missing, but do
you have any ideas?

The import statement in Python doesn't behave the same way as similar
statements in other languages - and it may be confusing you. I'll try to
explain it using this example.
You have:
- a *file* UTC.py, containing the source code for the *module* UTC. It
contains:
- a *class* definition (UTC) and
- an *instance* of that class, utc.

--- begin UTC.py ---If you pass a "timezone aware" datetime object as a SQL parameter
class UTC(tzinfo):
...
utc = UTC()
...
--- end UTC.py ---

Here is what I'm attempting:
============ output begin =============
Python 2.5.2 (r252:60911, Apr 21 2008, 11:12:42)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
import datetime, UTC

Here you have imported the *module* UTC. That is, the name UTC now refers
to a newly created module just loaded from the UTC.py file.

t = datetime.datetime(2008, 7, 14, 00, 00, 00, UTC())
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: 'module' object is not callable

The error comes from UTC(): UTC is a module, UTC() is attempting to "call"
it, and since modules are not callable objects, we get a TypeError.

utc
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
NameError: name 'utc' is not defined

The *only* name we have imported so far is UTC - the module. Lowercase utc
isn't defined.

utc = UTC()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: 'module' object is not callable

Same as above...

Ok, how to solve it? We know that UTC refers to the *module* with the same
name. To get the *class* inside that module, use UTC.UTC - try again in
the interpreter. To create a new instance of that class, you can use
UTC.UTC(). To obtain the instance already created in the UTC module, use
UTC.utc

**OR**

Import those names explicitely:

py> from UTC import UTC

In this case the name UTC refers to the *class* inside the module.
In this particular example it may be confusing - both have the same name.
Another example from the standard library: the poplib module contains a
POP3 class, so after executing this line:

py> from poplib import POP3

the name POP3 refers to that class. The poplib module itself isn't
directly available.
Back to the UTC module, you could use:

py> from UTC import utc

and now utc refers to the *instance* already created inside the module.
This last form may be the most convenient in your case:

py> import datetime
py> from UTC import utc
py> print datetime.datetime(2008, 7, 14, 20, 30, 0, 0, utc)
2008-07-14 20:30:00+00:00

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Jul 17 2008)________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611

 

[M.-A. Lemburg]

If this is MySQLdb interface:

query = "INSERT INTO image VALUES(%s, %s, %s, '%s')"

... only %s is valid; all values have been converted to escaped/quoted
string before getting to the substitution.

Right. I forgot to replace the %d's with %s's. The line should read:

query = "INSERT INTO image VALUES(%s, %s, %s, %s)"

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Jul 17 2008)________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611

 

[Gabriel Genellina]

En Wed, 16 Jul 2008 15:00:50 -0300, Keith Hughitt

Thanks Gabriel!

That helps clear things up for me. The above method works very well. I
only have one remaining question:
How can I pass a datetime object to MySQL?'

So far, what I've been doing is building the query as a string, for
example:

query = "INSERT INTO image VALUES(%d, %d, %s, '%s')" % (id, meas,
date, 'jpg')
cursor.execute(query)

That's not a good idea, in general (among other problems: what if any text
contains a quote? ever heard of "sql injection"?). Use this form instead:

query = "INSERT INTO image VALUES(%s, %s, %s, %s)"
cursor.execute(query, (id, meas, date, 'jpg'))

Note that I used %s everywhere (it's just a placeholder, not a format) and
the execute method receives two arguments, the second being a tuple
containing the desired values.

 

[Lawrence D'Oliveiro]

Note that I used %s everywhere (it's just a placeholder, not a format) ...

From /usr/lib64/python2.5/site-packages/MySQLdb/cursors.py, lines 150-151:

if args is not None:
query = query % db.literal(args)

 

[M.-A. Lemburg]

<cue the beer commercial>

Unfortunately, in the case of MySQLdb... It is both -- maybe that
will change once MySQLdb gives up compatibility with MySQL 4.x by
incorporating usage of prepared statements in place of a Python %
interpolation.

It has to be %s as the adapter first converts to string, escapes,
and quotes the arguments; regardless of native datatype they are strings
when put into the SQL statement...

If you prefer a different parameter style, you could use mxODBC
and the MySQL ODBC drivers.

You'd then write:

query = "INSERT INTO image VALUES(?, ?, ?, ?)"

and avoid any confusion due to the parameter style looking a
lot like the Python string formatting markers (for obvious
reasons, since that's what the MySQLdb module uses internally).

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Jul 18 2008)________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611