Python datetime to int for db

[Christopher Nelson]

http://trac.edgewall.org/wiki/TracDev/DatabaseApi#GuidelinesforSQLStatements
says "store date and time information in seconds as int fields (before
0.12) or better, in microseconds and bigint fields (since 0.12)" but
I've looked at Python language and library documentation and Goggled
around some and not found a clear way to convert from the Python
datetime object in my code to the bigint I want to put in the db.
Team Calendar breaks that rule and I'm scratching my head to find
another date-aware plugin to spelunk the source of.

If I want to do:

d = datetime.now()
cursor.execute("INSERT INTO mytable VALUES", somefunc(d))

what is `somefunc()`

?

Chris
--
A: Top-posting.
Q: What is the most annoying thing in e-mail?

[Christian Boos]

Hello Christopher,

On 9/18/2012 2:21 PM, Christopher Nelson wrote:
> http://trac.edgewall.org/wiki/TracDev/DatabaseApi#GuidelinesforSQLStatements
> says "store date and time information in seconds as int fields (before
> 0.12) or better, in microseconds and bigint fields (since 0.12)" but
> I've looked at Python language and library documentation and Goggled
> around some and not found a clear way to convert from the Python
> datetime object in my code to the bigint I want to put in the db.
> Team Calendar breaks that rule and I'm scratching my head to find
> another date-aware plugin to spelunk the source of.
>
> If I want to do:
>
> d = datetime.now()
> cursor.execute("INSERT INTO mytable VALUES", somefunc(d))
>
> what is `somefunc()`
>

Well, I think you're looking for to_utimestamp:


http://www.edgewall.org/docs/branches-1.0-stable/html/api/trac_util_datefmt.html#trac.util.datefmt.to_utimestamp

-- Christian

[Christopher Nelson]

>> If I want to do:
>>
>> d = datetime.now()
>> cursor.execute("INSERT INTO mytable VALUES", somefunc(d))
>>
>> what is `somefunc()`
>>
>
> Well, I think you're looking for to_utimestamp:
>
> http://www.edgewall.org/docs/branches-1.0-stable/html/api/trac_util_datefmt.html#trac.util.datefmt.to_utimestamp

Perfect. Thanks.

[Christopher Nelson]

>> If I want to do:
>>
>> d = datetime.now()
>> cursor.execute("INSERT INTO mytable VALUES", somefunc(d))
>>
>> what is `somefunc()`
>>
>
> Well, I think you're looking for to_utimestamp:
>
> http://www.edgewall.org/docs/branches-1.0-stable/html/api/trac_util_datefmt.html#trac.util.datefmt.to_utimestamp

I'm seeing:

File "/usr/local/lib/python2.6/dist-packages/Trac-0.11.6-py2.6.egg/trac/util/datefmt.py",
line 69, in to_utimestamp
diff = dt - _epoc
TypeError: can't subtract offset-naive and offset-aware datetimes

No doubt I'm passing a bad argument but the document you pointed to
doesn't indicate if the argument should be offset-naive or
offset-aware.

[Christopher Nelson]

>> If I want to do:
>>
>> d = datetime.now()
>> cursor.execute("INSERT INTO mytable VALUES", somefunc(d))
>>
>> what is `somefunc()`
>
> Well, I think you're looking for to_utimestamp:
>
> http://www.edgewall.org/docs/branches-1.0-stable/html/api/trac_util_datefmt.html#trac.util.datefmt.to_utimestamp

I can't make that work. I have:

self.env.log.debug('utimestamp tests')
ds = '2012-02-01'
try:
self.env.log.debug('a. ds:%s' % ds)
d = datetime(*time.strptime(ds, '%Y-%m-%d')[0:7])
self.env.log.debug('b. d:%s' % d)
d = d.replace(hour=0, minute=0, second=0, microsecond=0)
self.env.log.debug('c. d:%s' % d)
d = to_datetime(d)
self.env.log.debug('d. d:%s' % d)
uts = to_utimestamp(d)
self.env.log.debug('1. ds:%s -> uts:%s' % (ds, uts))
except:
self.env.log.debug('My way failed')

