[egenix-users] mx.DateTime: Truncating fractional seconds

[Wade Leftwich]

M.-A. Lemburg wrote:
> [put the list back on CC since I believe this is of interest
>  to others as well]
> 
> Steve Holden wrote:
> 
>>> I'm not sure what you mean. If it's the warnings that you get in
>>> Python 2.3 from the time module functions if you pass in the
>>> .tuple() value due to mxDateTime representing the seconds as
>>> float, then this problem will be gone in 2.1.0 final (I decided
>>> to make seconds in the .tuple() output an integer; the float
>>> value is still available through the .seconds attribute).
>>>
>>> If you're talking about the str() representation which includes
>>> fractions, then the easiest way to get rid off the fractions
>>> is use .strftime() which doesn't handle fractions of a second
>>> by nature :-)
>>
>>
>> I'm actually talking about using a DateTime object as a parameter for
>> inserting into an Access Date column, specifically in this case under
>> Python 2.2.x:
> 
> 
> Ah, ok. AFAIR, Access tells mxODBC to send the date as string
> and since mxDateTime instances generate seconds with fractions
> which Access doesn't support, you get the warning.
> 
> This simple trick should work:
> 
> def AccessDateTime(dt):
>     # cut away the seconds fraction part
>     return str(dt)[:-3]
> curs.execute(stmt, (AccessDateTime(now()),)
> 
>> $ python
>> Python 2.2.3 (#1, Jun 19 2003, 12:10:13)
>> [GCC 3.2 20020927 (prerelease)] on cygwin
>> Type "help", "copyright", "credits" or "license" for more information.
>>
>>>>> import mx.ODBC.Windows as db
>>>>> conn = db.connect("billing")
>>>>> curs = conn.cursor()
>>>>> from mx.DateTime import now, DateTime
>>>>> curs.execute("INSERT INTO Task (pjName, TaskName, Description, Rate,
>>
>>
>> Started)"
>> ...              " VALUES('gpcc', 'TEST', 'Delete me after this!',
>> 125.0, ?)",
>> ...              (now(), ))
>> Traceback (most recent call last):
>>   File "<stdin>", line 3, in ?
>> mx.ODBC.Windows.Warning: ('01S07', 13, '[Microsoft][ODBC Microsoft
>> Access Driver]Fractional truncation (null)', 5696)
>>
>>>>> n = now()
>>>>> curs.execute("INSERT INTO Task (pjName, TaskName, Description, Rate,
>>
>>
>> Started)"
>> ...              " VALUES('gpcc', 'TEST', 'Delete me after this!',
>> 125.0, ?)",
>> ...              (DateTime(n.year, n.month, n.day, n.hour, n.minute,
>> int(n.second)), ))
>> 1
>>
>> Unfortunately I'd already tried Wade's int() trick on the whole date,
>> without success in Access, I'm afraid, so it's interesting to know it
>> works with MS SQL Server:
>>
>>
>>>>> curs.execute("INSERT INTO Task (pjName, TaskName, Description, Rate,
>>
>>
>> Started)"
>> ...              " VALUES('gpcc', 'TEST', 'Delete me after this!',
>> 125.0, ?)",
>> ...             (int(n), ))
>> Traceback (most recent call last):
>>   File "<stdin>", line 3, in ?
>> mx.ODBC.Windows.DataError: ('22003', 34, '[Microsoft][ODBC Microsoft
>> Access Driver]Numeric value out of range (null)', 5696)
>>
>> Maybe the DateTime.__int__() method could be defined in some helpful way
>> here?
> 
> 
> int(datetime) returns the Unix ticks value for the datetime
> you pass in. It is not meant to be used in this context.
> The idea is that you can pass DateTime instances to other
> functions which expect a Unix ticks integer.
> 

In case there is still interest in this topic, I ran into the same 
situation this afternoon, and did it this way:

 >>> from mx import DateTime
 >>> def truncFractionalSeconds(dt):
... 	"""mxDateTime object => same with fractions of secs removed"""
... 	return DateTime.DateTimeFromTicks(int(dt))
...
 >>> now = DateTime.now()
 >>> now
<DateTime object for '2003-08-19 14:19:27.11' at 150b9e0>
 >>> truncFractionalSeconds(now)
<DateTime object for '2003-08-19 14:19:27.00' at 14f8be0>
 >>>

Seems to me like it's safer to pass your cursor a DateTime object than a 
string, which might lead to cross-platform problems.

-- Wade Leftwich
Ithaca, NY