TO_CHAR - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

TO_CHAR

TO_CHAR 將時間戳記或數值表達式轉換為字元字串資料格式。

Syntax (語法)

TO_CHAR (timestamp_expression | numeric_expression , 'format')

引數

timestamp_expression

此表達式產生 TIMESTAMP 或 TIMESTAMPTZ 類型值,或可隱含地強制轉換為時間戳記的值。

numeric_expression

此表達式產生數值資料類型的值,或可隱含地強制轉換為數值類型的值。如需詳細資訊,請參閱 數值類型。TO_CHAR 在數值字串左側插入空格。

注意

TO_CHAR 不支援 128 位元 DECIMAL 值。

format

新值的格式。關於有效的格式,請參閱日期時間格式字串 數值格式字串

傳回類型

VARCHAR

範例

下列範例將 EVENT 資料表中的每一個 STARTTIME 值,轉換為由時、分、秒組成的字串。

select to_char(starttime, 'HH12:MI:SS') from event where eventid between 1 and 5 order by eventid; to_char ---------- 02:30:00 08:00:00 02:30:00 02:30:00 07:00:00 (5 rows)

下列範例將整個時間戳記值轉換為另一種格式。

select starttime, to_char(starttime, 'MON-DD-YYYY HH12:MIPM') from event where eventid=1; starttime | to_char ---------------------+--------------------- 2008-01-25 14:30:00 | JAN-25-2008 02:30PM (1 row)

下列範例將時間戳記文字轉換為字元字串。

select to_char(timestamp '2009-12-31 23:15:59','HH24:MI:SS'); to_char ---------- 23:15:59 (1 row)

下列範例將數字轉換為字元字串。

select to_char(-125.8, '999D99S'); to_char --------- 125.80- (1 row)

下列範例將 sales 資料表中的支付價格減去佣金。然後,將差額捨進並轉換為羅馬數字,並顯示於 to_char 欄:

select salesid, pricepaid, commission, (pricepaid - commission) as difference, to_char(pricepaid - commission, 'rn') from sales group by sales.pricepaid, sales.commission, salesid order by salesid limit 10; salesid | pricepaid | commission | difference | to_char ---------+-----------+------------+------------+----------------- 1 | 728.00 | 109.20 | 618.80 | dcxix 2 | 76.00 | 11.40 | 64.60 | lxv 3 | 350.00 | 52.50 | 297.50 | ccxcviii 4 | 175.00 | 26.25 | 148.75 | cxlix 5 | 154.00 | 23.10 | 130.90 | cxxxi 6 | 394.00 | 59.10 | 334.90 | cccxxxv 7 | 788.00 | 118.20 | 669.80 | dclxx 8 | 197.00 | 29.55 | 167.45 | clxvii 9 | 591.00 | 88.65 | 502.35 | dii 10 | 65.00 | 9.75 | 55.25 | lv (10 rows)

下列範例將 to_char 欄所顯示的差額值加上貨幣符號:

select salesid, pricepaid, commission, (pricepaid - commission) as difference, to_char(pricepaid - commission, 'l99999D99') from sales group by sales.pricepaid, sales.commission, salesid order by salesid limit 10; salesid | pricepaid | commission | difference | to_char --------+-----------+------------+------------+------------ 1 | 728.00 | 109.20 | 618.80 | $ 618.80 2 | 76.00 | 11.40 | 64.60 | $ 64.60 3 | 350.00 | 52.50 | 297.50 | $ 297.50 4 | 175.00 | 26.25 | 148.75 | $ 148.75 5 | 154.00 | 23.10 | 130.90 | $ 130.90 6 | 394.00 | 59.10 | 334.90 | $ 334.90 7 | 788.00 | 118.20 | 669.80 | $ 669.80 8 | 197.00 | 29.55 | 167.45 | $ 167.45 9 | 591.00 | 88.65 | 502.35 | $ 502.35 10 | 65.00 | 9.75 | 55.25 | $ 55.25 (10 rows)

下列範例列出每一筆銷售完成的世紀。

select salesid, saletime, to_char(saletime, 'cc') from sales order by salesid limit 10; salesid | saletime | to_char ---------+---------------------+--------- 1 | 2008-02-18 02:36:48 | 21 2 | 2008-06-06 05:00:16 | 21 3 | 2008-06-06 08:26:17 | 21 4 | 2008-06-09 08:38:52 | 21 5 | 2008-08-31 09:17:02 | 21 6 | 2008-07-16 11:59:24 | 21 7 | 2008-06-26 12:56:06 | 21 8 | 2008-07-10 02:12:36 | 21 9 | 2008-07-22 02:23:17 | 21 10 | 2008-08-06 02:51:55 | 21 (10 rows)

下列範例將 EVENT 資料表中的每一個 STARTTIME 值,轉換為由時、分、秒及時區組成的字串。

select to_char(starttime, 'HH12:MI:SS TZ') from event where eventid between 1 and 5 order by eventid; to_char ---------- 02:30:00 UTC 08:00:00 UTC 02:30:00 UTC 02:30:00 UTC 07:00:00 UTC (5 rows) (10 rows)