9.8. 數據類型格式化函數 · PostgreSQL 中文文檔 9.3

# 9.8\. 數據類型格式化函數 PostgreSQL格式化函數提供一套有效的工具用于把各種數據類型轉換成格式化的字符串以及反過來從格式化的字符串轉換成指定的數據類型。 [Table 9-20](#calibre_link-2204)列出了這些函數。這些函數都遵循一個公共的調用習慣：第一個參數是待格式化的值，而第二個是定義輸出或輸入格式的模板。單參數`to_timestamp`函數也可以使用；它接受一個`double precision` 參數，并且從Unix紀元（秒自1970-01-01 00:00:00+00）轉換為`timestamp with time zone` 類型。（`Integer`類型的Unix紀元隱含的轉換為`double precision`類型。） **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` | 把數字轉換成字符串 | `to_char(-125.8, '999D99S')` | | ``to_date(```text`, `text`) | `date` | 把字符串轉換成日期 | `to_date('05?Dec?2000', 'DD?Mon?YYYY')` | | ``to_number(```text`, `text`) | `numeric` | 把字符串轉換成數字 | `to_number('12,454.8-', '99G999D9S')` | | ``to_timestamp(```text`, `text`) | `timestamp with time zone` | 把字符串轉換成時間戳 | `to_timestamp('05?Dec?2000', 'DD?Mon?YYYY')` | | ``to_timestamp(```double precision`) | `timestamp with time zone` | 把 Unix 紀元轉換成時間戳 | `to_timestamp(1284352323)` | 在`to_char`輸出模板字符串里，該函數族可以識別一些特定的模式，并且把給定的數值正確地格式化成相應的數據。任何不屬于模板模式的文本都簡單地逐字拷貝。同樣，在一個輸入模板字符串里(對其他函數)，模板模式標識數值由輸入數據字符串提供。 [Table 9-21](#calibre_link-2205)顯示了可以用于格式化日期和時間值的模版。 **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`, `am`, `PM` 或 `pm` | 正午指示器(沒有周期) | | `A.M.`, `a.m.`, `P.M.` 或 `p.m.` | 正午指示器(有周期) | | `Y,YYY` | 帶逗號的年(4 和更多位) | | `YYYY` | 年(4 和更多位) | | `YYY` | 年的后三位 | | `YY` | 年的后兩位 | | `Y` | 年的最后一位 | | `IYYY` | ISO年(4 位或更多位) | | `IYY` | ISO年的最后三位 | | `IY` | ISO年的最后兩位 | | `I` | ISO年的最后一位 | | `BC`, `bc`, `AD` 或 `ad` | 紀元標識(沒有周期) | | `B.C.`, `b.c.`, `A.D.` 或 `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) | | `IDDD` | ISO一年里的日（001-371；年的第一天是第一個ISO周的星期一） | | `DD` | 一個月里的日(01-31) | | `D` | 一周里的日，星期日（`1`）到星期六（`7`） | | `ID` | ISO一周里的日，星期一（`1`）到星期日（`7`） | | `W` | 一個月里的周數(1-5)(第一周從該月第一天開始) | | `WW` | 一年里的周數(1-53)(第一周從該年的第一天開始) | | `IW` | ISO一年里的周數(01-53；第一個星期四在第一周里) | | `CC` | 世紀(2 位)(20 世紀從 2001-01-01 開始) | | `J` | 儒略日(自公元前 4714 年 11 月 24日午夜來的天數) | | `Q` | 季度（`to_date`和`to_timestamp`忽略此項） | | `RM` | 羅馬數字的月份(I-XII ；I=January)(大寫) | | `rm` | 羅馬數字的月份(i-xii ；i=January)(小寫) | | `TZ` | 時區名(大寫) | | `tz` | 時區名(小寫) | 有一些修飾詞可以應用于模板來修改它們的行為。比如，`FMMonth` 就是帶著`FM`前綴的`Month`模式。 [Table 9-22](#calibre_link-1068) 顯示了用于日期/時間格式化的修飾詞模式。 **Table 9-22\. 日期/時間格式化的模板模式修飾詞** | 修飾詞 | 描述 | 例子 | | --- | --- | --- | | `FM` 前綴 | 填充模式(抑制填充空白和尾隨零) | `FMMonth` | | `TH` 后綴 | 大寫順序數后綴 | `DDTH`, 例如, `12TH` | | `th` 后綴 | 小寫順序數后綴 | `DDth`, 例如, `12th` | | `FX` 前綴 | 固定格式全局選項(見用法須知) | `FX?Month?DD?Day` | | `TM` 前綴 | 翻譯模式(基于[lc_time](#calibre_link-2206)顯示本地化的日期和月份名) | `TMMonth` | | `SP` 后綴 | 拼寫模式(未實現) | `DDSP` | 日期/時間格式化的用法須知： * `FM` suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width. In PostgreSQL, `FM` modifies only the next specification, while in Oracle `FM` affects all subsequent specifications, and repeated `FM` modifiers toggle fill mode on and off. `FM`抑制前導的零或尾隨的空白，如果沒有使用它的話，會在輸出中增加這些填充最終把輸出變成固定寬度的模式。在PostgreSQL中，`FM`只修改下一個規范，而在Oracle中`FM`影響所有隨后的規范，并且重復`FM`修飾符填充模式開關打開或關閉。 * `TM`不包含結尾空白。 * 如果沒有使用`FX`，`to_timestamp`和`to_date` 在轉換字符串的時候忽略多個空白。比如`to_timestamp('2000????JUN', 'YYYY MON')` 是正確的，但是`to_timestamp('2000????JUN', 'FXYYYY MON')` 會返回一個錯誤，因為`to_timestamp`只預料會有一個空白。 `FX`必須做為模板里的第一個項聲明。 * 在`to_char`模板里可以有普通文本，并且它們會被逐字輸出。你可以把一個字符串放到雙引號里強迫它解釋成一個文本，即使它里面包含模式關鍵字也如此。比如`'"Hello Year "YYYY'`中的`YYYY`將被年份數據代替，但是`Year`里單獨的`Y`會。在`to_date`， `to_number`和`to_timestamp`里，加雙引號的字符串忽略包含在字符串中的輸入字符的數量，例如，`"XX"`忽略兩個輸入字符。 * 如果你想在輸出里有雙引號，那么你必須在它們前面放反斜杠，比如`'\"YYYY Month\"'`。 * 如果年份的格式規范少于四個字節，例如`YYY`，并且提供的年份少于四個字節，那么年份將調整為接近于2020，例如`95`成為1995。 * 如果你使用的年份長于4位字符，那么用`YYYY`從字符串向`timestamp` 或`date`做轉換時要受到限制。你必須在`YYYY` 后面使用一些非數字字符或者模板，否則年份總是解釋為 4 位數字。比如對于 20000 年： `to_date('200001131', 'YYYYMMDD')`將會被解釋成一個 4 位數字的年份，最好在年后面使用一個非數字的分隔符，像`to_date('20000-1131', 'YYYY-MMDD')` 或`to_date('20000Nov31', 'YYYYMonDD')`。 * 在從字符串向`timestamp`或`date`轉換的時候，如果有`YYY`, `YYYY`或`Y,YYY`字段，那么`CC`字段會被忽略。如果`CC`與`YY`或`Y`一起使用，那么年份用指定的世紀計算。如果指定了世紀而沒有指定年，那么假設使用這個世紀的第一年。 * ISO周時間（有別于公歷日期）可以用下面的兩種方法之一聲明為 `to_timestamp`和`to_date`： * 年，周和工作日：例如`to_date('2006-42-4', 'IYYY-IW-ID')` 返回日期`2006-10-19`。如果你省略工作日，那么它假設為1（星期一）。 * 年和一年中的日：例如`to_date('2006-291', 'IYYY-IDDD')`返回 `2006-10-19`。試圖用ISO周和公歷日期字段混合構造日期是沒有意義的，并且將導致一個錯誤。在ISO年的范圍，"月"和"月中的天"的概念沒有意義。在公歷年的范圍，ISO周沒有意義。用戶應該避免混合公歷和ISO日期規范。 * 將字符串轉化為`timestamp`時，毫秒(`MS`)和微秒(`US`) 都是用小數點后面的位數轉換的。比如`to_timestamp('12:3', 'SS:MS')` 不是 3 毫秒, 而是 300 毫秒，因為轉換把它看做 12+0.3 秒。這意味著對于格式`SS:MS` 而言，輸入值`12:3`,`12:30`,`12:300` 聲明了相同的毫秒數。對于 3 毫秒，你必須使用`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(..., 'ID')`的星期編號匹配`extract(isodow from ...)` 函數，但是`to_char(..., 'D')`的星期編號不匹配`extract(dow from ...)` 的天編號。 * `to_char(interval)`將`HH`和`HH12`格式化為12小時，也就是零時和36時輸出是`12`，而`HH24`可以輸出完整的小時數，時間間隔可以超過23。 [Table 9-23](#calibre_link-1067)顯示了用于數值格式化的模板模式。 **Table 9-23\. 數值格式化的模版模式** | 模式 | 描述 | | --- | --- | | `9` | 帶有指定數值位數的值 | | `0` | 帶前導零的值 | | `.` (句點) | 小數點 | | `,` (逗號) | 分組(千) 分隔符 | | `PR` | 尖括號內負值 | | `S` | 帶符號的數值(使用區域設置) | | `L` | 貨幣符號(使用區域設置) | | `D` | 小數點(使用區域設置) | | `G` | 分組分隔符(使用區域設置) | | `MI` | 在指明的位置的負號(如果數字 < 0) | | `PL` | 在指明的位置的正號(如果數字 > 0) | | `SG` | 在指明的位置的正/負號 | | `RN` | 羅馬數字(輸入在 1 和 3999 之間) | | `TH` or `th` | 序數后綴 | | `V` | 移動指定位(小數)(參閱注解) | | `EEEE` | 指數為科學記數法 | 數字格式化的用法須知： * 使用`SG`, `PL`, `MI` 生成的符號并不掛在數字上面；比如，`to_char(-12, 'MI9999')` 生成`'-??12'`；但是`to_char(-12, 'S9999')` `'??-12'`。Oracle里的實現不允許在`9` 前面使用`MI`，而是要求`9`在`MI`前面。 * `9`聲明和`9`的個數相同的數字位數的數值。如果某個數值位沒有數字，則輸出一個空白。 * `TH`不會轉換小于零的數值，也不會轉換小數。 * `PL`, `SG`, `TH` 是PostgreSQL擴展。 * `V`方便地把輸入值乘以 `10^``_n_`，這里`_n_`是跟在`V`后面的數字。 `to_char`不支持把`V` 與一個小數點組合在一起使用(也就是說`99.9V99`是不允許的)。 * `EEEE`（科學記數法）不能和任何其他格式化的模式或修飾符以外的數字和小數點模式混合使用，并且必須在格式化字符串的后面（例如，`9.99EEEE`是合法的模式）。一定的修飾符可以應用于任何模板模式來改變其行為。例如，`FM9999` 是`9999`模式和`FM`修飾符。 [Table 9-24](#calibre_link-2207)顯示了數字格式的修飾符模式。 **Table 9-24\. 數字格式的模板模式修飾符** | 修飾符 | 描述 | 示例 | | --- | --- | --- | | `FM` 前綴 | 填充模式 (抑制填充空白和尾隨零) | `FM9999` | | `TH` 后綴 | 大寫順序數后綴 | `999TH` | | `th` 后綴 | 小寫順序數后綴 | `999th` | [Table 9-25](#calibre_link-2208)顯示了一些 `to_char`函數的用法。 **Table 9-25\. `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'` | | `to_char(0.0004859, '9.99EEEE')` | `' 4.86e-04'` |