d = parse_date(ds)
uts = to_utimestamp(d)
self.env.log.debug('2. ds:%s -> uts:%s' % (ds, uts))

and I see:

2012-09-18 13:48:00,442 Trac[tracpm] DEBUG: utimestamp tests
2012-09-18 13:48:00,442 Trac[tracpm] DEBUG: a. ds:2012-02-01
2012-09-18 13:48:00,445 Trac[tracpm] DEBUG: b. d:2012-02-01 00:00:00.000002
2012-09-18 13:48:00,445 Trac[tracpm] DEBUG: c. d:2012-02-01 00:00:00
2012-09-18 13:48:00,445 Trac[tracpm] DEBUG: d. d:2012-02-01 00:00:00
2012-09-18 13:48:00,446 Trac[tracpm] DEBUG: My way failed
2012-09-18 13:48:00,447 Trac[tracpm] DEBUG: 2. ds:2012-02-01 ->
uts:1328072400000000

In 0.11 parse_date() seems to assume ISO format which isn't really an
option for me. users can configure their own format to match their
data. So, how can I parse a date string into something that
to_utimestamp() will accept? (I'm more than willing to accept that my
parsing is awkward. It's evolved painfully and may be Just Wrong.
Any wisdom on cleaning it up is appreciated.)

Chris

[Christopher Nelson]

Now I have:

self.env.log.debug('utimestamp tests')
ds = '2012-02-01'
try:
self.env.log.debug('a. ds:%s' % ds)

d = datetime.strptime(ds, '%Y-%m-%d')

self.env.log.debug('b. d:%s' % d)
d = d.replace(hour=0, minute=0, second=0, microsecond=0)

d = d.replace(tzinfo=localtz)

self.env.log.debug('c. d:%s' % d)
d = to_datetime(d)
self.env.log.debug('d. d:%s' % d)
uts = to_utimestamp(d)
self.env.log.debug('1. ds:%s -> uts:%s' % (ds, uts))
except:
self.env.log.debug('My way failed')

which produces:

2012-09-18 14:49:09,988 Trac[tracpm] DEBUG: utimestamp tests
2012-09-18 14:49:09,988 Trac[tracpm] DEBUG: a. ds:2012-02-01
2012-09-18 14:49:09,990 Trac[tracpm] DEBUG: b. d:2012-02-01 00:00:00
2012-09-18 14:49:09,990 Trac[tracpm] DEBUG: c. d:2012-02-01 00:00:00-05:00
2012-09-18 14:49:09,990 Trac[tracpm] DEBUG: d. d:2012-02-01 00:00:00-05:00
2012-09-18 14:49:09,991 Trac[tracpm] DEBUG: 1. ds:2012-02-01 ->
uts:1328072400000000
2012-09-18 14:49:09,992 Trac[tracpm] DEBUG: 2. ds:2012-02-01 ->
uts:1328072400000000

But I'm not sure how portable that is beyond 0.11.

[Steffen Hoffmann]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 18.09.2012 19:52, Christopher Nelson wrote:
> In 0.11 parse_date() seems to assume ISO format which isn't really an
> option for me. users can configure their own format to match their
> data.

IIRC, Trac 0.12 does better, but sure, we even have 1.0 now.

If you need to stick to older Trac and if you're prepared to roll your
own code as (part of) a plugin, I recommend mxDateTime by eGenix.com [1]
to you. I happened to test it rather extensively while working on the
initial TracTicketsCustomTimeFields [2] implementation, that finally
made it into Trac after 1.0 in current development.

mxDateTime parser is really powerful and does even guess many typos
right, like semi-colon instead of colon for time stamps etc. But it
would be another external dependency, so it was not included in the
final changes for Trac by now.

Steffen Hoffmann


