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.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
<ke***********@gmail.comescribi�:

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.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.htmlmay 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)

--
Gabriel Genellina

[Sebastian \lunar\ Wiesner]

Gabriel Genellina <ga*******@yahoo.com.ar>:

En Fri, 11 Jul 2008 15:42:37 -0300, Keith Hughitt
<ke***********@gmail.comescribi�:

>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.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.htmlmay 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>

--
Freedom is always the freedom of dissenters.
(Rosa Luxemburg)

[Keith Hughitt]

On Jul 12, 12:52Â*am, "Gabriel Genellina" <[e-mail]>
wrote:

En Fri, 11 Jul 2008 15:42:37 -0300, Keith Hughitt Â*
<[e-mail]�:

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.htmlmay 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)

--
Gabriel Genellina

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.

>>import datetime, UTC
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

>>utc

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

>>utc = UTC()

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

>>>

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

Any ideas?

Thanks,
Keith

[Gabriel Genellina]

En Mon, 14 Jul 2008 12:06:30 -0300, Keith Hughitt
<ke***********@gmail.comescribió:

On Jul 12, 12:52*am, "Gabriel Genellina" <[e-mail]>
wrote:

>En Fri, 11 Jul 2008 15:42:37 -0300, Keith Hughitt *
<[e-mail]ó:

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.htmlmay 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 ---
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:

pyfrom 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:

pyfrom 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:

pyfrom 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:

pyimport datetime
pyfrom UTC import utc
pyprint datetime.datetime(2008, 7, 14, 20, 30, 0, 0, utc)
2008-07-14 20:30:00+00:00

--
Gabriel Genellina

[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
On Jul 15, 12:04*am, "Gabriel Genellina" <[e-mail]>
wrote:

En Mon, 14 Jul 2008 12:06:30 -0300,KeithHughitt *
<[e-mail]ó:

On Jul 12, 12:52*am, "Gabriel Genellina" <[e-mail]>
wrote:

En Fri, 11 Jul 2008 15:42:37 -0300,KeithHughitt *
<[e-mail]ó:

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.htmlmay 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:

pyfrom 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:

pyfrom 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:

pyfrom 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:

pyimport datetime
pyfrom UTC import utc
pyprint datetime.datetime(2008, 7, 14, 20, 30, 0, 0, utc)
2008-07-14 20:30:00+00:00

--
Gabriel Genellina

[Lawrence D'Oliveiro]

In message
<46**********************************@2g2000hsn.go oglegroups.com>, Keith
Hughitt wrote:

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

>>import calendar
calendar.timegm((2008, 7, 11, 0, 0, 0, 0, 0, -1))

1215734400

ldo@theonTZ=NZ date -d "00:00:00 01-Jan-1970Z [phone] 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>,
<http://docs.python.org/lib/module-calendar.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]

On 2008-07-16 20:00, Keith Hughitt wrote:

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
On Jul 15, 12:04 am, "Gabriel Genellina" <[e-mail]>
wrote:

>En Mon, 14 Jul 2008 12:06:30 -0300,KeithHughitt
<[e-mail]ó:

>>On Jul 12, 12:52 am, "Gabriel Genellina" <[e-mail]>
wrote:
En Fri, 11 Jul 2008 15:42:37 -0300,KeithHughitt
<[e-mail]ó:
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.htmlmay 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:

pyfrom 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:

pyfrom 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:

pyfrom 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:

pyimport datetime
pyfrom UTC import utc
pyprint datetime.datetime(2008, 7, 14, 20, 30, 0, 0, utc)
2008-07-14 20:30:00+00:00

--
Gabriel Genellina

--
http://mail.python.org/mailman/listinfo/python-list

--
Marc-Andre Lemburg
eGenix.com

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

>>Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

__________________________________________________ ______________________

:::: 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]

On 2008-07-17 22:43, Dennis Lee Bieber wrote:

On Thu, 17 Jul 2008 19:55:44 +0200, "M.-A. Lemburg" <ma*@egenix.com>
declaimed the following in comp.lang.python:

>Use binding parameters and it should work:

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

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)

>>Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

__________________________________________________ ______________________

:::: 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
<ke***********@gmail.comescribi�:

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.

--
Gabriel Genellina

[Lawrence D'Oliveiro]

In message <ma************************************@python.org >, Gabriel
Genellina wrote:

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]

On 2008-07-18 05:28, Dennis Lee Bieber wrote:

On Thu, 17 Jul 2008 20:26:11 -0300, "Gabriel Genellina"
<ga*******@yahoo.com.ardeclaimed the following in comp.lang.python:

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

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

>>Python/Zope Consulting and Support ... http://www.egenix.com/
mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

__________________________________________________ ______________________

:::: 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