PostgreSQL 8X时间日期函数.docx

1、PostgreSQL 8X时间日期函数PostgreSQL 8.X时间日期函数 2011-03-16 10:16:11| 分类： postgreSQL|字号 订阅9.9. 时间/日期函数和操作符Table 9-26 显示了 PostgreSQL 里可以用于处理日期/时间数值的函数，随后一节里描述了细节。 Table 9-25 演示了基本算术操作符 （+，* 等等）的行为。 而与格式化相关的函数，可以参考Section 9.8。 你应该很熟悉来自 Section 8.5 的日期/时间数据类型的背景知识。所有下面描述的函数和操作符接收的time或者timestamp输入实际上都来自两种可能： 一个

2、是接收time with time zone 或 timestamp with time zone， 另外一种是time without time zone 或者 timestamp without time zone。 出于简化考虑，这些变种没有独立显示出来。还有，+ 和 * 操作符都是以可交换的操作符对方式存在的 （比如，date + integer 和 integer + date）；我们只显示了这样的交换操作符对中的一个。Table 9-25. 日期/时间操做符操作符例子结果+date 2001-09-28 + integer 7date 2001-10-05+date 2001-09

3、-28 + interval 1 hourtimestamp 2001-09-28 01:00+date 2001-09-28 + time 03:00timestamp 2001-09-28 03:00+interval 1 day + interval 1 hourinterval 1 day 01:00+timestamp 2001-09-28 01:00 + interval 23 hourstimestamp 2001-09-29 00:00+time 01:00 + interval 3 hourstime 04:00- interval 23 hoursinterval -23:

4、00-date 2001-10-01 - date 2001-09-28integer 3-date 2001-10-01 - integer 7date 2001-09-24-date 2001-09-28 - interval 1 hourtimestamp 2001-09-27 23:00-time 05:00 - time 03:00interval 02:00-time 05:00 - interval 2 hourstime 03:00-timestamp 2001-09-28 23:00 - interval 23 hourstimestamp 2001-09-28 00:00-

5、interval 1 day - interval 1 hourinterval 23:00-timestamp 2001-09-29 03:00 - timestamp 2001-09-27 12:00interval 1 day 15:00*interval 1 hour * double precision 3.5interval 03:30/interval 1 hour / double precision 1.5interval 00:40Table 9-26. 日期/时间函数函数返回类型描述例子结果age(timestamp,timestamp)interval减去参数，生成一个

6、使用年、月的符号化的结果age(2001-04-10, timestamp 1957-06-13)43 years 9 mons 27 daysage(timestamp)interval从current_date减去得到的数值age(timestamp 1957-06-13)43 years 8 mons 3 dayscurrent_datedate今天的日期；见 Section 9.9.4 current_timetime with time zone现在的时间；见 Section 9.9.4 current_timestamptimestamp with time zone日期和时间；见

7、 Section 9.9.4 date_part(text,timestamp)double precision获取子域(等效于 extract)；又见 Section 9.9.1date_part(hour, timestamp 2001-02-16 20:38:40)20date_part(text,interval)double precision获取子域(等效于 extract)；又见 Section 9.9.1date_part(month, interval 2 years 3 months)3date_trunc(text,timestamp)timestamp截断成指定的精度；

8、又见Section 9.9.2date_trunc(hour, timestamp 2001-02-16 20:38:40)2001-02-16 20:00:00+00extract(field fromtimestamp)double precision获取子域；又见 Section 9.9.1extract(hour from timestamp 2001-02-16 20:38:40)20extract(field frominterval)double precision获取子域；又见 Section 9.9.1extract(month from interval 2 years 3

9、 months)3isfinite(timestamp)boolean测试有穷时间戳(非无穷)isfinite(timestamp 2001-02-16 21:28:30)trueisfinite(interval)boolean测试有穷时间间隔isfinite(interval 4 hours)truelocaltimetime今日的时间；见 Section 9.9.4 localtimestamptimestamp日期和时间；见 Section 9.9.4 now()timestamp with time zone当前的日期和时间(等效于current_timestamp)；见Sectio

