β

精通 Oracle+Python,第 2 部分:处理时间和日期

CzmMiao的博客生活 129 阅读

从 Python 2.4 版开始,cx_Oracle 自身可以处理 DATE 和 TIMESTAMP 数据类型,将这些列的值映射到 Python 的 datetime 模块的 datetime 对象中。因为 datetime 对象支持原位的运算操作,这可以带来某些优势。内置的时区支持和若干专用模块使 Python 成为一台实时机器。由于有了 cx_Oracle 的映射机制,Python 和 Oracle 间的日期/时间数据类型转换对开发人员是完全透明的。

Python 开发人员可能一开始会觉得 Oracle 的日期运算有点奇怪,但只需几点提示,该算法就会变得清楚且合理。本系列的这部分内容将帮助您从 Oracle 和 Python 两个角度来深入理解日期运算。二者均对日期/时间数据类型的处理提供丰富的支持,因此选择哪个由编程人员决定。如果您倾向于将应用程序逻辑放在数据库中, 或者喜欢将日期/时间操作封装在应用程序自身内部,Oracle 和 Python 的无缝集成会为您带来最大的灵活性,同时编程工作量却很少。

Oracle

Oracle 的特色在于为时区和日期运算提供顶级的支持。用于处理时间和日期的基本的 Oracle 数据类型包括:

  • DATE — 日期和时间信息,包括世纪、年、月、日、小时、分和秒。这种类型的列支持的值范围在公元前 4712 年 1 月 1 日到公元 9999 年 12 月 31 日之间。
  • TIMESTAMP — DATE 数据类型的粒度精确到秒。TIMESTAMP 字段包含 DATE 中的全部信息,另外还包括指定精度的秒的小数(最多为 9 位)。默认精度为 6 位。
  • TIMESTAMP WITH TIME ZONE — 除 TIMESTAMP 列中包含的信息外,此变体还包括时区偏移量,它是当地时间和 UTC(全球统一时间)之间的差值。精度属性与上面相同。
  • TIMESTAMP WITH LOCAL TIME ZONE — 与 TIMESTAMP WITH TIME ZONE 相对,此类型的值中不包含时区偏移量,而是由用户的当地会话时区确定该值。

日期时间由许多字段组成,其数量由数据类型的粒度和变体决定。可以使用 EXTRACT 语句通过 SQL 查询将这些字段提取出来。要了解有关数据类型中的可用字段和时间间隔的详细信息,请参考 Oracle 数据库 SQL 语言参考 数据类型部分 。我们来了解一下工作原理:

  
SQL> SELECT EXTRACT(YEAR FROM hire_date) FROM employees ORDER BY 1;

EXTRACT(YEARFROMHIRE_DATE)
----------------------------------------------------


                         ?

 rows selected.


利用此方法和 Oracle 的日期运算,您还可以获得两个日期之间的时间间隔:

SQL> SELECT hire_date, SYSDATE, EXTRACT(YEAR FROM (SYSDATE-hire_date) YEAR TO MONTH) "Years" 
  FROM employees WHERE ROWNUM <= 5;
HIRE_DATE          SYSDATE                 Years
------------------          ------------------               ----------
-JUN-87          23-FEB-07                  19
-SEP-89          23-FEB-07                  17
-JAN-93          23-FEB-07                  14
-JAN-90          23-FEB-07                  17
-MAY-91          23-FEB-07                  15
 rows selected.


日期操作涉及的另一数据类型为 INTERVAL,它表示一段时间。在编写本文时,Python 不支持将 INTERVAL 数据类型作为查询的一部分返回。唯一的方法是使用 EXTRACT 从时间间隔中提取出所需的信息。尽管如此,包含返回 TIMESTAMP 类型的时间间隔的查询仍然运转良好。

INTERVAL 类型有两个变体:

  • INTERVAL YEAR TO MONTH — 存储年和月的数量信息。年的精度可以手动指定。默认值是 (INTERVAL YEAR(2) TO MONTH)。
  • Error — 这里提到的除 Warning 外的所有异常的基类。
  • INTERVAL DAY TO SECOND — 在要求更高的精度时,此类型将天、小时、分和秒的信息存储为一段时间。天和秒的精度都可以显式指定,范围为 0 到 9。默认值是 INTERVAL DAY(2) TO SECOND(6)。


SYSDATE+1 等于明天

