9.8. 数据类型格式化函数

PostgreSQL 格式化函数提供一套有效的工具用于把各种数据类型 （日期/时间，integer，floating point，numeric） 转换成格式化的字符串以及反过来从格式化的字符串转换成 指定的数据类型。 Table 9-20 列出了这些函数。 这些函数都遵循一个公共的调用习惯： 第一个参数是待格式化的值，而第二个是一个定义输出格式的模板。

Table 9-20. 格式化函数
函数        返回类型        描述        例子
to_char(timestamp, text)        text        把时间戳转换成字串        to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)        text        把时间间隔转为字串        to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)        text        把整数转换成字串        to_char(125, '999')
to_char(double precision, text)        text        把实数/双精度数转换成字串        to_char(125.8::real, '999D9')
to_char(numeric, text)        text        把 numeric 转换成字串        to_char(-125.8, '999D99S')
to_date(text, text)        date        把字串转换成日期        to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)        timestamp with time zone        把字串转换成时间戳        to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)        numeric        把字串转换成 numeric        to_number('12,454.8-', '99G999D9S')

警告：to_char(interval, text) 已经废弃了，在新写的代码里不应该再用了。在下一个版本里它将被删除。

在输出模板字串里（对 to_char 而言），该函数族可以识别一些特定的模式，并且 把待格式化的数值正确地格式化成相应的数据。 任何不属于模板模式的文本都简单地照字拷贝。同样，在一个输入 模板字串里（对除 to_char 外的任何东西），模板模式标识要查看的输入数据字串，并且将在该位置 寻找数值。

Table 9-21 显示了 可以用于格式化日期和时间值的模版。

Table 9-21. 用于日期/时间格式化的模式
模式        描述
HH        一天的小时数 （01-12）
HH12        一天的小时数 （01-12）
HH24        一天的小时数 （00-23）
MI        分钟 （00-59）
SS        秒 （00-59）
MS        毫秒 (000-999)
US        微秒 (000000-999999)
SSSS        午夜后的秒 （0-86399）
AM 或 A.M. 或 PM 或 P.M.        正午标识（大写）
am 或 a.m. 或 pm 或 p.m.        正午标识（小写）
Y,YYY        带逗号的年（4 和更多位）
YYYY        年（4和更多位）
YYY        年的后三位
YY        年的后两位
Y        年的最后一位
IYYY        ISO 年（4位或更多位）
IYY        ISO 年的最后 3 位
IY        ISO 年的最后 2 位
I        ISO 年的最后一位
BC 或 B.C. 或 AD 或 A.D.        纪元标识（大写）
bc 或 b.c. 或 ad 或 a.d.        纪元标识（小写）
MONTH        全长大写月份名（空白填充为9字符）
Month        全长混合大小写月份名（空白填充为9字符）
month        全长小写月份名（空白填充为9字符）
MON        大写缩写月份名（3字符）
Mon        缩写混合大小写月份名（3字符）
mon        小写缩写月份名（3字符）
MM        月份号（01-12）
DAY        全长大写日期名（空白填充为9字符）
Day        全长混合大小写日期名（空白填充为9字符）
day        全长小写日期名（空白填充为9字符）
DY        缩写大写日期名（3字符）
Dy        缩写混合大小写日期名（3字符）
dy        缩写小写日期名（3字符）
DDD        一年里的日子（001-366）
DD        一个月里的日子（01-31）
D        一周里的日子（1-7；周日是1）
W        一个月里的周数（1-5）（第一周从该月第一天开始）
WW        一年里的周数（1-53）（第一周从该年的第一天开始）
IW        ISO 一年里的周数（第一个星期四在第一周里）
CC        世纪（2 位）
J        儒略日（自公元前4712年1月1日来的天数）
Q        季度
RM        罗马数字的月份（I-XII；I=JAN）（大写）
rm        罗马数字的月份（I-XII；I=JAN）（小写）
TZ        时区名 （大写）
tz        时区名 （小写）

有一些修饰词可以应用于模板来修改它们的行为。比如， FMMonth 就是 带着 FM 前缀的 Month 模式。 Table 9-22 显示了 用于日期/时间格式化的修饰词模式。

Table 9-22. 日期/时间格式化的模板模式修饰词
修饰词        描述        例子
FM 前缀        填充模式(抑制填充空白和零)        FMMonth
TH 后缀        大写顺序数后缀        DDTH
th 后缀        小写顺序数后缀        DDth
FX 前缀        固定格式全局选项（见用法须知）        >FX Month DD Day
SP suffix        拼写模式（还未实现）        DDSP