10、n 9.9.4 timeofday()text当前日期和时间；见Section 9.9.4 除了这些函数以外，还支持 SQL 操作符 OVERLAPS：( start1, end1 ) OVERLAPS ( start2, end2 ) ( start1, length1 ) OVERLAPS ( start2, length2 )这个表达式在两个时间域（用它们的终点定义）重叠的时候生成真值。 终点可以以一对日期，时间，或者时间戳来声明；或者是一个后面跟着一个时间间隔的 日期，时间，时间戳。SELECT (DATE 2001-02-16, DATE 2001-12-21) OVERLAPS (

11、DATE 2001-10-30, DATE 2002-10-30); Result:true SELECT (DATE 2001-02-16, INTERVAL 100 days) OVERLAPS (DATE 2001-10-30, DATE 2002-10-30); Result:false9.9.1. EXTRACT，date_partEXTRACT (field from source)extract 函数从日期/时间数值里抽取 子域，比如年或者小时等。source 必须是一个类型 timestamp，time，或者 interval 的值表达式。 （类型为 date 的表达式将转换为

12、 timestamp，因此也可以用。） field 是一个标识符 或者字串，它指定从源数据中抽取的数域。extract 函数返回类型为double precision 的数值。 下列数值是有效数据域的名字century世纪。SELECT EXTRACT(CENTURY FROM TIMESTAMP 2000-12-16 12:21:13); Result:20 SELECT EXTRACT(CENTURY FROM TIMESTAMP 2001-02-16 20:38:40); Result:21第一个世纪从 0001-01-01 00:00:00 AD 开始， 尽管那时候人们还不知道这是第一

13、个世纪。这个定义适用于所有使用格里高利历法的国家。 没有 0 世纪，我们直接从公元前 1 世纪到公元 1 世纪。 如果你认为这个不合理，那么请把抱怨发给：罗马圣彼得教堂，梵蒂冈，教皇收。PostgreSQL 8.0 以前版本里并不遵循世纪的习惯编号，只是把年份除以 100。day(月分)里的日期域(1-31)SELECT EXTRACT(DAY from TIMESTAMP 2001-02-16 20:38:40); Result:16decade年份域除以10SELECT EXTRACT(DECADE from TIMESTAMP 2001-02-16 20:38:40); Result:2

14、00dow每周的星期号(0 - 6；星期天是 0) (仅用于 timestamp)SELECT EXTRACT(DOW FROM TIMESTAMP 2001-02-16 20:38:40); Result:5请注意 extract 的星期几编号和 to_char 函数的不同。doy一年的第几天(1 -365/366) (仅用于 timestamp)SELECT EXTRACT(DOY from TIMESTAMP 2001-02-16 20:38:40); Result:47epoch对于 date 和 timestamp 数值而言， 是自 1970-01-01 00:00:00 以来的秒数

15、(结果可能是负数。)； 对于 interval 数值而言，它是时间间隔的总秒数。SELECT EXTRACT(EPOCH from TIMESTAMP 2001-02-16 20:38:40); Result:982352320 SELECT EXTRACT(EPOCH from INTERVAL 5 days 3 hours); Result:442800下面是把 epoch 值转换回时间戳的方法：SELECT TIMESTAMP WITH TIME ZONE epoch + 982384720 * INTERVAL 1 second;hour小时域 (0 - 23)SELECT EXTRA

16、CT(HOUR from TIMESTAMP 2001-02-16 20:38:40); Result:20microseconds秒域，包括小数部分，乘以 1,000,000。请注意它包括全部的秒。SELECT EXTRACT(MICROSECONDS from TIME 17:12:28.5); Result:28500000millennium千年。SELECT EXTRACT(MILLENNIUM from TIMESTAMP 2001-02-16 20:38:40); Result:320世纪（19xx年）里面的年份在第二个千年里。第三个千年从 2001 年一月一日开始。Postgr