现 在看一下 Oracle 如何解决日期运算。在处理 datetime 列时,Oracle 认为 1 是指 1 天。这一方法确实非常直观。如果您想使用更小的单位,需要使用除法:1 分钟是 1/1440,这是因为 1 天有 60*24 分钟;1 小时是 1/24;1 秒钟是 1/86400,依此类推。

要查询从现在起的 15 分钟,使用 SELECT SYSDATE+15/1440 FROM dual。

格式化日期

Oracle 自身将日期显示为字符串,如上面的示例所示。格式化取决于从环境中继承的或显式设置的参数。要查看您数据库中的格式化参数,使用此查询:

SQL> SELECT * FROM v$nls_parameters WHERE REGEXP_LIKE(parameter, 'NLS_(DATE|TIME).*');

PARAMETER                      VALUE                             
------------------------------               -------------------------------------                           

NLS_DATE_FORMAT                RR/MM/DD                             
NLS_DATE_LANGUAGE              POLISH
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           RR/MM/DD HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        RR/MM/DD HH24:MI:SSXFF TZR
 rows selected.
                            

开发人员可以使用一组 Oracle 函数(TO_DATE、TO_TIMESTAMP、TO_TIMESTAMP_TZ、TO_YMINTERVAL、TO_DSINTERVAL)将字符值 转换为日期时间。TO_CHAR 函数用于反方向的转换。注意这些转换对于 Oracle 和 Python 间的转换通常不是必要的,这是因为我们处理的类型在两个方向都是可转换的。尽管如此,Oracle 自身在格式化日期时间上仍然允许极大的灵活性:

SQL> SELECT TO_CHAR(TO_DATE('04-2007-07', 'DD-YYYY-MM'), 'DD/MM/YYYY') FROM dual;
TO_CHAR(TO_DATE('04-2007-07','DD-YYYY-MM'),'DD/MM/YYYY')
---------- 
/07/2007 

获取当前的时间和时区信息同样容易。引入了两个新的格式化模型 TZH 和 TZM:

     
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI TZH:TZM') FROM dual;
TO_CHAR(SYSTIMESTAMP,'HH24:MI TZH:TZM')
------------ 
:24 +01:00 

要获得可用格式化模型的完整列表,请参考 Oracle 数据库 SQL 语言参考 格式模型 部分,该部分还有大量的用法示例。在很多情况下,您可能会觉得为当前会话设置永久的格式模型非常有用。可使用 ALTER SESSION 语句进行此设置:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> SELECT SYSDATE FROM dual;
SYSDATE
--------------
-FEB-07
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> SELECT SYSDATE FROM dual;
SYSDATE
-------------------
-02-23 17:50:15

Oracle 数据库全球化支持指南 中详细介绍了国家语言支持 (NLS) 参数的设置过程。 Oracle 数据库 SQL 语言参考 包含与此主题相关的更多信息。尤其要查阅 日期时间/时间间隔运算 日期时间函数 这两部分,以了解日期运算和内置的日期函数的更多信息。


Python

Python 允许您在处理时间和日期时在低级和高级接口间进行自由选择。为了充分利用 Python 的标准库,我们将重点介绍 datetime 模块,它同时也是日期/时间运算的基础。该模块有 5 个核心类型:date、time、datetime、timedelta 和 tzinfo。

>>> import datetime
>>> d = datetime.datetime.now()
>>> print d
-03-03 16:48:27.734000
>>> print type(d)
<type 'datetime.datetime'>
>>> print d.hour, d.minute, d.second
(16, 48, 27)
                            

如上所述,datetime 对象精确到微秒,所公开的一组属性与天、小时、秒等相对应。在 Python 中,了解某对象所拥有的属性和方法的最快途径是使用内置的 dir() 函数。关于 Python 标准库的介绍也非常丰富,因此您可以随时使用 help() 函数来了解该对象的简要说明 — 大多数情况下这足以让您立即入门。

>>> dir(datetime.datetime)
['__add__', '__class__', '__delattr__', '__doc__', '__eq__', 
'__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', 
'__le__', '__lt__', '__ne__', '__new__', '__radd__', '__reduce__',
 '__reduce_ex__', '__repr__', '__rsub__', '__setattr__', '__str__', 
 '__sub__', 'astimezone', 'combine', 'ctime', 'date', 'day', 'dst', 
 'fromordinal', 'fromtimestamp', 'hour', 'isocalendar', 'isoformat', 
 'isoweekday', 'max', 'microsecond', 'min', 'minute', 'month', 'now',
  'replace', 'resolution', 'second', 'strftime', 'strptime', 'time',
   'timetuple', 'timetz', 'today', 'toordinal', 'tzinfo', 'tzname',
    'utcfromtimestamp', 'utcnow', 'utcoffset', 'utctimetuple', 
        'weekday', 'year']
