日期/时间函数和操作符 ====================== **表.日期/时间函数** 显示了可以用于处理日期/时间数值的函数，后面描述了细节。下表演示了基本算术操作符的行为(+,*, 等)。而与格式化相关的函数，可以参考 `数据类型格式化函数 <./data-type-formatting-functions.html>`_ 。你应该很熟悉 `日期/时间类型 <./date-time-types.html>`_ 的背景知识。 所有下述函数和操作符接收的time或timestamp输入实际上都来自两种可能： 一种是接收time with time zone或timestamp with time zone， 另外一种是接收time without time zone或timestamp without time zone。出于简化考虑，这些变种没有独立显示出来。还有，+和* 操作符都是以可交换的操作符对(比如，date + integer 和 integer + date)； 我们只显示了这样的交换操作符对中的一个。 **表.日期/时间操作符** .. list-table:: :widths: auto :header-rows: 1 * - 操作符 - 例子 - 结果 * - \+ - date '2001-09-28' + integer '7' - date '2001-10-05' * - \+ - date '2001-09-28' + interval '1 hour' - timestamp '2001-09-28 01:00:00' * - \+ - date '2001-09-28' + time '03:00' - timestamp '2001-09-28 03:00:00' * - \+ - interval '1 day' + interval '1 hour' - interval '1 day 01:00:00' * - \+ - timestamp '2001-09-28 01:00' + interval '23 hours' - timestamp '2001-09-29 00:00:00' * - \+ - time '01:00' + interval '3 hours' - time '04:00:00' * - \- - \- interval '23 hours' - interval '-23:00:00' * - \- - date '2001-10-01' - date '2001-09-28' - integer '3' * - \- - date '2001-10-01' - integer '7' - date '2001-09-24' * - \- - date '2001-09-28' - interval '1 hour' - timestamp '2001-09-27 23:00:00' * - \- - time '05:00' - time '03:00' - interval '02:00:00' * - \- - time '05:00' - interval '2 hours' - time '03:00:00' * - \- - timestamp '2001-09-28 23:00' - interval '23 hours' - timestamp '2001-09-28 00:00:00' * - \- - interval '1 day' - interval '1 hour' - interval '1 day -01:00:00' * - \- - timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' - interval '1 day 15:00:00' * - \* - 900 * interval '1 second' - interval '00:15:00' * - \* - 21 * interval '1 day' - interval '21 days' * - \* - double precision '3.5' * interval '1 hour' - interval '03:30:00' * - \/ - interval '1 hour' / double precision '1.5' - interval '00:40:00' **表.日期/时间函数** .. list-table:: :widths: auto :header-rows: 1 * - 函数 - 返回类型 - 描述 - 例子 - 结果 * - age(timestamp, timestamp) - interval - 减去参数，产生使用年和月的“符号化”结果 - age(timestamp '2001-04-10', timestamp '1957-06-13') - 43 years 9 mons 27 days * - age(timestamp) - interval - 从current_date减去参数后的结果 - age(timestamp '1957-06-13') - 43 years 8 mons 3 days * - clock_timestamp() - timestamp with time zone - 当前时间戳（在语句执行时变化）；见 `当前日期/时间 <./date-time-functions-and-operators.html#id21>`_ - \ - \ * - current_date - date - 当前的日期；见 `当前日期/时间 <./date-time-functions-and-operators.html#id21>`_ - \ - \ * - current_time - time with time zone - 当日时间；见 `当前日期/时间 <./date-time-functions-and-operators.html#id21>`_ - \ - \ * - current_timestamp - timestamp with time zone - (当前事务开始时的)当前时间和日期；见 `当前日期/时间 <./date-time-functions-and-operators.html#id21>`_ - \ - \ * - date_part(text, timestamp) - double precision - 获取子域(等效于extract)；又见 `EXTRACT, date_part <./date-time-functions-and-operators.html#id17>`_ - date_part('hour', timestamp '2001-02-16 20:38:40') - 20 * - date_part(text, interval) - double precision - 获取子域(等效于extract)；又见 `EXTRACT, date_part <./date-time-functions-and-operators.html#id17t>`_ - date_part('month', interval '2 years 3 months') - 3 * - date_trunc(text, timestamp) - timestamp - 截断成指定的精度；又见 `date_trunc <./date-time-functions-and-operators.html#date_trunc>`_ - date_trunc('hour', timestamp '2001-02-16 20:38:40') - 2001-02-16 20:00:00 * - extract(field from timestamp) - double precision - 获取子域；又见 `EXTRACT, date_part <./date-time-functions-and-operators.html#id17>`_ - extract(hour from timestamp '2001-02-16 20:38:40') - 20 * - extract(field from interval) - double precision - 获取子域；又见 `EXTRACT, date_part <./date-time-functions-and-operators.html#id17>`_ - extract(month from interval '2 years 3 months') - 3 * - isfinite(timestamp) - boolean - 测试是否为有穷日期(不等于无穷) - isfinite(timestamp '2001-02-16 21:28:30') - true * - isfinite(interval) - boolean - 测试是否为有穷时间间隔 - isfinite(interval '4 hours') - true * - justify_days(interval) - interval - 按照每月 30 天调整时间间隔 - justify_days(interval '30 days') - 1 month * - justify_hours(interval) - interval - 按照每天 24 小时调整时间间隔 - justify_hours(interval '24 hours') - 1 day * - justify_interval(interval) - interval - 使用justify_days和justify_hours调整时间间隔的同时进行正负号调整 - justify_interval(interval '1 mon -1 hour') - 29 days 23:00:00 * - localtime - time - 当日时间；见 `当前日期/时间 <./date-time-functions-and-operators.html#id21>`_ - \ - \ * - localtimestamp - timestamp - 当前事务开始时的当前日期和时间；见 `当前日期/时间 <./date-time-functions-and-operators.html#id21>`_ - \ - \ * - now() - timestamp with time zone - 当前事务开始时的当前日期和时间；见 `当前日期/时间 <./date-time-functions-and-operators.html#id21>`_ - \ - \ * - statement_timestamp() - timestamp with time zone - 当前事务开始时的当前日期和时间；见 `当前日期/时间 <./date-time-functions-and-operators.html#id21>`_ - \ - \ * - timeofday() - text - 当前事务开始时的当前日期和时间；见 `当前日期/时间 <./date-time-functions-and-operators.html#id21>`_ - \ - \ * - transaction_timestamp() - timestamp with time zone - 当前事务开始时的当前日期和时间；见 `当前日期/时间 <./date-time-functions-and-operators.html#id21>`_ - \ - \ 除了这些函数以外，还支持 SQL 的OVERLAPS操作符：: :: (start1, end1) OVERLAPS (start2, end2) (start1, length1) OVERLAPS (start2, length2) 这个表达式在两个时间域(用它们的终点定义)重叠的时候生成真值，在不重叠是生成假值。 终点可以用一对日期、时间、时间戳来声明；或者是一个后面跟着一个时间间隔的日期、 时间、时间戳。 :: SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (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: false 当把interval值添加到timestamp with time zone上(或从中减去)的时候， days 部分会按照指定的天数增加(或减少)timestamp with time zone的日期。对于横跨夏令时的变化(会话的时区设置被识别为夏时制)，interval '1 day' 并不一定等于interval '24 hours'。例如，当会话的时区设置为CST7CDT 的时候timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' 的结果是timestamp with time zone '2005-04-03 12:00-06'， 而将interval '24 hours'增加到相同的timestamp with time zone 之上的结果则是timestamp with time zone '2005-04-03 13:00-06'， 因为CST7CDT时区在2005-04-03 02:00的时候有一个夏令时变更。 EXTRACT, date_part ------------------- :: EXTRACT(field FROM source) extract函数从日期/时间数值里抽取子域，比如年、小时等。 *source* 必须是一个timestamp, time, interval类型的值表达式(类型为date的表达式转换为 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 开始，尽管那时候人们还不知道这是第一个世纪。 这个定义适用于所有使用阳历的国家。没有 0 世纪，我们直接从公元前 1 世纪到公元 1 世纪。 如果你认为这个不合理，那么请把抱怨发给：梵蒂冈，罗马圣彼得教堂，教皇收。 day (月份)里的日期(1-31) :: SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 16 decade 年份除以 10 :: SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 200 dow 每周的星期号(0-6;星期天是0)(只适用于时间戳值) :: SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 5 请注意，extract的星期几编号和to_char函数不同。 doy 一年的第几天(1-365/366)(只适用于时间戳值) :: SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 47 epoch 对于date和timestamp值而言， 是自 1970-01-01 00:00:00 当地时间以来的秒数； 对于interval值而言，它是时间间隔的总秒数。 :: SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08'); Result: 982384720 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 EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 20 microseconds 秒域(包括小数部分)乘以 1,000,000 。请注意它包括全部的秒。 :: SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5'); Result: 28500000 millennium 千年 :: SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 3 1900s的年份在第二个千年里。第三个千年从 2001 年 1 月 1 日零时开始。 milliseconds 秒域(包括小数部分)乘以 1000 。请注意它包括完整的秒。 :: SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5'); Result: 28500 minute 分钟域(0-59) :: SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 38 month 对于timestamp值，它是一年里的月份数(1-12)；对于interval值， 它是月的数目，然后对 12 取模(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: 1 quarter 该天所在的该年的季度(1-4)(只适用于时间戳值) :: SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 1 second 秒域，不包括小数部分(0-59) :: SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 40 SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); Result: 28 timezone 与 UTC 的时区偏移量，以秒记。正数对应 UTC 东边的时区，负数对应 UTC 西边的时区。 timezone_hour 时区偏移量的小时部分。 timezone_minute 时区偏移量的分钟部分。 week 该天在所在的年份里是第几周。ISO 8601 定义一年的第一周包含该年的一月四日(ISO-8601 的周从星期一开始)。换句话说， 一年的第一个星期四在第一周。 在ISO定义里，一月的头几天可能是前一年的第 52 或者第 53 周。比如，2005-01-01是 2004 年的第 53 周， 而2006-01-01是 2005 年的第 52 周。 :: SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 7 year 年份域。要记住这里没有0 AD，所以从AD年里抽取BC年应该小心些。 :: SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 2001 extract函数主要的用途是运算。 对于用于显示的日期/时间数值格式化，参阅 `数据类型格式化函数 <./data-type-formatting-functions.html>`_ 。 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: 4 date_trunc ------------- date_trunc函数在概念上和用于数字的trunc函数类似。 :: date_trunc('field', source) *source* 是timestamp或interval 类型的值表达式(date和time类型的值都分别自动转换成 timestamp或interval)。 *field* 选择对该时间戳值用什么样的精度进行截断。返回的数值是timestamp 或interval类型，所有不如选定的精度重要的域都设置为零(日期和月份域则为 1)。 *field* 的有效数值是： microseconds milliseconds second minute hour day week month quarter year decade century millennium 例子： :: SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40'); Result: 2001-02-16 20:00:00 SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); Result: 2001-01-01 00:00:00 AT TIME ZONE -------------- AT TIME ZONE构造允许把时间戳转换成不同的时区。下表显示了其变体。 **表. AT TIME ZONE 变体** .. list-table:: :widths: auto :header-rows: 1 * - 表达式 - 返回类型 - 描述 * - timestamp without time zone AT TIME ZONE zone - timestamp with time zone - 把给出的 **不带时区** 的时间戳转换成给定时区的时间戳 * - timestamp with time zone AT TIME ZONE zone - timestamp without time zone - 把给出的 **带时区** 的时间戳转换成新时区的时间戳 * - time with time zone AT TIME ZONE zone - time with time zone - 把给出的 **带时区** 的时间转换成给定时区的时间 在这些表达式里， *zone* 可以声明为文本串(比如'PST') 或者一个时间间隔(比如INTERVAL '-08:00')。在文本的情况下， 可用的时区名字在 `时区 <./date-time-types.html#id11>`_ 有详细描述。 例子(假设本地时区是PST8PDT): :: SELECT (TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST') AT TIME ZONE 'MST'; Result: 2001-02-16 20:38:40 SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST'; Result: 2001-02-16 18:38:40 第一个例子接受一个无时区的时间戳然后把它解释成 MST(UTC-7) 时间生成 UTC 时间戳， 然后把这个时间转换为 PST(UTC-8) 显示。第二个例子接受一个声明为 EST(UTC-5) 的时间戳， 然后把它转换成 MST(UTC-7) 的当地时间。 timezone(zone, timestamp) 函数等效于 SQL 兼容的构造timestamp AT TIME ZONE zone。 当前日期/时间 -------------- OushuDB 提供许多返回当前日期和时间的函数。 这些符合 SQL 标准的函数全部都按照当前事务的开始时刻返回结果： :: CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TIME(precision) CURRENT_TIMESTAMP(precision) LOCALTIME LOCALTIMESTAMP LOCALTIME(precision) LOCALTIMESTAMP(precision) CURRENT_TIME和CURRENT_TIMESTAMP 返回带有时区的值；LOCALTIME和LOCALTIMESTAMP 返回不带时区的值。 CURRENT_TIME,CURRENT_TIMESTAMP, LOCALTIME,LOCALTIMESTAMP 可以有选择地获取一个精度参数，该精度导致结果的秒数域圆整到指定小数位。 如果没有精度参数，将给予所能得到的全部精度。 一些例子： :: 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.662522 因为这些函数全部都按照当前事务的开始时刻返回结果， 所以它们的值在事务运行的整个期间内都不改变。我们认为这是一个特性： 目的是为了允许一个事务在"当前时间"上有连贯的概念， 这样在同一个事务里的多个修改可以保持同样的时间戳。 .. note:: 许多其它数据库系统更频繁地更新这些数值。 OushuDB 也提供了一些返回当前声明开始时间的函数，在函数被调用的那一瞬间的实际时间。完整的非SQL 标准时间函数列表是： :: now() transaction_timestamp() statement_timestamp() clock_timestamp() timeofday() now() 是传统的OushuDB命令，和CURRENT_TIMESTAMP等效。transaction_timestamp()相当于CURRENT_TIMESTAMP，但是它命名时明确地反映了其返回类型。statement_timestamp() 返回当前事务开始时刻的时间戳(更准确的说是收到客户端最后一条命令的时间)。statement_timestamp()和transaction_timestamp() 在一个事务的第一条命令里返回值相同，但是在随后的命令中却不一定相同。clock_timestamp()返回实时时钟的当前时间戳， 因此它的值甚至在同一条 SQL 命令中都会变化。timeofday()类似于clock_timestamp()， 它也返回实时时钟的当前时间戳，不过它返回一个格式化了的text字符串， 而不是timestamp with time zone值。 所有日期/时间类型还接受特殊的文本值now， 用于声明当前的日期和时间(重申：当前事务的开始时刻)。 因此，下面三个都返回相同的结果： :: SELECT CURRENT_TIMESTAMP; SELECT now(); SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT .. tip:: 在创建表的时候你不应该用第三种形式声明一个DEFAULT值。系统将在分析这个常量的时候把now转换为一个timestamp，因此这个缺省值就会变成创建表的时间！而前两种形式要到实际使用缺省值的时候才计算， 因为它们是函数调用。因此它们可以给出每次插入行的时刻。 延时执行 ---------- 下面的这个函数可以用于让服务器进程延时执行： :: pg_sleep(seconds) pg_sleep让当前的会话进程休眠 *seconds* 秒以后再执行。 *seconds* 是一个double precision 类型的值，所以可以指定带小数的秒数。例如： :: SELECT pg_sleep(1.5); .. note:: 有效的休眠时间间隔精度是平台相关的，通常 0.01 秒是通用的。 休眠的时间将至少等于指定的时间，也有可能由于服务器荷载较重等原因而比指定的时间长。 .. warning:: 请确保调用pg_sleep的会话没有持有不必要的锁。 否则其它会话可能必须等待这个休眠的会话释放所持有的锁，从而减慢系统速度。