17、eSQL 8.0 之前的版本并不遵循前年编号的习惯，只是返回年份除以 1000。milliseconds秒域，包括小数部分，乘以 1000。请注意它包括完整的秒。SELECT EXTRACT(MILLISECONDS from TIME 17:12:28.5); Result:28500minute分钟域 (0 - 59)SELECT EXTRACT(MINUTE from TIMESTAMP 2001-02-16 20:38:40); Result:38month对于 timestamp 数值，它是一年里的月份数(1 - 12)； 对于 interval 数值，它是月的数目，然后对 12 取

18、模(0 - 11)SELECT EXTRACT(MONTH from TIMESTAMP 2001-02-16 20:38:40); Result:2 SELECT EXTRACT(MONTH from INTERVAL 2 years 3 months); Result:3 SELECT EXTRACT(MONTH from INTERVAL 2 years 13 months); Result:1quarter该天所在的该年的季度(1 - 4)(仅用于 timestamp)SELECT EXTRACT(QUARTER from TIMESTAMP 2001-02-16 20:38:40);

19、 Result:1second秒域，包括小数部分 (0 - 59 1)SELECT EXTRACT(SECOND from TIMESTAMP 2001-02-16 20:38:40); Result:40 SELECT EXTRACT(SECOND from TIME 17:12:28.5); Result:28.5timezone与 UTC 的时区偏移，以秒记。正数对应 UTC 东边的时区，负数对应 UTC 西边的时区。timezone_hour时区偏移的小时部分。timezone_minute时区偏移的分钟部分。week该天在所在的年份里是第几周。根据定义 (ISO 8601)， 一年的

20、第一周包含该年的一月四日。(ISO-8601的周从星期一开始。) 换句话说，一年的第一个星期四在第一周。（只用于 timestamp 数值）。SELECT EXTRACT(WEEK from TIMESTAMP 2001-02-16 20:38:40); Result:7year年份域。要记住这里没有 0 AD，所以从 AD 年里抽取 BC 年应该小心些。SELECT EXTRACT(YEAR from TIMESTAMP 2001-02-16 20:38:40); Result:2001extract 函数主要的用途是做运算用。 对于用于显示的日期/时间数值格式化，参阅 Section 9.

21、8。date_part 函数是在传统的 Ingres 函数的基础上制作的(该 函数等效于 SQL 标准函数 extract)date_part(field, source)请注意这里的 field 参数必须是 一个字串值，而不是一个名字。有效的 date_part 数域名 和用于 extract 的是一样的。SELECT date_part(day, TIMESTAMP 2001-02-16 20:38:40);Result:16 SELECT date_part(hour, INTERVAL 4 hours 3 minutes); Result:49.9.2. date_truncdate_

22、trunc 函数在概念上和用于 数字的 trunc 函数类似。date_trunc(field, source)source 是类型 timestamp 的值表达式(类型 date 和 time 的数值都分别自动转换成timestamp或者interval)。 用 field 选择对该时间戳数值 选用什么样的精度进行截断)。 返回的数值是 timestamp 类型或者interval，所有小于选定的 精度的域都设置为零(或者一，如果是日期和月份域的话)。field 的有效数值是microsecondsmillisecondssecondminutehourdayweekmonthyeardec

23、adecenturymillennium例子：SELECT date_trunc(hour, TIMESTAMP 2001-02-16 20:38:40); Result:2001-02-16 20:00:00+00 SELECT date_trunc(year, TIMESTAMP 2001-02-16 20:38:40); Result:2001-01-01 00:00:00+009.9.3. AT TIME ZONEAT TIME ZONE 构造允许把时间戳转换成不同的 时区。Table 9-27 显示了其变体。Table 9-27. AT TIME ZONE变体表达式返回类型描述tim