日期/时间格式化的用法须知：

    *

      FM 抑制前导的零或尾随的空白， 如果没有使用它的话，会在输出中增加这些填充最终把输出变成固定宽度的模式。
    *

      如果没有使用 FX 选项， to_timestamp 和 to_date 在转换字串的时候忽略多个空白。 FX 必须做为模板里的第一个项声明。 比如 to_timestamp('2000    JUN', 'YYYY MON') 是正确的， to_timestamp('2000    JUN', 'FXYYYY MON') 会返回一个错误，因为to_timestamp 只预料会有一个空白。
    *

      在 to_char 模板 里可以有普通文本，并且它们会被照字输出。 你可以把一个字串放到双引号里强迫它解释成一个文本， 即使它里面包含模式关键字也如此。比如，在 '"Hello Year "YYYY'， YYYY 将被年份数据代替，但是Year里单独 的 Y 不会。
    *

      如果你想在输出里有双引号，那么你必须在它们 前面放双反斜杠，比如 '\\"YYYY Month\\"'. （需要两个反斜杠是因为反斜杠在字串常量里已经有特殊含义了。）
    *

      如果你使用的年份长于 4 位字符，那么用 YYYY 从字串向timestamp或者date 做转换时要受到限制。 你必须在 YYYY 后面使用一些非数字字符或者模板， 否则年份总是解释为 4 位数字。比如（对于 20000 年）： to_date('200001131', 'YYYYMMDD') 将会被解释成一个 4 位数字的年份， 最好在年后面使用一个非数字的分隔符，象 to_date('20000-1131', 'YYYY-MMDD') 或 to_date('20000Nov31', 'YYYYMonDD')。
    *

      将字串转化为timestamp时， 毫秒（MS）和微秒（US）都是用字串 的小数点后面的部分转换的。比如 to_timestamp('12:3', 'SS:MS') 不是 3 毫秒, 而是 300，因为转换把它看做 12 + 0.3 秒。 这意味着对于格式 'SS:MS'而言，输入值为 12:3 或 12:30或12:300 声明了相同数目的 毫秒。对于三毫秒，你必须使用 12:003，那么转换会把它看做 12 + 0.003 = 12.003 秒。

      下面是一个更复杂的 例子∶ to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US') 是 15 小时，12 分钟，和 2 秒 + 20 毫秒 + 1230微秒 = 2.021230 秒。
    *

      to_char 的星期几的编号（参阅 'D' 格式化模式）与 extract 的不同。

Table 9-23 显示了 可以用于数值格式化的模版模式。

Table 9-23. 用于数值格式化的模板模式
模式        描述
9        带有指定数值位数的值
0        带前导零的值
. (句点)        小数点
, (逗号)        分组（千）分隔符
PR        尖括号内负值
S        带符号的数值（使用区域设置）
L        货币符号（使用区域设置）
D        小数点（使用区域设置）
G        分组分隔符（使用区域设置）
MI        在指明的位置的负号（如果数字 < 0）
PL        在指明的位置的正号（如果数字 > 0）
SG        在指明的位置的正/负号
RN        罗马数字（输入在 1 和 3999 之间）
TH 或 th        序数后缀
V        移动指定位（小数）（参阅注解）
EEEE        科学记数。（现在还未实现）

数字格式化的用法须知：

    *

      使用SG，PL 或 MI 生成的符号并不挂在数字上面； 比如， to_char(-12, 'S9999')生成 '  -12', 而to_char(-12， 'MI9999')生成 '-  12'。 Oracle 里的实现不允许在 9 前面使用 MI，而是要求9 在 MI 前面。
    *

      9 声明和 9 的个数 相同的数字位数的数值。如果某个数值位没有数字，则输出一个空白。
    *

      TH 不会转换小于零的数值，也不会转换小数。
    *

      PL，SG 和 TH 是 PostgreSQL 扩展。
    *

      V 方便地把输入值乘以 10^n，这里 n 是跟在 V后面的数字。 to_char 不支持把 V 与一个小数点组合在一起使用 （也就是说，99.9V99 是不允许的）。

Table 9-24 显示了 一些使用 to_char 函数的用法。

