日時を使った検索がうまくいかない
MongoDBで検索するときの条件として日時を使ってみたのだが、意図した結果が得られない。
前回（http://d.hatena.ne.jp/stog/20100525/1274890034）作ったデータベースに対して以下のように検索してみる。
mongodb_find_date.py
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymongo import datetime conn = pymongo.Connection() db = conn["mytest"] coll = db["members"] # 誕生日が2000-01-01以降のものを検索したい print "find({'birthday': {'$gte': datetime.datetime(2000, 1, 1)}})" print "----------------------------------------" for data in coll.find({'birthday': {'$gte': datetime.datetime(2000, 1, 1)}}): print "name: %(name)s,\ttype: %(type)s,\tsex: %(sex)s,\tbirthday: %(birthday)s" % data print # 誕生日でソートしたい print "find().sort('birthday')" print "----------------------------------------" for data in coll.find().sort('birthday'): print "name: %(name)s,\ttype: %(type)s,\tsex: %(sex)s,\tbirthday: %(birthday)s" % data print conn.disconnect()
以下が実行結果。意図した結果が得られていない。
1970-01-01よりも前の日付の比較がおかしい気がする。
$ python mongodb_find_date.py
find({'birthday': {'$gte': datetime.datetime(2000, 1, 1)}})
----------------------------------------
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: cat, sex: F, birthday: 2008-12-24 00:00:00
name: Michael, type: human, sex: M, birthday: 1940-08-09 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: 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
find().sort('birthday')
----------------------------------------
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
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
mongo対話シェルでも試したが同じく意図した結果にならない。
$ mongo mytest
MongoDB shell version: 1.4.2
url: mytest
connecting to: mytest
type "help" for help
> var d = new Date(2000, 0, 1)
> print(d)
Sat Jan 01 2000 00:00:00 GMT+0900 (JST)
>
>
> db.members.find({birthday:{$gte:d}})
{ "_id" : ObjectId("4bfd44d0b4742d50ec000002"), "birthday" : "Wed Jan 15 2003 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "たなか", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000003"), "birthday" : "Tue Jan -2147483647 584556011 23:25:52 GMT+0900 (JST)", "type" : "human", "name" : "さとう", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000004"), "birthday" : "Tue Jun 07 2005 09:00:00 GMT+0900 (JST)", "type" : "dog", "name" : "ポチ", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000006"), "birthday" : "Wed Dec 24 2008 09:00:00 GMT+0900 (JST)", "type" : "cat", "name" : "タマ", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000009"), "birthday" : "Tue Jan -2147483647 584555990 23:25:48 GMT+0900 (JST)", "type" : "human", "name" : "Michael", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec00000b"), "birthday" : "Sat Dec 05 2009 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "David", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec00000c"), "birthday" : "Tue Jan -2147483647 584556020 23:25:48 GMT+0900 (JST)", "type" : "human", "name" : "James", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d1b4742d50ec00000e"), "birthday" : "Tue Jan -2147483647 584556006 23:25:52 GMT+0900 (JST)", "type" : "human", "name" : "Barbara", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d1b4742d50ec00000f"), "birthday" : "Sat Jan 20 2001 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "Anne", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d1b4742d50ec000010"), "birthday" : "Tue Jan -2147483647 584555995 23:25:52 GMT+0900 (JST)", "type" : "human", "name" : "Maria", "sex" : "F" }
>
>
> db.members.find().sort({birthday:1})
{ "_id" : ObjectId("4bfd44d0b4742d50ec000008"), "birthday" : "Thu Jan 01 1970 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "John", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000001"), "birthday" : "Wed Feb 20 1974 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "たかはし", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d1b4742d50ec000011"), "birthday" : "Wed Nov 30 1977 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "Susan", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000000"), "birthday" : "Mon Nov 03 1980 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "おがわ", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d1b4742d50ec00000d"), "birthday" : "Wed Apr 10 1985 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "Mary", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000005"), "birthday" : "Tue Aug 08 1995 09:00:00 GMT+0900 (JST)", "type" : "dog", "name" : "タロ", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000007"), "birthday" : "Fri Dec 25 1998 09:00:00 GMT+0900 (JST)", "type" : "cat", "name" : "ミケ", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec00000a"), "birthday" : "Thu May 20 1999 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "Robert", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d1b4742d50ec00000f"), "birthday" : "Sat Jan 20 2001 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "Anne", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000002"), "birthday" : "Wed Jan 15 2003 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "たなか", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000004"), "birthday" : "Tue Jun 07 2005 09:00:00 GMT+0900 (JST)", "type" : "dog", "name" : "ポチ", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000006"), "birthday" : "Wed Dec 24 2008 09:00:00 GMT+0900 (JST)", "type" : "cat", "name" : "タマ", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec00000b"), "birthday" : "Sat Dec 05 2009 09:00:00 GMT+0900 (JST)", "type" : "human", "name" : "David", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000009"), "birthday" : "Tue Jan -2147483647 584555990 23:25:48 GMT+0900 (JST)", "type" : "human", "name" : "Michael", "sex" : "M" }
{ "_id" : ObjectId("4bfd44d1b4742d50ec000010"), "birthday" : "Tue Jan -2147483647 584555995 23:25:52 GMT+0900 (JST)", "type" : "human", "name" : "Maria", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d1b4742d50ec00000e"), "birthday" : "Tue Jan -2147483647 584556006 23:25:52 GMT+0900 (JST)", "type" : "human", "name" : "Barbara", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec000003"), "birthday" : "Tue Jan -2147483647 584556011 23:25:52 GMT+0900 (JST)", "type" : "human", "name" : "さとう", "sex" : "F" }
{ "_id" : ObjectId("4bfd44d0b4742d50ec00000c"), "birthday" : "Tue Jan -2147483647 584556020 23:25:48 GMT+0900 (JST)", "type" : "human", "name" : "James", "sex" : "M" }
>
うーむ。。
自分がinsertやfindの方法を何か間違えているのか、それとも現状のMongoDBでは1970-01-01より古い日付の取扱いに難があるのか分からない。
