[Tutor] SQL Datetimes

[Danny Yoo]

On Mon, 14 Feb 2005, Bill Kranec wrote:

> I'm using Kinterbasdb to access a Firebird database through Python, and
> when I retrieve a row with a datetime value, I get a tuple like:
>
>  >>> myCursor.execute( 'SELECT * FROM table' )
>  >>> for row in myCursor.fetchall():
>         print row
>
> (<DateTime object for '2004-08-09 00:00:00.00' at d41720>, 'value2',
> 'value3', 'value4', 100)
>
> I would like to get:
>
> ('8/9/2004', 'value2', 'value3', 'value4', 100)


Hi Bill,

Just out of curiosity, can you work with the datetime object itself?  It
sounds like you want it to come out as a string, but that's somewhat
"lossy" because we lose things like the 'time' portion of a datetime.

According to the documentation here:

    http://www.python.org/doc/lib/datetime-datetime.html

we can call methods on the datetime object to build something like the
date string you're looking for.  For example:

###
>>> import datetime
>>> now = datetime.datetime.today()
>>> now
datetime.datetime(2005, 2, 14, 23, 1, 21, 50304)
>>> def getDateString(d):
...     return "%s/%s/%s" % (d.year, d.month, d.day)
...
>>> getDateString(now)
'2005/2/14'
###

We can go the other way (from date string to datetime instance), but it'll
take a little bit of string manipulation.



> Does anyone know how to convert the DateTime object into an actual
> datetime, and vice versa? I'm pretty sure what I want to do is
> documented here (
> http://kinterbasdb.sourceforge.net/dist_docs/usage.html#adv_param_conv_dynamic_type_translation
> ), but I don't understand what is going on.

I'd have to read into it more deeply, but I think it might be overkill to
override the default type translators between Python and the database.
My opinion is that it's probably less work to write functions to go back
and forth between the date strings and the datetime objects.


If you have more questions, please feel free to ask.  Best of wishes to
you!