Table 9-24. to_char 例子
表达式        结果
to_char(current_timestamp, 'Day, DD  HH12:MI:SS')        'Tuesday  , 06  05:39:18'
to_char(current_timestamp, 'FMDay, FMDD  HH12:MI:SS')        'Tuesday, 6  05:39:18'
to_char(-0.1, '99.99')        '  -.10'
to_char(-0.1, 'FM9.99')        '-.1'
to_char(0.1, '0.9')        ' 0.1'
to_char(12, '9990999.9')        '    0012.0'
to_char(12, 'FM9990999.9')        '0012.'
to_char(485, '999')        ' 485'
to_char(-485, '999')        '-485'
to_char(485,'9 9 9')        ' 4 8 5'
to_char(1485, '9,999')        ' 1,485'
to_char(1485, '9G999')        ' 1 485'
to_char(148.5, '999.999')        ' 148.500'
to_char(148.5, 'FM999.999')        '148.5'
to_char(148.5, 'FM999.990')        '148.500'
to_char(148.5, '999D999')        ' 148,500'
to_char(3148.5, '9G999D999')        ' 3 148,500'
to_char(-485, '999S')        '485-'
to_char(-485, '999MI')        '485-'
to_char(485, '999MI')        '485 '
to_char(485, 'FM999MI')        '485'
to_char(485, 'PL999')        '+485'
to_char(485, 'SG999')        '+485'
to_char(-485, 'SG999')        '-485'
to_char(-485, '9SG99')        '4-85'
to_char(-485, '999PR')        '<485>'
to_char(485, 'L999')        'DM 485
to_char(485, 'RN')        '        CDLXXXV'
to_char(485, 'FMRN')        'CDLXXXV'
to_char(5.2, 'FMRN')        'V'
to_char(482, '999th')        ' 482nd'
to_char(485, '"Good number:"999')        'Good number: 485'
to_char(485.8, '"Pre:"999" Post:" .999')        'Pre: 485 Post: .800'
to_char(12, '99V999')        ' 12000'
to_char(12.4, '99V999')        ' 12400'
to_char(12.45, '99V9')        ' 125'

9.9. 时间/日期函数和操作符

Table 9-26 显示了 PostgreSQL 里可以用于处理日期/时间数值的函数，随后一节里描述了细节。 Table 9-25 演示了基本算术操作符 （+，* 等等）的行为。 而与格式化相关的函数，可以参考Section 9.8。 你应该很熟悉来自 Section 8.5 的日期/时间数据类型的背景知识。

所有下面描述的函数和操作符接收的time或者timestamp输入实际上都来自两种可能： 一个是接收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 '7'        date '2001-10-05'
+         date '2001-09-28' + interval '1 hour'        timestamp '2001-09-28 01:00'
+         date '2001-09-28' + time '03:00'        timestamp '2001-09-28 03:00'
+         interval '1 day' + interval '1 hour'        interval '1 day 01:00'
+         timestamp '2001-09-28 01:00' + interval '23 hours'        timestamp '2001-09-29 00:00'
+         time '01:00' + interval '3 hours'        time '04:00'
-         - interval '23 hours'        interval '-23: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'
-         time '05:00' - time '03:00'        interval '02:00'
-         time '05:00' - interval '2 hours'        time '03:00'
-         timestamp '2001-09-28 23:00' - interval '23 hours'        timestamp '2001-09-28 00:00'
-         interval '1 day' - interval '1 hour'        interval '23:00'
-         timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'        interval '1 day 15:00'
*         interval '1 hour' * double precision '3.5'        interval '03:30'
/         interval '1 hour' / double precision '1.5'        interval '00:40'

