日時を使った検索がうまくいかない（回避案）
前回（http://d.hatena.ne.jp/stog/20100526/1274899622）の続き。
MongoDBで日時を使った検索をするとき、1970-01-01より前の日付がうまく扱えない件について、自分が使い方を間違っているのか、そういう仕様なのか未だに分かっていない。。
なので、以下のように日付をタイムスタンプとして扱えば良いのかも。
テストデータ
dummy.tsv
name type sex birthday おがわ human M 1980/11/03 たかはし human F 1974/02/20 たなか human M 2003/01/15 さとう human F 1960/10/05 ポチ dog F 2005/06/07 タロ dog M 1995/08/08 タマ cat F 2008/12/24 ミケ cat M 1998/12/25 John human M 1970/01/01 Michael human M 1940/08/09 Robert human M 1999/05/20 David human M 2009/12/05 James human M 1969/12/31 Mary human F 1985/04/10 Barbara human F 1955/10/15 Anne human F 2001/01/20 Maria human F 1945/09/25 Susan human F 1977/11/30
日付をタイムスタンプに変換してDBに保存する。
test_insert.py
#!/usr/bin/env python # -*- coding:utf-8 -*- import csv import datetime import calendar import pymongo conn = pymongo.Connection() db = conn["mytest"] coll = db["members2"] fh = open("./dummy.tsv", "r") contents = csv.DictReader(fh, delimiter='\t') for data in contents: year, month, day = [int(x) for x in data["birthday"].split('/')] dt = datetime.datetime(year, month, day) data["birthday"] = calendar.timegm(dt.timetuple()) coll.insert(data) fh.close() conn.disconnect() print "coll.count() =", coll.count()
DBから取得したタイムスタンプを日付に変換して出力する。
test_find.py
#!/usr/bin/env python # -*- coding:utf-8 -*- import csv import datetime import calendar import pymongo def timestamp2date(timestamp): return datetime.datetime.utcfromtimestamp(timestamp) def printdata(data): print "name: %(name)s,\ttype: %(type)s,\tsex: %(sex)s,\tbirthday: %(birthday)s" % data conn = pymongo.Connection() db = conn["mytest"] coll = db["members2"] # 検索条件なし print "find()" print "----------------------------------------" for data in coll.find(): data['birthday'] = timestamp2date(data['birthday']) printdata(data) print # 犬を検索 print "find({'type':'dog'})" print "----------------------------------------" for data in coll.find({'type':'dog'}): data['birthday'] = timestamp2date(data['birthday']) printdata(data) print # 人間ではなく、性別がオスであるものを検索 print "find({'type':{'$ne':'human'}, 'sex':'M'})" print "----------------------------------------" for data in coll.find({'type':{'$ne':'human'}, 'sex':'M'}): data['birthday'] = timestamp2date(data['birthday']) printdata(data) print # 誕生日が2000-01-01以降のものを検索したい print "find({'birthday': {'$gte': datetime.datetime(2000, 1, 1)}})" print "----------------------------------------" for data in coll.find({'birthday': {'$gte': calendar.timegm(datetime.datetime(2000, 1, 1).timetuple())}}): data['birthday'] = timestamp2date(data['birthday']) printdata(data) print # 誕生日でソートしたい print "find().sort('birthday')" print "----------------------------------------" for data in coll.find().sort('birthday'): data['birthday'] = timestamp2date(data['birthday']) printdata(data) print conn.disconnect()
以下が実行結果。
$ python test_insert.py
coll.count() = 18
$ python test_find.py
find()
----------------------------------------
name: おがわ, type: human, sex: M, birthday: 1980-11-03 00:00:00
name: たかはし, type: human, sex: F, birthday: 1974-02-20 00:00:00
name: たなか, type: human, sex: M, birthday: 2003-01-15 00:00:00
name: さとう, type: human, sex: F, birthday: 1960-10-05 00:00:00
name: ポチ, type: dog, sex: F, birthday: 2005-06-07 00:00:00
name: タロ, type: dog, sex: M, birthday: 1995-08-08 00:00:00
name: タマ, type: cat, sex: F, birthday: 2008-12-24 00:00:00
name: ミケ, type: cat, sex: M, birthday: 1998-12-25 00:00:00
name: John, type: human, sex: M, birthday: 1970-01-01 00:00:00
name: Michael, type: human, sex: M, birthday: 1940-08-09 00:00:00
name: Robert, type: human, sex: M, birthday: 1999-05-20 00:00:00
name: David, type: human, sex: M, birthday: 2009-12-05 00:00:00
name: James, type: human, sex: M, birthday: 1969-12-31 00:00:00
name: Mary, type: human, sex: F, birthday: 1985-04-10 00:00:00
name: Barbara, type: human, sex: F, birthday: 1955-10-15 00:00:00
name: Anne, type: human, sex: F, birthday: 2001-01-20 00:00:00
name: Maria, type: human, sex: F, birthday: 1945-09-25 00:00:00
name: Susan, type: human, sex: F, birthday: 1977-11-30 00:00:00
find({'type':'dog'})
----------------------------------------
name: ポチ, type: dog, sex: F, birthday: 2005-06-07 00:00:00
name: タロ, type: dog, sex: M, birthday: 1995-08-08 00:00:00
find({'type':{'$ne':'human'}, 'sex':'M'})
----------------------------------------
name: タロ, type: dog, sex: M, birthday: 1995-08-08 00:00:00
name: ミケ, type: cat, sex: M, birthday: 1998-12-25 00:00:00
find({'birthday': {'$gte': datetime.datetime(2000, 1, 1)}})
----------------------------------------
name: たなか, type: human, sex: M, birthday: 2003-01-15 00:00:00
name: ポチ, type: dog, sex: F, birthday: 2005-06-07 00:00:00
name: タマ, type: cat, sex: F, birthday: 2008-12-24 00:00:00
name: David, type: human, sex: M, birthday: 2009-12-05 00:00:00
name: Anne, type: human, sex: F, birthday: 2001-01-20 00:00:00
find().sort('birthday')
----------------------------------------
name: Michael, type: human, sex: M, birthday: 1940-08-09 00:00:00
name: Maria, type: human, sex: F, birthday: 1945-09-25 00:00:00
name: Barbara, type: human, sex: F, birthday: 1955-10-15 00:00:00
name: さとう, type: human, sex: F, birthday: 1960-10-05 00:00:00
name: James, type: human, sex: M, birthday: 1969-12-31 00:00:00
name: John, type: human, sex: M, birthday: 1970-01-01 00:00:00
name: たかはし, type: human, sex: F, birthday: 1974-02-20 00:00:00
name: Susan, type: human, sex: F, birthday: 1977-11-30 00:00:00
name: おがわ, type: human, sex: M, birthday: 1980-11-03 00:00:00
name: Mary, type: human, sex: F, birthday: 1985-04-10 00:00:00
name: タロ, type: dog, sex: M, birthday: 1995-08-08 00:00:00
name: ミケ, type: cat, sex: M, birthday: 1998-12-25 00:00:00
name: Robert, type: human, sex: M, birthday: 1999-05-20 00:00:00
name: Anne, type: human, sex: F, birthday: 2001-01-20 00:00:00
name: たなか, type: human, sex: M, birthday: 2003-01-15 00:00:00
name: ポチ, type: dog, sex: F, birthday: 2005-06-07 00:00:00
name: タマ, type: cat, sex: F, birthday: 2008-12-24 00:00:00
name: David, type: human, sex: M, birthday: 2009-12-05 00:00:00
とりあえず、うまくいったっぽい。
