Postgresql 中文操作指南

9.8. Data Type Formatting Functions #

PostgreSQL 格式化函数提供了一组功能强大的工具，用于将各种数据类型（日期/时间、整数、浮点数、数字）转换为格式化字符串，并用于将格式化字符串转换为特定数据类型。 Table 9.26 中列出了它们。这些函数都遵循一个通用的调用约定：第一个自变量是要格式化的值，第二个自变量是定义输出或输入格式的模板。

The PostgreSQL formatting functions provide a powerful set of tools for converting various data types (date/time, integer, floating point, numeric) to formatted strings and for converting from formatted strings to specific data types. Table 9.26 lists them. These functions all follow a common calling convention: the first argument is the value to be formatted and the second argument is a template that defines the output or input format.

Table 9.26. Formatting Functions

Function

Description

Example(s)

to_char ( timestamp, text ) → text

to_char ( timestamp with time zone, text ) → text

Converts time stamp to string according to the given format.

to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') → 05:31:12

to_char ( interval, text ) → text

Converts interval to string according to the given format.

to_char(interval '15h 2m 12s', 'HH24:MI:SS') → 15:02:12

to_char ( numeric_type, text ) → text

Converts number to string according to the given format; available for integer, bigint, numeric, real, double precision.

to_char(125, '999') → 125

to_char(125.8::real, '999D9') → 125.8

to_char(-125.8, '999D99S') → 125.80-

to_date ( text, text ) → date

Converts string to date according to the given format.

