PostgreSQL 湚嚟寡庘昄柊冕婔喖橬昽䔇噖噙䫘庯檪劇䓉昄扞䌂傋蘸扵潊湚嚟寡䔇庖严婾傖埪埉誺準傯湚嚟寡䔇庖严婾蘸扵潊毺垔䔇昄扞䌂傋㔗臘9-20彖庺庖認底庘昄㔗認底庘昄鄘镕冻婔婻噸噌䔇脄䫘幹愇丸婔婻埗昄滇写湚嚟寡䔇唚蔯丸庯婻滇垔幬膷庺潡膷庺湚嚟䔇昇溪㔗
to_timestamp 庘昄幘脘毖埖婔婻 double precision 埗昄檪垄傯 Unix 亻噄蘸扵潊 timestamp with time zone㔗Integer 䌂傋䔇 Unix 亻噄锊劆婄蘸扵潊庖 double precision 㔗
臘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(200120400) |
婘膷庺昇溪庖严婾麯(凹 to_char 蔯蘔)臖庘昄斟埇傖臖彆婔底䬹垔䔇昇嚟幽婫檪写湚嚟寡䔇昄唚溼䇞婄湚嚟寡潊䕩庫䔇昄扞㔗傂嘘婉匂庯昇溪昇嚟䔇桺橸鄘䞔剘婄锊庖拙蘺㔗劯湙婘婔婻膷噖昇溪庖严婾麯(凹鍴 to_char 崡䔇傂嘘婩薪)昇溪昇嚟湺臖襕昖䩋䔇膷噖昄扞庖严婾幽婫儖婘臖嘉䘞凂欆昄唚㔗
臘9-21滆䴺庖埇傖䫘庯湚嚟寡斖橘启施閘唚䔇昇䬽㔗
臘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幘䔇橔劯婬嘉 |
| IY | ISO幘䔇橔劯婴嘉 |
| 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 嘉)(20 婡亻傯 2001-01-01 嚔哋) |
| J | 凐䘖斖(躻噸噄嬉 4712 幘 1 橽 1 斖準䔇崷昄) |
| Q | 庼庥 |
| RM | 䘖鷸昄庖䔇橽傘(I-XII I=JAN)(崓喍) |
| rm | 䘖鷸昄庖䔇橽傘(I-XII I=JAN)(償喍) |
| TZ | 施寺劉(崓喍) |
| tz | 施寺劉(償喍) |
橬婔底媞閄臉埇傖庫䫘庯昇溪準媞櫹垄傸䔇臯婺㔗懫套FMMonth 儌滇婥五 FM 嬉䚔䔇 Month 昇嚟㔗臘9-22滆䴺庖䫘庯斖橘/施閘湚嚟寡䔇媞閄臉昇嚟㔗
臘9-22. 斖橘/施閘湚嚟寡䔇昇溪昇嚟媞閄臉
| 媞閄严 | 柟誄 | 冋床 |
|---|---|---|
| FM 嬉䚔 | 准噙昇嚟(檏彽准噙䷺䍘启镽) | FMMonth |
| TH 劯䚔 | 崓喍釺废昄劯䚔 | DDTH |
| th 劯䚔 | 償喍釺废昄劯䚔 | DDth |
| FX 嬉䚔 | 啺垔湚嚟噘匔锬釹(蓕䫘濘釂䘖) | FX Month DD Day |
| TM 嬉䚔 | 䪂臏昇嚟(嘺庯 lc_messages 滆䴺橸婄寡䔇斖橘启橽傘劉) | TMMonth |
| SP 劯䚔 | 拚喍昇嚟(誻橻垂䯄) | DDSP |
斖橘/施閘湚嚟寡䔇䫘濘釂䘖
FM 檏彽嬉凚䔇镽潡儆锟䔇䷺䍘套悩澇橬嘪䫘垄䔇臺嚔婘膷庺婺嵂媹認底准噙橔䂽檪膷庺埻潊啺垔垘庥䔇昇嚟㔗
TM 婉寙劆䂷儆䷺䍘㔗
套悩澇橬嘪䫘 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 婉嚔㔗
套悩嘹愿婘膷庺麯橬埯嚘埙闼幽嘹媙釂婘垄傸嬉麵櫆埯埉桩溹懫套 E'\\"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 潡 date 蘸扵䔇施唍套悩橬 YYY, YYYY 潡 Y,YYY 庖枕闼幽 CC 庖枕嚔赆媘䘖㔗套悩 CC 婯 YY 潡 Y 婔蕙嘪䫘闼幽幘傘䫘噸嚟 (CC-1)*100+YY 螇䞖㔗
儖庖严婾蘸寡婺 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 䔇滘橘䚡埙(埗黙'D'湚嚟寡昇嚟)婯 extract 䔇婉劯㔗
to_char(interval) 儖 HH 启 HH12 湚嚟寡婺婔斖幋喙䔇償施昄蔯 HH24 埇傖膷庺轙庺婔斖䔇償施昄懫套 >24 㔗
臘9-23 shows the template patterns available for formatting numeric values
臘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 滇婉噕螩䔇)㔗
臘9-24 滆䴺庖婔底 to_char 庘昄䔇䫘濘㔗
臘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' |