24、estamp without time zone AT TIME ZONE zonetimestamp with time zone把给定时区的当地时间转换成 UTCtimestamp with time zone AT TIME ZONE zonetimestamp without time zone把 UTC 转换成给定时区的当地时间time with time zone AT TIME ZONE zonetime with time zone在时区之间转换当地时间在这些表达式里，我们需要的 zone 可以声明为 文本串（比如，PST）或者一个时间间隔 （比如，INTERVAL -08:0

25、0）。 在文本的情况下，可用的时区名字在 Table B-4 里显示。 （可能支持更通用的 Table B-6 会更好些，不过目前这些还没有实现。）例子（假设本地时区是 PST8PDT）：SELECT TIMESTAMP 2001-02-16 20:38:40 AT TIME ZONE MST; Result:2001-02-16 19:38:40-08 SELECT TIMESTAMP WITH TIME ZONE 2001-02-16 20:38:40-05 AT TIME ZONE MST; Result:2001-02-16 18:38:40第一个例子接受一个无时区的时间戳然后把她解释

26、成 MST 时间（UTC-7） 生成 UTC 时间戳，然后这个时间转换为 PST（UTC-8）来显示。 第二个例子接受一个声明为 EST（UTC-5）的时间戳，然后把它 转换成 MST（UTC-7）的当地时间。函数timezone(zone, timestamp) 等效于 SQL 兼容的构造timestamp AT TIME ZONE zone。9.9.4. 当前日期/时间我们可以使用下面的函数获取当前的日期和/或时间CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TIME ( precision ) CURRENT_TIMESTAMP

27、( precision ) LOCALTIME LOCALTIMESTAMP LOCALTIME ( precision ) LOCALTIMESTAMP ( precision )CURRENT_TIME 和 CURRENT_TIMESTAMP 带有时区值； LOCALTIME 和 LOCALTIMESTAMP 的数值没有时区值。CURRENT_TIME ， CURRENT_TIMESTAMP， LOCALTIME 和 LOCALTIMESTAMP 可以有选择地给予一个精度参数， 该精度导致结果的秒数域园整为指定小数位。如果没有精度参数， 将给予所能得到的全部精度。注意: 在 Postgre

28、SQL 7.2 之前没有实现精度参数， 结果总是给出整数的秒。一些例子：SELECT CURRENT_TIME; Result:14:39:53.662522-05 SELECT CURRENT_DATE; Result:2001-12-23 SELECT CURRENT_TIMESTAMP; Result:2001-12-23 14:39:53.662522-05 SELECT CURRENT_TIMESTAMP(2); Result:2001-12-23 14:39:53.66-05 SELECT LOCALTIMESTAMP; Result:2001-12-23 14:39:53.662

29、522函数 now() 是传统的 PostgreSQL 和 CURRENT_TIMESTAMP 的等效物。还有一个 timeofday() 函数，由于历史原因， 它返回一个字串，而不是 timestamp 值SELECT timeofday(); Result:Sat Feb 17 19:07:32.000126 2001 EST还有一件事提醒大家，那就是 CURRENT_TIMESTAMP 和相关的函数把时间当做当前事务的开始返回；在事务运行的时候， 它们的数值并不改变。 我们认为这是一个特性：目的是为了允许一个事务在当前 时间上有连贯的概念，这样在同一个事务离得多个修改可以有同样的时间戳。

30、 但 timeofday() 返回当前的实际时间，并且随着事务的处理会前进。注意: 许多其它数据库系统更频繁地更新这些数值。所有日期/时间类型还接受特殊的文本值 now， 用于声明当前的日期和时间。因此，下面三个都返回相同的结果SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP now;提示: 在创建表声明一个DEFAULT值的时候你是不会想用第三种形式的。 因为系统将在分析这个常量的时候把 now 转换成 一个timestamp，因此在需要缺省值的时候， 就会使用创建表的时间! 而头两种形式要到实际使用缺省值的时候才计算， 因为它们是函数调用。因此它们可以给出插入时间行的时候 需要的缺省行为。如果操作系统实现了润秒， 那么上限是 60