to_date('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05

to_number ( text, text ) → numeric

Converts string to numeric according to the given format.

to_number('12,454.8-', '99G999D9S') → -12454.8

to_timestamp ( text, text ) → timestamp with time zone

Converts string to time stamp according to the given format. (See also to_timestamp(double precision) in Table 9.33.)

to_timestamp('05 Dec 2000', 'DD Mon YYYY') → 2000-12-05 00:00:00-05

Tip

to_timestamp 和 to_date 用于处理无法通过简单转换转换的输入格式。对于大多数标准的日期/时间格式，只需将源字符串强制转换为所需的数据类型即可，并且更容易。同样，to_number 对于标准数字表示也是不必要的。

to_timestamp and to_date exist to handle input formats that cannot be converted by simple casting. For most standard date/time formats, simply casting the source string to the required data type works, and is much easier. Similarly, to_number is unnecessary for standard numeric representations.

在 to_char 输出模板字符串中，某些模式会被识别并根据给定值替换为格式适当的数据。任何不是模板模式的文本都将原样复制。类似地，在输入模板字符串（对于其他函数）中，模板模式标识由输入数据字符串提供的值。如果模板字符串中存在不是模板模式的字符，则输入数据字符串中对应的字符将被简单地跳过（无论它们是否等于模板字符串字符）。

In a to_char output template string, there are certain patterns that are recognized and replaced with appropriately-formatted data based on the given value. Any text that is not a template pattern is simply copied verbatim. Similarly, in an input template string (for the other functions), template patterns identify the values to be supplied by the input data string. If there are characters in the template string that are not template patterns, the corresponding characters in the input data string are simply skipped over (whether or not they are equal to the template string characters).

Table 9.27 显示了可用于格式化日期和时间值的模板模式。

Table 9.27 shows the template patterns available for formatting date and time values.

Table 9.27. Template Patterns for Date/Time Formatting

Pattern

Description

hour of day (01–12)

HH12

hour of day (01–12)

HH24

hour of day (00–23)

minute (00–59)

second (00–59)

millisecond (000–999)

microsecond (000000–999999)

FF1

tenth of second (0–9)

FF2

hundredth of second (00–99)

FF3

millisecond (000–999)

FF4

tenth of a millisecond (0000–9999)

FF5

hundredth of a millisecond (00000–99999)

FF6

microsecond (000000–999999)

SSSS, SSSSS

seconds past midnight (0–86399)

AM, am, PM or pm

meridiem indicator (without periods)

A.M., a.m., P.M. or p.m.

meridiem indicator (with periods)

Y,YYY

year (4 or more digits) with comma

YYYY

year (4 or more digits)

YYY

last 3 digits of year

last 2 digits of year

last digit of year

IYYY

ISO 8601 week-numbering year (4 or more digits)

IYY

last 3 digits of ISO 8601 week-numbering year

last 2 digits of ISO 8601 week-numbering year

last digit of ISO 8601 week-numbering year

BC, bc, AD or ad

era indicator (without periods)

B.C., b.c., A.D. or a.d.

era indicator (with periods)

MONTH

full upper case month name (blank-padded to 9 chars)

Month

full capitalized month name (blank-padded to 9 chars)

month

full lower case month name (blank-padded to 9 chars)

MON

abbreviated upper case month name (3 chars in English, localized lengths vary)

Mon

abbreviated capitalized month name (3 chars in English, localized lengths vary)

mon

abbreviated lower case month name (3 chars in English, localized lengths vary)

month number (01–12)

DAY

full upper case day name (blank-padded to 9 chars)

Day

full capitalized day name (blank-padded to 9 chars)

day

full lower case day name (blank-padded to 9 chars)

abbreviated upper case day name (3 chars in English, localized lengths vary)

abbreviated capitalized day name (3 chars in English, localized lengths vary)

abbreviated lower case day name (3 chars in English, localized lengths vary)

DDD

day of year (001–366)

IDDD

day of ISO 8601 week-numbering year (001–371; day 1 of the year is Monday of the first ISO week)

day of month (01–31)

day of the week, Sunday (1) to Saturday (7)

ISO 8601 day of the week, Monday (1) to Sunday (7)

week of month (1–5) (the first week starts on the first day of the month)

week number of year (1–53) (the first week starts on the first day of the year)

week number of ISO 8601 week-numbering year (01–53; the first Thursday of the year is in week 1)

century (2 digits) (the twenty-first century starts on 2001-01-01)

Julian Date (integer days since November 24, 4714 BC at local midnight; see Section B.7)

quarter

month in upper case Roman numerals (I–XII; I=January)

month in lower case Roman numerals (i–xii; i=January)

upper case time-zone abbreviation (only supported in to_char)

lower case time-zone abbreviation (only supported in to_char)

TZH

time-zone hours

TZM

time-zone minutes

time-zone offset from UTC (only supported in to_char)

修改符可应用于任何模板模式以更改其行为。例如，FMMonth 是 Month 模式，带有 FM 修改符。 Table 9.28 显示了日期/时间格式化的修改符模式。

Modifiers can be applied to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier. Table 9.28 shows the modifier patterns for date/time formatting.

Table 9.28. Template Pattern Modifiers for Date/Time Formatting

Modifier

Description

Example

FM prefix

fill mode (suppress leading zeroes and padding blanks)

FMMonth

TH suffix

upper case ordinal number suffix

DDTH, e.g., 12TH

th suffix

lower case ordinal number suffix

DDth, e.g., 12th

FX prefix

fixed format global option (see usage notes)

FX Month DD Day

TM prefix

translation mode (use localized day and month names based on lc_time)

TMMonth

SP suffix

spell mode (not implemented)

DDSP

日期/时间格式化的使用说明：

Usage notes for date/time formatting:

Tip

在 PostgreSQL 12 之前，可以使用非字母或非数字字符跳过输入字符串中的任意文本。例如，to_timestamp('2000y6m1d', 'yyyy-MM-DD') 用于操作。现在，你只能为此目的使用字母字符。例如，to_timestamp('2000y6m1d', 'yyyytMMtDDt') 和 to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"') 跳过 y、m 和 d。

Prior to PostgreSQL 12, it was possible to skip arbitrary text in the input string using non-letter or non-digit characters. For example, to_timestamp('2000y6m1d', 'yyyy-MM-DD') used to work. Now you can only use letter characters for this purpose. For example, to_timestamp('2000y6m1d', 'yyyytMMtDDt') and to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"') skip y, m, and d.

Caution

虽然 to_date 会拒绝格里高利历和 ISO 星期几编号日期字段的混合使用，但 to_char 不会拒绝，因为像 YYYY-MM-DD (IYYY-IDDD) 这样的输出格式规范可能有用。但避免编写类似 IYYY-MM-DD 的内容；在年初附近，这会产生出人意料的结果。（有关更多信息，请参见 Section 9.9.1。）

While to_date will reject a mixture of Gregorian and ISO week-numbering date fields, to_char will not, since output format specifications like YYYY-MM-DD (IYYY-IDDD) can be useful. But avoid writing something like IYYY-MM-DD; that would yield surprising results near the start of the year. (See Section 9.9.1 for more information.)

Table 9.29 显示了可用于格式化数字值的模板模式。

Table 9.29 shows the template patterns available for formatting numeric values.

Table 9.29. Template Patterns for Numeric Formatting

Pattern

Description

digit position (can be dropped if insignificant)

digit position (will not be dropped, even if insignificant)

. (period)

decimal point

, (comma)

group (thousands) separator

negative value in angle brackets

sign anchored to number (uses locale)

currency symbol (uses locale)

decimal point (uses locale)

group separator (uses locale)

minus sign in specified position (if number < 0)

plus sign in specified position (if number > 0)

plus/minus sign in specified position

Roman numeral (input between 1 and 3999)

TH or th

ordinal number suffix

shift specified number of digits (see notes)

EEEE

exponent for scientific notation

数字格式化的使用说明：

Usage notes for numeric formatting:

某些修改符可应用于任何模板模式以更改其行为。例如，FM99.99 是 99.99 模式，带有 FM 修改符。 Table 9.30 显示了数字格式化的修改符模式。

Certain modifiers can be applied to any template pattern to alter its behavior. For example, FM99.99 is the 99.99 pattern with the FM modifier. Table 9.30 shows the modifier patterns for numeric formatting.

Table 9.30. Template Pattern Modifiers for Numeric Formatting

Modifier

Description

Example

FM prefix

fill mode (suppress trailing zeroes and padding blanks)

FM99.99

TH suffix

upper case ordinal number suffix

999TH

th suffix

lower case ordinal number suffix

999th

Table 9.31 显示了 to_char 函数使用的一些示例。

Table 9.31 shows some examples of the use of the to_char function.

Table 9.31. to_char Examples

Expression

Result

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, 'FM90.99')

'-0.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'

to_char(0.0004859, '9.99EEEE')

' 4.86e-04'