[1] http://www.egenix.com/products/python/mxBase/mxDateTime/doc/#DateTime
[2]
http://trac.edgewall.org/wiki/TracTicketsCustomTimeFields#Externalrelatedresources
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlBYxAcACgkQ31DJeiZFuHfrewCg3qrKa8ljtwq7Ox3PGKX0mKGS
rQgAoKr28Otsfv2SyTA7IybferSoDDR7
=6ooi
-----END PGP SIGNATURE-----

[Christian Boos]

You're right, this is not yet documented.

As an aside, when playing on the command line with the datefmt
utilities, I found some surprising differences between our FixedOffset
tzinfo class and the tzinfo classes from pytz: I expected the
"Europe/Paris" timezone from pytz to be the same as our "GMT +2:00"
(when DST is active that is, otherwise GMT +1), but they are not...

>>> from trac.util.datefmt import *
>>> paris = timezone("Europe/Paris")
>>> gmt02 = timezone("GMT +2:00")
>>> (paris, gmt02)
(<DstTzInfo 'Europe/Paris' PMT+0:09:00 STD>, <FixedOffset "GMT +2:00"
2:00:00>)

I have no idea why that DstTzInfo instance shows "PMT+0:09:00", but OK.

First we start by using "naive" datetime objects.

>>> from datetime import datetime
>>> d_naive = datetime.now()
>>> d_paris = to_datetime(d_naive, paris)
>>> d_gmt02 = to_datetime(d_naive, gmt02)

Now if we try to use to_utimestamp() on these datetime objects, we'll
get different values!

>>> to_utimestamp(d_paris)
1347999789289000L
>>> to_utimestamp(d_gmt02)
1347993129289000L

??

But even when comparing directly those datetime objects, we see they're
not the same:

>>> d_paris - d_gmt02
datetime.timedelta(0, 6660)

!? 111 minutes... At first I had no idea what this corresponded to.

The Python docs
(http://docs.python.org/library/datetime.html#datetime-objects) helped a
bit here: "Subtraction of a datetime from a datetime ... If both are
aware and have different tzinfo attributes, a-b acts as if a and b were
first converted to naive UTC datetimes first. The result is ..." (in our
example:)

>>> (d_paris.replace(tzinfo=None) - d_paris.utcoffset()) -
(d_gmt02.replace(tzinfo=None) - d_gmt02.utcoffset())
datetime.timedelta(0, 6660)

And this helps to pinpoint the cause:

>>> d_gmt02.utcoffset()
datetime.timedelta(0, 7200)

(correct, 2 hours)

>>> d_paris.utcoffset()
datetime.timedelta(0, 540)

Ahem, 9 minutes... (remember the PMT+0:09:00 above?)

Googling a bit reveals a similar "surprise" for someone else, a few
years ago:

http://marc.info/?l=zope3-dev&m=115384670611141&w=2

The answer was: "Read pytz/README.txt. You're not supposed to pass
tzinfo to datetime."

Ok, so it really look like we should find an alternative to the
t.replace(tzinfo=tzinfo) we do in to_datetime() when t is a "naive"
datetime, because if we specify there a timezone from pytz, we'll get
non-sensical datetime objects.

If we start with "aware" datetime objects (bootstrapping the tzinfo with
one of our own FixedOffset instance), it indeed works as expected.

>>> d_utc = d_naive.replace(tzinfo=utc)
>>> du_paris = to_datetime(d_utc, paris)
>>> du_gmt02 = to_datetime(d_utc, gmt02)

Then:

>>> du_paris - du_gmt02
datetime.timedelta(0)

Better!

Looks like in Trac we're mostly using "aware" datetime objects anyway,
like when retrieving dates from the database (from_utimestamp(usecs)) or
from the user (user_time(...)), or the current date (datetime.now(utc)),
so that this problem was not apparent. Still, the docs need to be
clarified about the "naive"/"aware" situation and to_datetime() should
be fixed.

-- Christian