博客访问： 412306
博文数量： 53
博客积分： 757
博客等级： 上士
技术积分： 1888
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： NOSQL
& & &&最近一些项目由于历史原因，字段类型存储混乱，比如一个collection里modifedDate列，有的存的是ISODate类型，有的是NumberLong时间戳，这在Mongodb中是严重不推荐的，需要转换成统一的。Mongodb并不提供Alter table这样的语句或者工具，只能写程序转。
& & &&Mongodb的客户端是js写的，其实用js写脚本就可以实现。
& & &&以上面的例子来说，要将ISODate类型全都转换为NumberLong时间戳：
& & &&1、查询多少记录是ISODate类型
& & & ①、$type
db.table_name.find({modifedDate:{$type:9}}).count()
& & & 其中的9是Mongodb定义的BSON Date类型对应的数字。下面这张表是BSON TYPE及他们对应的数字。
& & & & & & & & & &&
&②、forEach函数和instanceof
db.table_name.find().forEach(function(x){if(x.modifedDate instanceof Date){count++}});
print(count);
& & &&2、修改类型
& & &&使用forEach函数
db.table_name.find({modifedDate:{$type:9}}).forEach(function(x){x.modifiedDate=NumberLong(x.modifiedDate.getTime()/1000);db.table_name.save(x)})
阅读(18051) | 评论(0) | 转发(1) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。mongo中的日期类型，使用ISO格式，例如：ISODate("T07:58:51.718Z")。下面来测试一下：&
&C:\&mongo 127.0.0.1:2222/test
MongoDB shell version: 1.8.3
connecting to: 127.0.0.1:2222/test
type "help" for help
neu:PRIMARY& db.t2.insert({mydate:ISODate("T07:58:51.718Z")})
neu:PRIMARY& db.t2.find()
{ "_id" : ObjectId("50937ded06dae"), "mydate" : ISODate("T07:58:51.718Z") }
日期字符串也支持多种格式
neu:PRIMARY& db.t2.insert({mydate:ISODate(" 07:58:51")})
neu:PRIMARY& db.t2.insert({mydate:ISODate(":58:51")})
neu:PRIMARY& db.t2.insert({mydate:ISODate("")})&
neu:PRIMARY& db.t2.find()
{ "_id" : ObjectId("50937ded06dae"), "mydate" : ISODate("T07:58:51.718Z") }
{ "_id" : ObjectId("bb20f"), "mydate" : ISODate("T07:58:51Z") }
{ "_id" : ObjectId("bb20f"), "mydate" : ISODate("T07:58:51Z") }
{ "_id" : ObjectId("bb20f"), "mydate" : ISODate("T00:00:00Z") }
我们比较一下mongo中的当前时间与系统当前时间：
neu:PRIMARY& new Date()
ISODate("T08:13:10.250Z")
neu:PRIMARY& ^Z
输入新日期: (年月日)
当前时间: 16:13:14.95
输入新时间:
我们看到，mongo中的时间似乎与系统时间相差了8个小时，why？
这是因为mongo中的date类型以UTC（Coordinated Universal Time）存储，就等于GMT（格林尼治标准时）时间。而系统时间使用的是GMT+0800时间，两者正好相差8个小时。
在mongodb与oracle或sqlserver相互同步数据时，需要注意这一点！
如果用python直接读取mongo中的日期，就会发生错误：
&&& import pymongo
&&& conn=pymongo.Connection('127.0.0.1',2222)
&&& db=conn.test
&&& for row in db.t2.find():
... & & print row['mydate']
07:58:51.718000
在python中，与之对应的日期是 datetime.datetime.utcnow()，而不是now()，两者正好差8个小时：
&&& import datetime&
&&&& datetime.datetime.utcnow()
datetime.datetime(,&8, 22, 52, 953000)
&&& datetime.datetime.now()
datetime.datetime(,&16, 22, 58, 218000)&
关于二者的转换，可以参考这个链接：&
阅读(...) 评论()python-isodate-0.4.9-10.1.4.noarch RPM
Name: python-isodate
Distribution:
Version: 0.4.9
Release: 10.1.4
Build date: Wed Oct
8 19:47:41 2014
Build host: cloud129
Size: 203907
Source RPM:
Packager: http://bugs.opensuse.org
Summary: An ISO 8601 Date/Time/Duration Parser and Formatter
This module implements ISO 8601 date, time and duration parsing.
The implementation follows ISO standard, and implements only
date/time representations mentioned in the standard. If something is not
mentioned there, then it is treated as non existent, and not as an allowed
&= 3.0.4-1
&= 4.4.6-1
BSD-3-Clause
* Thu Oct 24 2013
- Require python-setuptools instead of distribute (upstreams merged)
* Wed Apr 17 2013
- Re-add python-2to3.
It is needed for OpenSUSE &= 12.2
* Tue Apr 16 2013 p.
- Remove BuildRequires: python-2to3, useless since included in main
python devel package
- Add Requires: python3; fix build for OpenSUSE &= 12.2
* Sun Jan 13 2013 p.
- Initial python3 support
* Fri Nov 23 2012 saschpe@suse.de
- Update to version 0.4.9:
+ support pickling FixedOffset instances
+ make sure parsed fractional seconds are in microseconds
+ add leading zeros when formattig microseconds (Jarom Loveridge)
- Spec file cleanup
* Fri May 18 2012 jfunk@funktronics.ca
- Initial release
/usr/lib/python2.7/site-packages/isodate
/usr/lib/python2.7/site-packages/isodate-0.4.9-py2.7.egg-info
/usr/lib/python2.7/site-packages/isodate-0.4.9-py2.7.egg-info/PKG-INFO
/usr/lib/python2.7/site-packages/isodate-0.4.9-py2.7.egg-info/SOURCES.txt
/usr/lib/python2.7/site-packages/isodate-0.4.9-py2.7.egg-info/dependency_links.txt
/usr/lib/python2.7/site-packages/isodate-0.4.9-py2.7.egg-info/top_level.txt
/usr/lib/python2.7/site-packages/isodate/__init__.py
/usr/lib/python2.7/site-packages/isodate/__init__.pyc
/usr/lib/python2.7/site-packages/isodate/duration.py
/usr/lib/python2.7/site-packages/isodate/duration.pyc
/usr/lib/python2.7/site-packages/isodate/isodates.py
/usr/lib/python2.7/site-packages/isodate/isodates.pyc
/usr/lib/python2.7/site-packages/isodate/isodatetime.py
/usr/lib/python2.7/site-packages/isodate/isodatetime.pyc
/usr/lib/python2.7/site-packages/isodate/isoduration.py
/usr/lib/python2.7/site-packages/isodate/isoduration.pyc
/usr/lib/python2.7/site-packages/isodate/isoerror.py
/usr/lib/python2.7/site-packages/isodate/isoerror.pyc
/usr/lib/python2.7/site-packages/isodate/isostrf.py
/usr/lib/python2.7/site-packages/isodate/isostrf.pyc
/usr/lib/python2.7/site-packages/isodate/isotime.py
/usr/lib/python2.7/site-packages/isodate/isotime.pyc
/usr/lib/python2.7/site-packages/isodate/isotzinfo.py
/usr/lib/python2.7/site-packages/isodate/isotzinfo.pyc
/usr/lib/python2.7/site-packages/isodate/tests
/usr/lib/python2.7/site-packages/isodate/tests/__init__.py
/usr/lib/python2.7/site-packages/isodate/tests/__init__.pyc
/usr/lib/python2.7/site-packages/isodate/tests/test_date.py
/usr/lib/python2.7/site-packages/isodate/tests/test_date.pyc
/usr/lib/python2.7/site-packages/isodate/tests/test_datetime.py
/usr/lib/python2.7/site-packages/isodate/tests/test_datetime.pyc
/usr/lib/python2.7/site-packages/isodate/tests/test_duration.py
/usr/lib/python2.7/site-packages/isodate/tests/test_duration.pyc
/usr/lib/python2.7/site-packages/isodate/tests/test_pickle.py
/usr/lib/python2.7/site-packages/isodate/tests/test_pickle.pyc
/usr/lib/python2.7/site-packages/isodate/tests/test_strf.py
/usr/lib/python2.7/site-packages/isodate/tests/test_strf.pyc
/usr/lib/python2.7/site-packages/isodate/tests/test_time.py
/usr/lib/python2.7/site-packages/isodate/tests/test_time.pyc
/usr/lib/python2.7/site-packages/isodate/tzinfo.py
/usr/lib/python2.7/site-packages/isodate/tzinfo.pyc
/usr/share/doc/packages/python-isodate
/usr/share/doc/packages/python-isodate/CHANGES.txt
/usr/share/doc/packages/python-isodate/README.txt
/usr/share/doc/packages/python-isodate/TODO.txt
Generated by
, Sun Aug 14 09:40:43 2016Notably missing from this page are the capabilities of
which was introduced in 2.3 and is very powerful. This page gives some basics of working with time in Python. More detail can be found in
There are several ways to work with time:
seconds since the "Epoch"
time.time()
time.gmtime()
time.ctime()
The Epoch is January 1st, 1970, midnight, on UNIX systems. On other systems, look at the results of time.gmtime(0) to discover the date of the Epoch. Measuring time in seconds since the Epoch is convenient for storage and comparing dates, because you only have a single number to consider. The tuple contains several values, arranged the following way: year, month 1-12, day 1-31, hour 0-23, minutes 0-59, seconds 0-61, day 0-6 (Mon-Sun), day 1-366, and DST -1,0,+1. "DST" means "daylight savings time." For more information, see
Using a tuple is convenient, because you can access particular numbers by index, and because you can easily compare dates. Remember that Python compares tuple data from front to back. The data is indexed so that comparing tuple times is intuitive. The string format reads something like "Mon Feb 16 16:04:25 2004". You can't really compare these usefully, but they're what you need to display things to the user.
Converting Between Formats
for image details)
mock ISO 8601
You can construct ISO 8601 with the following:
document.write(' iso_time = time.strftime("%Y-%m-%dT%H:%M:%S", tuple_time)
You can revert the construction with the following:
document.write(' tuple_time = time.strptime("T00:44:35", "%Y-%m-%dT%H:%M:%S")
The problem with this, though, is that if the string isn't formatted exactly like that, it won't be read. For instance, :44:35 is valid ISO8601, as is
for that matter, but neither will be read by the code above. To account for both common inputs, you could use:
document.write(' tuple_time = time.strptime(iso_time.replace("-", ""), "%Y%m%dT%H:%M:%S")
I seem to require receiving both while working with
produced by a mix of both Python and Perl code. The
specification is not very particular about the ISO8601 variant used for it's DateTime. Note that this method does not handle time zones, so trying to parse T12:34:56-0700 or T12:34:56Z will fail.
This is a fundamental limitation of time.strptime for which there is no easy workaround.
Parsing ISO 8601 with xml.utils.iso8601
If you have xml.utils.iso8601, you can do the following:
document.write(' import xml.utils.iso8601
import time
print xml.utils.iso8601.tostring(time.time())
print xml.utils.iso8601.parse("T21:39:00-08:00")
print xml.utils.iso8601.ctime(0)
The module isn' If you omit the dashes, (perfectly valid ISO8601,) you'll get a ValueError exception.
However, unlike time.strptime, xml.utils.iso8601.parse will handle the timezone specification correctly. xml.utils is distributed in python-xml in debian and ubuntu, but appears to be unmaintained.
Parsing ISO 8601 with pyiso8601
provides a standalone ISO8601 parser.
Although this isn't advertised as ISO8601 support,
seems to deal with the format correctly (for example, it has time zone support). import dateutil.parser
dateutil.parser.parse('T18:39:22Z')
Parsing RFC 3339 with the rfc3339 module
is the standard datetime format for most internet and web standards. It is a stricter ISO8601 profile, restricted to datetimes of second resolution or more precise. It requires explicit timezones as offsets from UTC.
() is an RFC 3339 parser. It also implements fixed-offset timezones and a formatter. It lacks support for leap seconds, as does the standard library's datetime module.
ISO8601 and Python in general
All in all, reading ISO8601 time values is non-trivial. For example, it is possible that someone can specify a full day, or a full week even, in ISO format. You can also specify days by number, if I recall correctly. Generally, programs conceed to accept only a limited subset of ISO8601. That subset tends to be "yyyy-mm-ddThh:mm:ss" and then either ".mmmmmm" sub-seconds or "+hh:00"/"-hh:00" for time zone.
(nothing yet)
This page is meant just to give you the basics. You may also want to read:
("GMT") Official nomencalture and description for 8601:
mxDateTime python extension module. provides many interfaces for handling dates and times, including ISO 8601 handling:
Contributors
Discussion