From b866d2e2d79416f8497e4dffa7e800298d018f6c Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 25 Jan 2000 23:53:56 +0000 Subject: as attache of this mail is patch (to the main tree) with to_char's family functions. Contain: conversion from a datetype to formatted text: to_char( datetime, text) to_char( timestamp, text) to_char( int4, text) to_char( int8, text) to_char( float4, text) to_char( float8, text) to_char( numeric, text) vice versa: to_date ( text, text) to_datetime ( text, text) to_timestamp ( text, text) to_number ( text, text) (convert to numeric) PostgreSQL to_char is very compatible with Oracle's to_char(), but not total exactly (now). Small differentions are in number formating. It will fix in next to_char() version. ! If will this patch aplly to the main tree, must be delete the current to_char version in contrib (directory "dateformat" and note in contrib's README), this patch not erase it (sorry Bruce). The patch patching files: doc/src/sgml/func.sgml ^^^^^^^^ Hmm, I'm not sure if my English... :( Check it anyone (volunteer)? Thomas, it is right? SGML is not my primary lang and compile the current PG docs tree is very happy job (hard variables setting in docs/sgml/Makefile --> HSTYLE= /home/users/t/thomas/.... :-) What add any definition to global configure.in and set Makefiles in docs tree via ./configure? src/backend/utils/adt/Makefile src/backend/utils/adt/formatting.c src/include/catalog/pg_proc.h src/include/utils/formatting.h Karel Zak http://home.zf.jcu.cz/~zakkr/ --- doc/src/sgml/func.sgml | 541 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 541 insertions(+) (limited to 'doc/src') diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 2bfec9272e5..55cadab4450 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -420,6 +420,547 @@ + + + + Formatting Functions + + + Author + + Written by + Karel Zak + on 2000-01-24. + + + + + Formatting functions provide a powerful set of tools for converting + various datetypes (date/time, int, float, numeric) to formatted strings + and reverse convert from formatted strings to original datetypes. + + + + + Formatting Functions + + + + Function + Returns + Description + Example + + + + + to_char(datetime, text) + text + convert datetime to string + to_char('now'::datetime, 'HH12:MI:SS') + + + to_char(timestamp, text) + text + convert timestamp to string + to_char( now(), 'HH12:MI:SS') + + + to_char(int, text) + text + convert int4/int8 to string + to_char(125, '999') + + + to_char(float, text) + text + convert float4/float8 to string + to_char(125.8, '999D9') + + + to_char(numeric, text) + text + convert numeric to string + to_char(-125.8, '999D99S') + + + to_datetime(text, text) + datetime + convert string to datetime + to_datetime('05 Dec 2000 13', 'DD Mon YYYY HH') + + + to_date(text, text) + date + convert string to date + to_date('05 Dec 2000', 'DD Mon YYYY') + + + to_timestamp(text, text) + date + convert string to timestamp + to_timestamp('05 Dec 2000', 'DD Mon YYYY') + + + to_number(text, text) + numeric + convert string to numeric + to_number('12,454.8-', '99G999D9S') + + + +
+ + + + For all formatting functions is second argument format-picture. + + + + + Format-pictures for datetime to_char() version. + + + + Format-picture + Description + + + + + HH + hour of day (01-12) + + + HH12 + hour of day (01-12) + + + MI + minute (00-59) + + + SS + socond (00-59) + + + SSSS + seconds past midnight (0-86399) + + + Y,YYY + year (4 and more digits) with comma + + + YYYY + year (4 and more digits) + + + YYY + last 3 digits of year + + + YY + last 2 digits of year + + + Y + last digit of year + + + MONTH + full month name (9-letters) - all characters is upper + + + Month + full month name (9-letters) - first character is upper + + + month + full month name (9-letters) - all characters is lower + + + MON + abbreviated month name (3-letters) - all characters is upper + + + Mon + abbreviated month name (3-letters) - first character is upper + + + mon + abbreviated month name (3-letters) - all characters is lower + + + MM + month (01-12) + + + DAY + full day name (9-letters) - all characters is upper + + + Day + full day name (9-letters) - first character is upper + + + day + full day name (9-letters) - all characters is lower + + + DY + abbreviated day name (3-letters) - all characters is upper + + + Dy + abbreviated day name (3-letters) - first character is upper + + + dy + abbreviated day name (3-letters) - all characters is upper + + + DDD + day of year (001-366) + + + DD + day of month (01-31) + + + D + day of week (1-7; SUN=1) + + + W + week of month + + + WW + week number of year + + + CC + century (2-digits) + + + J + julian day (days since January 1, 4712 BC) + + + Q + quarter + + + RM + month in roman numeral (I-XII; I=JAN) + + + +
+ + + + All format-pictures allow use suffixes (postfix / prefix). The suffix is + always valid for near format-picture. The 'FX' is global prefix only. + + + + + Suffixes for format-pictures for datetime to_char() version. + + + + Suffix + Description + Example + + + + + FM + fill mode - prefix + FMMonth + + + TH + upper ordinal number - postfix + DDTH + + + th + lower ordinal number - postfix + DDTH + + + FX + FX - (Fixed format) global format-picture switch. + the TO_DATETIME / TO_DATA skip blank space if this option is + not use. Must by used as first item in formt-picture. + FX Month DD Day + + + SP + spell mode (not implement now) + DDSP + + + +
+ + + + '\' - must be use as double \\, example '\\HH\\MI\\SS' + + + '"' - string between a quotation marks is skipen and not is parsed. + If you want write '"' to output you must use \\", exapmle '\\"YYYY Month\\"'. + + + text - the PostgreSQL's to_char() support text without '"', but string + between a quotation marks is fastly and you have guarantee, that a text + not will interpreted as a keyword (format-picture), exapmle '"Hello Year: "YYYY'. + + + + + Format-pictures for number (int/float/numeric) to_char() version. + + + + Format-picture + Description + + + + + 9 + return value with the specified number of digits, and if digit is + not available use blank space + + + 0 + as 9, but instead blank space use zero + + + . (period) + decimal point + + + , (comma) + group (thousand) separator + + + PR + return negative value in angle brackets + + + S + return negatice value with minus sign (use locales) + + + L + currency symbol (use locales) + + + D + decimal point (use locales) + + + G + group separator (use locales) + + + MI + return minus sign on specified position (if number < 0) + + + PL + return plus sign on specified position (if number > 0) + + + RN + return number as roman number (number must be between 1 and 3999) + + + TH or th + convert number to ordinal number (not convert numbers under zero and decimal numbers) + + + V + arg1 * (10 ^ n); - return a value multiplied by 10^n (where 'n' is number of '9's after the 'V'). + The to_char() not support use 'V' and decimal poin together, example "99.9V99". + + + EEEE + science numbers. Now not supported. + + + +
+ + + + The PostgreSQL to_char() not support absurd to_char(0.1, '99.99') + --> ' .10' format. + + + + + The to_char() examples. + + + + Input + Output + + + + + to_char(now(), 'Day, HH12:MI:SS') + 'Tuesday , 05:39:18' + + + to_char(now(), 'FMDay, HH12:MI:SS') + 'Tuesday, 05:39:18' + + + to_char( 0.1, '99.99') + ' 0.10' + + + to_char( 0.1, '0.9') + ' 0.1' + + + to_char( 0.1, '090.9') + ' 000.1' + + + to_char( 485, '999') + ' 485' + + + to_char( -485, '999') + '-485' + + + to_char( 485, '09999') + ' 00485' + + + to_char( 485, 'FM09999') + '00485' + + + to_char( 485, 'FM999') + '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, '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, '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-decimal:"999" Post-decimal:" .999') + 'Pre-decimal: 485 Post-decimal: .800' + + + to_char( 12, '99V999') + ' 12000' + + + to_char( 12.4, '99V999') + ' 12400' + + + to_char( 12.45, '99V9') + ' 125' + + + +
+ + + + + + Geometric Functions -- cgit v1.2.3