>>> help(datetime.datetime.weekday)
Help on method_descriptor:

weekday(...)
    Return the day of the week represented by the date.
    Monday == 0 ... Sunday == 6
       

当只需要 datetime 的一个组件(date 或 time)时,您可以使用 datetime.datetime 对象的 date() 或 time() 方法来分别返回 datetime.date 或 datetime.time 对象。


Python 的日期运算

不 需要手动计算日期之间的差值,因为 datetime 模块已经通过 timedelta 对象支持这样的运算。Timedelta 表示持续时间,它在内部存储天、秒和微秒的数量。timedelta 属性还提供毫秒、分、小时和周等信息。此内容是根据内部表示计算出的。支持的取值范围是:-999999999 <= 天数 <= 999999999、0 <= 秒数 < 86400、0 <= 微秒数 < 1000000。注意 timedelta 对象由所需数量的字段进行表示。因此 timedelta(hours=1) 与 timedelta(0, 3600) 的输出相同,即 0 天和 3600 秒。此处无毫秒,因为表示 1 小时不需要使用毫秒。


下面给出 Python 中最常用和支持的 datetime/timedelta 对象运算操作。

对象类型

运算

示例和相应结果

datetime.timedelta

td2 + td3

timedelta(minutes=10) + timedelta(hours=2) == timedelta(0, 7800)

td2 - td3

timedelta(weeks=3) - timedelta(hours=72) == timedelta(18)

td2 * n

timedelta(minutes=5) * 5 == timedelta(0, 1500)

td2 / n

timedelta(weeks=1) / 7 == timedelta(1)

datetime.date

d2 + td

date(2007, 12, 31) + timedelta(days=1) == date(2008, 1, 1)

d2 - td

date(2007, 12, 31) - timedelta(weeks=52) == date(2007, 1, 1)

d1 - d2

date(2007, 1, 1) - date(2006, 1, 1) == timedelta(365)

# 2004 年为闰年,有 366 天,Python 知道这一点:

date(2005, 1, 1) - date(2004, 1, 1) == timedelta(366)

datetime.datetime

d2 + td

datetime(2007, 12, 31, 23, 59) + timedelta(minutes=1) == datetime(2008, 1, 1, 0, 0)

d2 - td

datetime(2007, 12, 31) - timedelta(weeks=52, seconds=1) == datetime(2006, 12, 31, 23, 59, 59)

d1 - d2

datetime(2007, 1, 1) - datetime(2008, 1, 1) == timedelta(-365)


不 要企图用获取 2007 年 2 月 29 日来愚弄 Python,因为此日期不存在 — 解释器将引发 ValueError 异常,并显示“day is out of range for month”消息。您也可以随时使用 datetime.datetime.today() 为当前的日期和时间获取一个 datetime 对象。

如果您需要将现有的字符串分析为 date(time) 对象,可以使用 datetime 对象的 strptime() 方法。

>>> from datetime import datetime
>>> datetime.strptime("2007-12-31 23:59:59", "%Y-%m-%d %H:%M:%S")
datetime.datetime(2007, 12, 31, 23, 59, 59)

strptime 函数的格式字符串的完整列表包含在 Python 库参考的 时间模块文档 中。


时区


Python 通过 datetime 模块提供了对时区的支持,但它还没有马上进入自己的黄金阶段,它只是为您的实现提供了一个框架。您需要创建您自己的、从 datetime.tzinfo 继承的类,同时把需要的逻辑放在里面。Python 库参考对 此主题 进行了大量介绍。


TO_DATE(Python)


利用 cx_Oracle 可以使 Oracle 和 Python 间的数据类型转换变得完全不可见,这一点都不奇怪。包含 DATE 或 TIMESTAMP 列的查询返回 datetime 对象。

我们来看一下 Oracle 的 INTERVAL 运算和 Python 的 timedelta 计算是否等同。

