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

作者：Przemyslaw Piotrowski

Oracle 和 Python 的日期处理介绍

2007 年 9 月发布

从 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)
--------------------------
                      1987
                      1987
                         ?
                      2000
107 rows selected.  

利用此方法和 Oracle 的日期运算，您也可以得到两个日期之间的时间间隔：

SQL> SELECT hire_date, SYSDATE, EXTRACT(YEAR FROM (SYSDATE-hire_date) YEAR TO MONTH) "Years" 
  2  FROM employees WHERE ROWNUM <= 5;
HIRE_DATE          SYSDATE                 Years
------------------ ------------------ ----------
17-JUN-87          23-FEB-07                  19
21-SEP-89          23-FEB-07                  17
13-JAN-93          23-FEB-07                  14
03-JAN-90          23-FEB-07                  17
21-MAY-91          23-FEB-07                  15
5 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
                                       
6 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')
---------- 
04/07/2007 

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

	  
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI TZH:TZM') FROM dual;
TO_CHAR(SYSTIMESTAMP,'HH24:MI TZH:TZM')
------------ 
16: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
--------------
23-FEB-07
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> SELECT SYSDATE FROM dual;
SYSDATE
-------------------
2007-02-23 17:50:15

国家语言支持 (NLS) 参数的设置过程在  中进行了详细说明。Oracle 数据库 SQL 语言参考 包含与此主题相关的更多信息。尤其要查阅和这两部分，以了解日期运算和内置的日期函数的更多信息。

Python

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

>>> import datetime
>>> d = datetime.datetime.now()
>>> print d
2007-03-03 16:48:27.734000
>>> print type(d)

>>> 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) # leap year 2004 had 366 days and Python is aware of that: 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
2007-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
-----------------------------------------------------------------------
10-MAR-07 08.01.24.000000 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 中可用的 。不过要做好充分准备，因为 WITH TIME ZONE 列类型和 Python datetime 对象间没有平滑的转换。

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

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

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