Table 9-26. 日期/时间函数
函数        返回类型        描述        例子        结果
age(timestamp, timestamp)        interval        减去参数，生成一个使用年、月的"符号化"的结果        age('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
current_date        date        今天的日期；见 Section 9.9.4                   
current_time        time with time zone        现在的时间；见 Section 9.9.4                   
current_timestamp        timestamp with time zone        日期和时间；见 Section 9.9.4                   
date_part(text, timestamp)        double precision        获取子域(等效于 extract)；又见 Section 9.9.1         date_part('hour', timestamp '2001-02-16 20:38:40')        20
date_part(text, interval)        double precision        获取子域(等效于 extract)；又见 Section 9.9.1         date_part('month', interval '2 years 3 months')        3
date_trunc(text, timestamp)        timestamp        截断成指定的精度；又见Section 9.9.2         date_trunc('hour', timestamp '2001-02-16 20:38:40')        2001-02-16 20:00:00+00
extract(field from timestamp)        double precision        获取子域；又见 Section 9.9.1         extract(hour from timestamp '2001-02-16 20:38:40')        20
extract(field from interval)        double precision        获取子域；又见 Section 9.9.1         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
localtime        time        今日的时间；见 Section 9.9.4                   
localtimestamp        timestamp        日期和时间；见 Section 9.9.4                   
now()        timestamp with time zone        当前的日期和时间(等效于 current_timestamp)；见Section 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
       (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

9.9.1. 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 世纪。 如果你认为这个不合理，那么请把抱怨发给：罗马圣彼得教堂，梵蒂冈，教皇收。

    PostgreSQL 8.0 以前版本里并不遵循世纪的习惯编号，只是把年份除以 100。
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) (仅用于 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: 47

epoch

    对于 date 和 timestamp 数值而言， 是自 1970-01-01 00:00:00 以来的秒数(结果可能是负数。)； 对于 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 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

    20世纪（19xx年）里面的年份在第二个千年里。第三个千年从 2001 年一月一日开始。

    PostgreSQL 8.0 之前的版本并不遵循前年编号的习惯，只是返回年份除以 1000。
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)(仅用于 timestamp)

    SELECT EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');
    Result: 1

second

    秒域，包括小数部分 (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.5

timezone

    与 UTC 的时区偏移，以秒记。正数对应 UTC 东边的时区，负数对应 UTC 西边的时区。
timezone_hour

    时区偏移的小时部分。
timezone_minute

    时区偏移的分钟部分。
week

    该天在所在的年份里是第几周。根据定义 (ISO 8601)， 一年的第一周包含该年的一月四日。(ISO-8601的周从星期一开始。) 换句话说，一年的第一个星期四在第一周。（只用于 timestamp 数值）。

    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 函数主要的用途是做运算用。 对于用于显示的日期/时间数值格式化，参阅 Section 9.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: 4

9.9.2. date_trunc

date_trunc 函数在概念上和用于 数字的 trunc 函数类似。

date_trunc('field', source)

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

field 的有效数值是∶

microseconds
milliseconds
second
minute
hour
day
week
month
year
decade
century
millennium

例子：

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+00

9.9.3. AT TIME ZONE

AT TIME ZONE 构造允许把时间戳转换成不同的 时区。Table 9-27 显示了其变体。

Table 9-27. AT TIME ZONE变体
表达式        返回类型        描述
timestamp without time zone AT TIME ZONE zone         timestamp with time zone        把给定时区的当地时间转换成 UTC
timestamp with time zone AT TIME ZONE zone         timestamp without time zone        把 UTC 转换成给定时区的当地时间
time with time zone AT TIME ZONE zone         time with time zone        在时区之间转换当地时间

在这些表达式里，我们需要的 zone 可以声明为 文本串（比如，'PST'）或者一个时间间隔 （比如，INTERVAL '-08:00'）。 在文本的情况下，可用的时区名字在 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

第一个例子接受一个无时区的时间戳然后把她解释成 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 ( precision )
LOCALTIME
LOCALTIMESTAMP
LOCALTIME ( precision )
LOCALTIMESTAMP ( precision )

CURRENT_TIME 和 CURRENT_TIMESTAMP 带有时区值； LOCALTIME 和 LOCALTIMESTAMP 的数值没有时区值。

CURRENT_TIME ， CURRENT_TIMESTAMP， LOCALTIME 和 LOCALTIMESTAMP 可以有选择地给予一个精度参数， 该精度导致结果的秒数域园整为指定小数位。如果没有精度参数， 将给予所能得到的全部精度。

    注意: 在 PostgreSQL 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.662522

函数 now() 是传统的 PostgreSQL 和 CURRENT_TIMESTAMP 的等效物。

还有一个 timeofday() 函数，由于历史原因， 它返回一个字串，而不是 timestamp 值∶

SELECT timeofday();
Result: Sat Feb 17 19:07:32.000126 2001 EST

还有一件事提醒大家，那就是 CURRENT_TIMESTAMP 和相关的函数把时间当做当前事务的开始返回；在事务运行的时候， 它们的数值并不改变。 我们认为这是一个特性：目的是为了允许一个事务在"当前" 时间上有连贯的概念，这样在同一个事务离得多个修改可以有同样的时间戳。 但 timeofday() 返回当前的实际时间，并且随着事务的处理会前进。

    注意: 许多其它数据库系统更频繁地更新这些数值。

所有日期/时间类型还接受特殊的文本值 now， 用于声明当前的日期和时间。因此，下面三个都返回相同的结果∶

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';

    提示: 在创建表声明一个DEFAULT值的时候你是不会想用第三种形式的。 因为系统将在分析这个常量的时候把 now 转换成 一个 timestamp，因此在需要缺省值的时候， 就会使用创建表的时间! 而头两种形式要到实际使用缺省值的时候才计算， 因为它们是函数调用。因此它们可以给出插入时间行的时候 需要的缺省行为。

Notes
[1]       

如果操作系统实现了润秒， 那么上限是 60

9.10. 几何函数和操作符

有许多内置函数和操作符支持 几何类型point，box，lseg， line，path，polygon 和 circle 等， 在Table 9-28， Table 9-29，和 Table 9-30。

Table 9-28. 几何函数和操作符
操作符        描述        例子
+        平移        box '((0,0),(1,1))' + point '(2.0,0)'
-        平移        box '((0,0),(1,1))' - point '(2.0,0)'
*        伸缩/旋转        box '((0,0),(1,1))' * point '(2.0,0)'
/        伸缩/旋转        box '((0,0),(2,2))' / point '(2.0,0)'
#        交点或者交面        '((1,-1),(-1,1))' # '((1,1),(-1,-1))'
#        路径或多边形顶点数        # '((1,0),(0,1),(-1,0))'
@-@         长度或者周长        @-@ path '((0,0),(1,0))'
@@         中心        @@ circle '((0,0),10)'
##        第一个操作数相对第二个操作数的最近点        point '(0,0)' ## lseg '((2,0),(0,2))'
<->         间距        circle '((0,0),1)' <-> circle '((5,0),1)'
&&        重叠？        box '((0,0),(1,1))' && box '((0,0),(2,2))'
&<        是否没有延伸到右边？        box '((0,0),(1,1))' &< box '((0,0),(2,2))'
&>        是否没有延伸到左边？        box '((0,0),(3,3))' &> box '((0,0),(2,2))'
<<        在左边？        circle '((0,0),1)' << circle '((5,0),1)'
>>         在右边？        circle '((5,0),1)' >> circle '((0,0),1)'
<^        低于？        circle '((0,0),1)' <^ circle '((0,5),1)'
>^        高于？        circle '((0,5),1)' >^ circle '((0,0),1)'
?#        相交？        lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))';
?-        水平？        ?- lseg '((-1,0),(1,0))'
?-         是水平对齐吗？        point '(1,0)' ?- point '(0,0)'
?|        竖直？        ?| lseg '((-1,0),(1,0))'
?|        竖直对齐吗？        point '(0,1)' ?| point '(0,0)'
?-|        垂直？        lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'
?||        平行？        lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'
~         包含？        circle '((0,0),2)' ~ point '(1,1)'
@        包含或在...上？        point '(1,1)' @ circle '((0,0),2)'
~=        与...相同？        polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'

Table 9-29. 几何函数
函数        返回类型        描述        例子
area(object)        double precision        目标的面积        area(box '((0,0),(1,1))')
box_intersect(box, box)        box        方的交        box_intersect(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')
center(object)        point        对象中心        center(box '((0,0),(1,2))')
diameter(circle)        double precision        圆直径        diameter(circle '((0,0),2.0)')
height(box)        double precision        方的竖直高度        height(box '((0,0),(1,1))')
isclosed(path)        boolean        是闭合路径吗？        isclosed(path '((0,0),(1,1),(2,0))')
isopen(path)        boolean        是开环路径吗？        isopen(path '[(0,0),(1,1),(2,0)]')
length(object)        double precision        长度        length(path '((-1,0),(1,0))')
npoints(polygon)        integer        点数        npoints(path '[(0,0),(1,1),(2,0)]')
npoints(polygon)        integer        点数        npoints(polygon '((1,1),(0,0))')
pclose(path)        path        把路径转换为闭合        pclose(path '[(0,0),(1,1),(2,0)]')
popen(path)        path        把路径转换为开放的        popen(path '((0,0),(1,1),(2,0))')
radius(circle)        double precision        圆半径        radius(circle '((0,0),2.0)')
width(box)        double precision        方的水平尺寸        width(box '((0,0),(1,1))')

Table 9-30. 几何类型转换函数
函数        返回类型        描述        例子
box(circle)        box        将圆转换成长方形        box(cricle '((0,0),2.0)')
box(point, point)        box        将点转换成长方形        box(point '(0,0)', point '(1,1)')
box(polygon)        box        将多边形转换成长方形        box(polygon '((0,0),(1,1),(2,0))')
circle(box)        circle        方转换成圆        circle(box '((0,0),(1,1))')
circle(point, double precision)        circle        点和到圆的半径        circle(point '(0,0)',2.0)
lseg(box)        lseg        长方形对角线转化成线段        lseg(box '((-1,0),(1,0))')
lseg(point, point)        lseg        点转换成线段        lseg(point '(-1,0)',point '(1,0)')
path(polygon)        point        多边形转换成路径        path(polygon '((0,0),(1,1),(2,0))')
point(circle)        point        圆心        point(circle '((0,0),2.0)')
point(lseg, lseg)        point        转换成点（相交）        point(lseg '((-1,0),(1,0))', lseg '((-2,-2),(2,2))')
point(polygon)        point        多边形中心        point(polygon '((0,0),(1,1),(2,0))')
polygon(box)        polygon        方形转换成4点多边形        polygon(box '((0,0),(1,1))')
polygon(circle)        polygon        圆转换成12点多边形        polygon(circle '((0,0),2.0)')
polygon(npts, circle)        polygon        圆转换成npts 点多边形        polygon(12, circle '((0,0),2.0)')
polygon(path)        polygon        路径转换成多边形        polygon(path '((0,0),(1,1),(2,0))')

我们可以把一个 point 的两个组成部分当作索引分别为 0 和 1 的数组元素进行访问。比如，如果 t.p 是一个 point 字段，那么 SELECT p[0] from t 检索 X 座标而 UPDATE t SET p[1] = ... 改变 Y 座标。同样， box 或者 lseg 的值可以当作两个 point 的数组值看待。

函数 area 可以用于类型 box，circle，和 path。 area 函数操作 path 数据类型的时候， 只有在 path 的点没有交叉的情况下才可用。 比如，path '((0,0),(0,1),(2,1),(2,2),(1,2),(1,0),(0,0))'::PATH 是不行的， 而下面的视觉等效 path'((0,0),(0,1),(1,1),(1,2),(2,2),(2,1),(1,1),(1,0),(0,0))'::PATH 就可以。 如果交叉和不交叉的 path 概念让你胡涂，那么把上面两个 path 都画在一张纸上，你就明白了。

9.11. 网络地址类型函数和操作符

Table 9-31 显示了可以用于 cidr 和 inet 的操作符。 操作符 <<，<<= >>，和 >>= 用于计算子网包含：它们只考虑两个地址的网络部分，忽略任何主机部分， 然后判断其中一个网络部分是等于另外一个还是另外一个的子网。

Table 9-31. cidr 和 inet 操作符
操作符        描述        例子
<         小于吗        inet '192.168.1.5' < inet '192.168.1.6'
<=         小于或等于        inet '192.168.1.5' <= inet '192.168.1.5'
=         等于        inet '192.168.1.5' = inet '192.168.1.5'
>=         大于或等于        inet '192.168.1.5' >= inet '192.168.1.5'
>         大于        inet '192.168.1.5' > inet '192.168.1.4'
<>         不等于        inet '192.168.1.5' <> inet '192.168.1.4'
<<         包含于        inet '192.168.1.5' << inet '192.168.1/24'
<<=         包含于或等于        inet '192.168.1/24' <<= inet '192.168.1/24'
>>         包含        inet '192.168.1/24' >> inet '192.168.1.5'
>>=         包含或等于        inet '192.168.1/24' >>= inet '192.168.1/24'

Table 9-32 显示了所有可以用于 cidr 和 inet 的函数。 函数 host，text 和 abbrev 主要是为了提供可选的显示格式用的。 你可以用普通的类型转换语法∶ inet(expression) 或者 colname::inet 把一个文本数域转换成inet。

Table 9-32. cidr 和 inet 函数
函数        返回类型        描述        例子        结果
broadcast(inet)        inet        网络广播地址        broadcast('192.168.1.5/24')        192.168.1.255/24
host(inet)        text        将主机地址类型抽出为文本        host('192.168.1.5/24')        192.168.1.5
masklen(inet)        integer        抽取网络掩码长度        masklen('192.168.1.5/24')        24
set_masklen(inet,integer)        inet        为inet数值设置网络掩码长度        set_masklen('192.168.1.5/24',16)        192.168.1.5/16
netmask(inet)        inet        为网络构造网络掩码        netmask('192.168.1.5/24')        255.255.255.0
hostmask(inet)        inet        为网络构造主机掩码        hostmask('192.168.23.20/30')        0.0.0.3
network(inet)        cidr        抽取地址的网络部分        network('192.168.1.5/24')        192.168.1.0/24
text(inet)        text        把 IP 地址和掩码长度抽取为文本        text(inet '192.168.1.5')        192.168.1.5/32
abbrev(inet)        text        抽取缩写显示为文本        abbrev(cidr '10.1.0.0/16')        10.1/16
family(inet)        integer        抽取地址族 4 for IPv4, 6 for IPv6        family('::1')        6

Table 9-33 显示了可以用于 macaddr 类型的函数。 函数 trunc(macaddr) 返回一个 MAC 地址，该地址的最后三个字节设置为零。 这样可以把剩下的前缀与一个制造商相关联。 源程序目录 contrib/mac 里有一些用于创建和维护这样的 关联表的工具。

Table 9-33. macaddr 函数
函数        返回类型        描述        例子        结果
trunc(macaddr)        macaddr        把后三个字节置为零        trunc(macaddr '12:34:56:78:90:ab')        12:34:56:00:00:00

macaddr 类型还支持标准关系操作符 （>，<=，等） 用于词法排序。

9.12. 序列操作函数

本节描述 PostgreSQL 用于操作 序列对象的函数。序列对象(也叫序列生成器或者就是序列) 都是用 CREATE SEQUENCE 创建的特殊的单行表。 一个序列对象通常用于为行或者表生成唯一的标识符。 序列函数，在 Table 9-34 列出， 为我们从序列对象中获取后续的序列值提供了简单的，多用户安全的 方法。

Table 9-34. 序列函数
函数        返回类型        描述
nextval(text)        bigint        递增序列并返回新值
currval(text)        bigint        返回最近一次用nextval获取的数值
setval(text, bigint)        bigint        设置序列的当前数值
setval(text, bigint, boolean)        bigint        设置序列的当前数值以及 is_called 标志

由于历史原因，序列函数调用可以操作的序列是通过一个文本字串参数声明的。 为了可以和处理普通SQL名字具有一定兼容性，序列函数把它们的参数 转换成小写，除非用户用双引号把它们括起来。因此

nextval('foo')      操作序列号 foo
nextval('FOO')      操作序列号 foo
nextval('"Foo"')    操作序列号 Foo

必要时序列名可以用模式修饰∶

nextval('myschema.foo') 在 myschema.foo 上操作
nextval('"myschema".foo') 同上
nextval('foo')      为 foo 搜索搜索路径

当然，文本参数可以是一个表达式的参数，而不仅仅是一个简单的文本， 这一点有时候是有用的。

可用的序列函数有∶

nextval

    递增序列对象到它的下一个数值并且返回该值。这个动作是自动完成的： 即使多个会话并发运行 nextval，每个进程也会安全地收到一个唯一的序列值。
currval

    在当前会话中返回最近一次 nextval 抓到的该序列的数值。 （如果在本会话中从未在该序列上调用过 nextval， 那么会报告一个错误。）请注意因为此函数返回一个会话范围的数值， 它也能给出一个可预计的结果，可以判断其它会话是否执行过 nextval。
setval

    重置序列对象的计数器数值。双参数的形式设置序列的 last_value 字段为声明数值并且将其 is_called 字段设置为 true，表示下一次 nextval 将在返回数值之前递增该序列。在三参数形式里，is_called 可以设置为 true 或 false。如果你把它设置为 false，那么下一次 nextval 将返回这里声明的数值，而从随后的 nextval 才开始递增该序列。 比如

    SELECT setval('foo', 42);           下次nextval将返回 43
    SELECT setval('foo', 42, true);     和上面一样
    SELECT setval('foo', 42, false);    下次nextval将返回 42

    setval 返回的结果就是它的第二个参数的数值。

    Important: 为了避免从同一个序列获取数值的当前事务被阻塞， nextval 操作决不会回滚；也就是说，一旦一个数值已经被抓走， 那么就认为它已经用过了，即使调用 nextval 的事务后面又退出了也一样。这就意味着退出的事务可能在序列赋予的数值中留下"空洞"。 setval 操作也决不回滚。

如果一个序列对象是带着缺省参数创建的，那么对它调用 nextval 将返回从1 开始的后续的数值。 其它的行为可以通过使用 CREATE SEQUENCE 命令里的 特殊参数获取；参阅其命令参考页获取更多信息。

9.13. 条件表达式

本节描述在 PostgreSQL 里可以用的SQL兼容的条件表达式。

    提示: 如果你的需求超过这些条件表达式的能力，你可能会希望用一种更富表现力的编程语言写一个存储过程。

9.13.1. CASE

SQL CASE 表达式是一种通用的条件表达式，类似于其它语言中的 if/else 语句。

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

CASE 子句可以用于任何表达式可以有效存在的地方。 condition 是一个返回boolean 的表达式。 如果结果为真，那么 CASE 表达式的结果就是符合条件的 result。 如果结果为假，那么以相同方式搜寻任何随后的 WHEN 子句。 如果没有 WHEN condition 为真，那么 case 表达式的结果就是在 ELSE 子句里的值。 如果省略了 ELSE 子句而且没有匹配的条件， 结果为 NULL。

例子：

SELECT * FROM test;

a
---
1
2
3


SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

a | case
---+-------
1 | one
2 | two
3 | other

所有 result 表达式的数据的类型都必须可以转换成单一的输出类型。 参阅 Section 10.5 获取细节。

下面这个"简单的" CASE 表达式是上面的通用形式的一个特殊的变种。

CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END

先计算 expression 的值， 然后与所有在WHEN 子句里声明的 value 对比，直到找到一个相等的。 如果没有找到匹配的，则返回在 ELSE 子句里的 result （或者 NULL）。 这个类似于 C 里的 switch 语句。

上面的例子可以用简单 CASE 语法来写：

SELECT a,
       CASE a WHEN 1 THEN 'one'
              WHEN 2 THEN 'two'
              ELSE 'other'
       END
    FROM test;

a | case
---+-------
1 | one
2 | two
3 | other

CASE 表达式并不计算任何对于判断结果并不需要的子表达式。 比如，下面是一个可以避免被零除的方法：

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

9.13.2. COALESCE

COALESCE(value[, ...])

COALESCE 返回它的第一个非 NULL 的参数的值。 它常用于在为显示目的检索数据时用缺省值替换 NULL 值。 比如：

SELECT COALESCE(description, short_description, '(none)') ...

和 CASE 表达式一样，COALESCE 将不会 计算不需要用来判断结果的参数；也就是说，在第一个非空参数右边的参数不会被 计算。
9.13.3. NULLIF

NULLIF(value1, value2)

当且仅当 value1 和 value2 相等时，NULLIF 才返回 NULL。 否则它返回 value1。 这些可以用于执行上面给出的 COALESCE 例子的反例：

SELECT NULLIF(value, '(none)') ...

9.14. 数组函数和操作符

Table 9-35 显示了可以用于 array 类型的操作符。

Table 9-35. array 操作符
操作符        描述        例子        结果
=         等于        ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]        t
<>         不等于        ARRAY[1,2,3] <> ARRAY[1,2,4]        t
<         小于        ARRAY[1,2,3] < ARRAY[1,2,4]        t
>         大于        ARRAY[1,4,3] > ARRAY[1,2,4]        t
<=         小于或等于        ARRAY[1,2,3] <= ARRAY[1,2,3]        t
>=         大于或等于        ARRAY[1,4,3] >= ARRAY[1,4,3]        t
||         数组与数组连接        ARRAY[1,2,3] || ARRAY[4,5,6]        {1,2,3,4,5,6}
||         数组与数组连接        ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]        {{1,2,3},{4,5,6},{7,8,9}}
||         元素与数组连接        3 || ARRAY[4,5,6]        {3,4,5,6}
||         元素与数组连接        ARRAY[4,5,6] || 7        {4,5,6,7}

参阅 Section 8.10 获取有关数组操作符行为的更多细节。

Table 9-36 显示了可以用于数组类型的函数。 参阅 Section 8.10 获取更多信息以及使用这些函数的例子。

Table 9-36. array 函数
函数        返回类型        描述        例子        结果
array_cat (anyarray, anyarray)         anyarray        连接两个数组        array_cat(ARRAY[1,2,3], ARRAY[4,5])        {1,2,3,4,5}
array_append (anyarray, anyelement)         anyarray        向一个数组末尾附加一个元素        array_append(ARRAY[1,2], 3)        {1,2,3}
array_prepend (anyelement, anyarray)         anyarray        向一个数组开头附加一个元素        array_prepend(1, ARRAY[2,3])        {1,2,3}
array_dims (anyarray)         text        返回一个数组维数的文本表现        array_dims(array[[1,2,3], [4,5,6]])        [1:2][1:3]
array_lower (anyarray, integer)         integer        返回指定的数组维数的下界        array_lower(array_prepend(0, ARRAY[1,2,3]), 1)        0
array_upper (anyarray, integer)         integer        返回指定数组维数的上界        array_upper(ARRAY[1,2,3,4], 1)        4
array_to_string (anyarray, text)         text        使用提供的分隔符连接数组元素        array_to_string(array[1, 2, 3], '~^~')        1~^~2~^~3
string_to_array (text, text)         text[]        使用指定的分隔符把字串分裂成数组元素        string_to_array( 'xx~^~yy~^~zz', '~^~')        {xx,yy,zz}