>>> import cx_Oracle
>>> db = cx_Oracle.connect('hr/hrpwd@localhost:1521/XE')
>>> cursor = db.cursor()
>>> r = cursor.execute("SELECT end_date-start_date diff, end_date,
start_date FROM job_history")

>>> for diff, end_date, start_date in cursor:
... print diff, '\t', (end_date-start_date).days
...
2018    2018
1497    1497

1644    1644

很好!它们的确匹配。


下面的示例说明,您可以自由决定将日期/时间逻辑放在数据库端还是 Python 端。这一灵活性使得适合任意情形成为可能。我们来查一下 1998 年第 4 季度招聘的所有员工:

>>> Q4 = (datetime.date(1998, 10, 1), datetime.date(1998, 12, 31))
>>> r = cursor.execute("""
SELECT last_name||' '||first_name name, TO_CHAR(hire_date, 'YYYY-MM-DD')
FROM employees  WHERE hire_date BETWEEN :1 AND :2  ORDER BY hire_date ASC """, Q4)
>>> for row in cursor:
... print row
...
('Sewall Sarath', '1998-11-03')
('Himuro Guy', '1998-11-15')
('Cambrault Nanette', '1998-12-09')

可 以放心地使用 Python 的 datetime.date、datetime.time 和 datetime.datetime 对象作为绑定变量来查询日期。您可以选择所要求的粒度等级,但要记住,在处理秒的小数时,您需要指导 cx_Oracle 让其明白传递了一个小数部分。普通查询返回带有秒的小数部分的完全有效的时间戳,这仅与使用绑定变量有关。当然,您可以结合使用 Python 的 strptime() 函数和 Oracle 的 TO_DATE(),但说实话,干嘛要自找麻烦呢?


我们来创建一个简单表,结构如下:

CREATE TABLE python_tstamps (
ts TIMESTAMP(6)
);


下面的示例说明了这一问题。ts 有一个小数部分,它在 ts = datetime.datetime.now() 插入过程中被截断了:

>>> ts = datetime.datetime.now()
>>> print ts
-03-10 20:01:24.046000
>>> cursor.execute("INSERT INTO python_tstamps VALUES(:t)", {'t':ts})
>>> db.commit()


SQL> SELECT ts FROM python_tstamps;

TS
-----------------------------------------------------------------------
-MAR-07 08.01.56.000000 PM
-MAR-07 08.12.02.109000 PM
                            

解决方法是在准备和执行阶段之间使用 setinputsizes() 方法。它指导 cx_Oracle 如何处理特定的绑定变量。它在内存中预先定义了一些区域以存储这些对象。也可用它来为特定长度的字符串预先分配内存区域 — 它们应当以表示其长度的整数值给出。


现在我们来重新编写插入操作:

>>> ts = datetime.datetime.now()
>>> print ts
2007-03-10 20:12:02.109000
>>> cursor.prepare("INSERT INTO python_tstamps VALUES(:t_val)")
>>> cursor.setinputsizes(t_val=cx_Oracle.TIMESTAMP)
cursor.setinputsizes(t_val=cx_Oracle.TIMESTAMP)
>>> cursor.execute(None, {'t_val':ts})
>>> db.commit()


SQL> SELECT ts FROM python_tstamps;

TS
----------------------------------------------------------------------------
10-MAR-07 08.01.56.000000 PM
10-MAR-07 08.12.02.109000 PM

总结


日期时间上下文中应当记住的有关 cx_Oracle 4.3 的重要内容:

  • 不支持 INTERVAL 和 TIMESTAMP WITH (LOCAL) TIME ZONE
  • 除非在 prepare() 和 execute() 间使用了 setinputsizes() 方法,作为绑定变量传递的日期/时间值的秒的小数部分将被截取。
  • 对于时区支持,或者选择使用标准库的 datetime.tzinfo 类来编写您自己的实现,或者选择 SourceForge 中可用的 pytz 模块 。不过要做好充分准备,因为 WITH TIME ZONE 列类型和 Python datetime 对象间没有平滑的转换。

Python 标准库提供用于日期/时间任务的其他工具,包括:

  • 一个日历模块,用于以文本和 HTML 格式来显示日历,同时用于编写您自己的导出实现
  • 一个 timeit 模块,用于对 Python 代码进行概要描述和基准评测
  • 一个 sched 模块,它的功能与 Linux/Unix 下的 cron 实用程序相同

完成本教程后,您应当已经熟悉了负责 Oracle 和 Python 日期处理的概念。熟悉了无缝集成的 datetime 数据类型后,您现在可以将它们注入到您的可感知日历的应用程序中,把 Python 放到您的开发工具箱中。

马上就需要日历吗?导入日历;输出 calendar.calendar(2007)。

参考至:http://www.oracle.com/technetwork/cn/articles/dsl/prez-python-timesanddates-093014-zhs.html

如有错误,欢迎指正

邮箱:czmcj@163.com



已有 0 人发表留言,猛击->> 这里 <<-参与讨论


ITeye推荐