MINI SHELL

Server : Apache/2.2.2 (Fedora)
System : Linux App1.pathumtani.go.th 2.6.20-1.2320.fc5smp #1 SMP Tue Jun 12 19:40:16 EDT 2007 i686
User : apache ( 48)
PHP Version : 5.2.9
Disable Function : NONE
Directory :  /proc/self/root/usr/share/doc/postgresql-8.1.9/html/

Upload File :
current_dir [ Writeable ] document_root [ Writeable ]

 

Current File : //proc/self/root/usr/share/doc/postgresql-8.1.9/html/functions-formatting.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Data Type Formatting Functions</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.1.9 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Functions and Operators"
HREF="functions.html"><LINK
REL="PREVIOUS"
TITLE="Pattern Matching"
HREF="functions-matching.html"><LINK
REL="NEXT"
TITLE="Date/Time Functions and Operators"
HREF="functions-datetime.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2007-04-20T04:40:08"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.1.9 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-matching.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 9. Functions and Operators</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-datetime.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="FUNCTIONS-FORMATTING"
>9.8. Data Type Formatting Functions</A
></H1
><A
NAME="AEN9934"
></A
><A
NAME="AEN9936"
></A
><A
NAME="AEN9938"
></A
><A
NAME="AEN9940"
></A
><A
NAME="AEN9942"
></A
><P
>    The <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> 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.
    <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-TABLE"
>Table 9-20</A
> 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.
   </P
><P
>    The <CODE
CLASS="FUNCTION"
>to_timestamp</CODE
> function can also take a single 
    <TT
CLASS="TYPE"
>double precision</TT
> argument to convert from Unix epoch to 
    <TT
CLASS="TYPE"
>timestamp with time zone</TT
>.
    (<TT
CLASS="TYPE"
>Integer</TT
> Unix epochs are implicitly cast to 
    <TT
CLASS="TYPE"
>double precision</TT
>.)
   </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-TABLE"
></A
><P
><B
>Table 9-20. Formatting Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Return Type</TH
><TH
>Description</TH
><TH
>Example</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_char</CODE
>(<TT
CLASS="TYPE"
>timestamp</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>convert time stamp to string</TD
><TD
><TT
CLASS="LITERAL"
>to_char(current_timestamp, 'HH12:MI:SS')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_char</CODE
>(<TT
CLASS="TYPE"
>interval</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>convert interval to string</TD
><TD
><TT
CLASS="LITERAL"
>to_char(interval '15h&nbsp;2m&nbsp;12s', 'HH24:MI:SS')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_char</CODE
>(<TT
CLASS="TYPE"
>int</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>convert integer to string</TD
><TD
><TT
CLASS="LITERAL"
>to_char(125, '999')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_char</CODE
>(<TT
CLASS="TYPE"
>double precision</TT
>,
        <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>convert real/double precision to string</TD
><TD
><TT
CLASS="LITERAL"
>to_char(125.8::real, '999D9')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_char</CODE
>(<TT
CLASS="TYPE"
>numeric</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>text</TT
></TD
><TD
>convert numeric to string</TD
><TD
><TT
CLASS="LITERAL"
>to_char(-125.8, '999D99S')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_date</CODE
>(<TT
CLASS="TYPE"
>text</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>date</TT
></TD
><TD
>convert string to date</TD
><TD
><TT
CLASS="LITERAL"
>to_date('05&nbsp;Dec&nbsp;2000', 'DD&nbsp;Mon&nbsp;YYYY')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_timestamp</CODE
>(<TT
CLASS="TYPE"
>text</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>timestamp with time zone</TT
></TD
><TD
>convert string to time stamp</TD
><TD
><TT
CLASS="LITERAL"
>to_timestamp('05&nbsp;Dec&nbsp;2000', 'DD&nbsp;Mon&nbsp;YYYY')</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_timestamp</CODE
>(<TT
CLASS="TYPE"
>double precision</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>timestamp with time zone</TT
></TD
><TD
>convert UNIX epoch to time stamp</TD
><TD
><TT
CLASS="LITERAL"
>to_timestamp(200120400)</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>to_number</CODE
>(<TT
CLASS="TYPE"
>text</TT
>, <TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>numeric</TT
></TD
><TD
>convert string to numeric</TD
><TD
><TT
CLASS="LITERAL"
>to_number('12,454.8-', '99G999D9S')</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>    In an output template string (for <CODE
CLASS="FUNCTION"
>to_char</CODE
>), there are certain patterns that are
    recognized and replaced with appropriately-formatted data from the value
    to be formatted.  Any text that is not a template pattern is simply
    copied verbatim.  Similarly, in an input template string (for anything but <CODE
CLASS="FUNCTION"
>to_char</CODE
>), template patterns
    identify the parts of the input data string to be looked at and the
    values to be found there.
   </P
><P
>   <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE"
>Table 9-21</A
> shows the
   template patterns available for formatting date and time values.
  </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-DATETIME-TABLE"
></A
><P
><B
>Table 9-21. Template Patterns for Date/Time Formatting</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Pattern</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>HH</TT
></TD
><TD
>hour of day (01-12)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>HH12</TT
></TD
><TD
>hour of day (01-12)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>HH24</TT
></TD
><TD
>hour of day (00-23)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MI</TT
></TD
><TD
>minute (00-59)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>SS</TT
></TD
><TD
>second (00-59)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MS</TT
></TD
><TD
>millisecond (000-999)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>US</TT
></TD
><TD
>microsecond (000000-999999)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>SSSS</TT
></TD
><TD
>seconds past midnight (0-86399)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>AM</TT
> or <TT
CLASS="LITERAL"
>A.M.</TT
> or
        <TT
CLASS="LITERAL"
>PM</TT
> or <TT
CLASS="LITERAL"
>P.M.</TT
></TD
><TD
>meridian indicator (uppercase)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>am</TT
> or <TT
CLASS="LITERAL"
>a.m.</TT
> or
        <TT
CLASS="LITERAL"
>pm</TT
> or <TT
CLASS="LITERAL"
>p.m.</TT
></TD
><TD
>meridian indicator (lowercase)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Y,YYY</TT
></TD
><TD
>year (4 and more digits) with comma</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>YYYY</TT
></TD
><TD
>year (4 and more digits)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>YYY</TT
></TD
><TD
>last 3 digits of year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>YY</TT
></TD
><TD
>last 2 digits of year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Y</TT
></TD
><TD
>last digit of year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>IYYY</TT
></TD
><TD
>ISO year (4 and more digits)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>IYY</TT
></TD
><TD
>last 3 digits of ISO year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>IY</TT
></TD
><TD
>last 2 digits of ISO year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>I</TT
></TD
><TD
>last digits of ISO year</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>BC</TT
> or <TT
CLASS="LITERAL"
>B.C.</TT
> or
        <TT
CLASS="LITERAL"
>AD</TT
> or <TT
CLASS="LITERAL"
>A.D.</TT
></TD
><TD
>era indicator (uppercase)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>bc</TT
> or <TT
CLASS="LITERAL"
>b.c.</TT
> or
        <TT
CLASS="LITERAL"
>ad</TT
> or <TT
CLASS="LITERAL"
>a.d.</TT
></TD
><TD
>era indicator (lowercase)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MONTH</TT
></TD
><TD
>full uppercase month name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Month</TT
></TD
><TD
>full mixed-case month name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>month</TT
></TD
><TD
>full lowercase month name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MON</TT
></TD
><TD
>abbreviated uppercase month name (3 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Mon</TT
></TD
><TD
>abbreviated mixed-case month name (3 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>mon</TT
></TD
><TD
>abbreviated lowercase month name (3 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MM</TT
></TD
><TD
>month number (01-12)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>DAY</TT
></TD
><TD
>full uppercase day name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Day</TT
></TD
><TD
>full mixed-case day name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>day</TT
></TD
><TD
>full lowercase day name (blank-padded to 9 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>DY</TT
></TD
><TD
>abbreviated uppercase day name (3 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Dy</TT
></TD
><TD
>abbreviated mixed-case day name (3 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>dy</TT
></TD
><TD
>abbreviated lowercase day name (3 chars)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>DDD</TT
></TD
><TD
>day of year (001-366)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>DD</TT
></TD
><TD
>day of month (01-31)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>D</TT
></TD
><TD
>day of week (1-7; Sunday is 1)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>W</TT
></TD
><TD
>week of month (1-5) (The first week starts on the first day of the month.)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>WW</TT
></TD
><TD
>week number of year (1-53) (The first week starts on the first day of the year.)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>IW</TT
></TD
><TD
>ISO week number of year (The first Thursday of the new year is in week 1.)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>CC</TT
></TD
><TD
>century (2 digits)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>J</TT
></TD
><TD
>Julian Day (days since January 1, 4712 BC)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>Q</TT
></TD
><TD
>quarter</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>RM</TT
></TD
><TD
>month in Roman numerals (I-XII; I=January) (uppercase)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>rm</TT
></TD
><TD
>month in Roman numerals (i-xii; i=January) (lowercase)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>TZ</TT
></TD
><TD
>time-zone name (uppercase)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>tz</TT
></TD
><TD
>time-zone name (lowercase)</TD
></TR
></TBODY
></TABLE
></DIV
><P
>    Certain modifiers may be applied to any template pattern to alter its
    behavior.  For example, <TT
CLASS="LITERAL"
>FMMonth</TT
>
    is the <TT
CLASS="LITERAL"
>Month</TT
> pattern with the
    <TT
CLASS="LITERAL"
>FM</TT
> modifier.
    <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE"
>Table 9-22</A
> shows the
    modifier patterns for date/time formatting.
   </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE"
></A
><P
><B
>Table 9-22. Template Pattern Modifiers for Date/Time Formatting</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><THEAD
><TR
><TH
>Modifier</TH
><TH
>Description</TH
><TH
>Example</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>FM</TT
> prefix</TD
><TD
>fill mode (suppress padding blanks and zeroes)</TD
><TD
><TT
CLASS="LITERAL"
>FMMonth</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>TH</TT
> suffix</TD
><TD
>uppercase ordinal number suffix</TD
><TD
><TT
CLASS="LITERAL"
>DDTH</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>th</TT
> suffix</TD
><TD
>lowercase ordinal number suffix</TD
><TD
><TT
CLASS="LITERAL"
>DDth</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>FX</TT
> prefix</TD
><TD
>fixed format global option (see usage notes)</TD
><TD
><TT
CLASS="LITERAL"
>FX&nbsp;Month&nbsp;DD&nbsp;Day</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>SP</TT
> suffix</TD
><TD
>spell mode (not yet implemented)</TD
><TD
><TT
CLASS="LITERAL"
>DDSP</TT
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>    Usage notes for date/time formatting:

    <P
></P
></P><UL
><LI
><P
>       <TT
CLASS="LITERAL"
>FM</TT
> suppresses leading zeroes and trailing blanks
       that would otherwise be added to make the output of a pattern be
       fixed-width.
      </P
></LI
><LI
><P
>       <CODE
CLASS="FUNCTION"
>to_timestamp</CODE
> and <CODE
CLASS="FUNCTION"
>to_date</CODE
>
       skip multiple blank spaces in the input string if the <TT
CLASS="LITERAL"
>FX</TT
> option 
       is not used. <TT
CLASS="LITERAL"
>FX</TT
> must be specified as the first item
       in the template.  For example 
       <TT
CLASS="LITERAL"
>to_timestamp('2000&nbsp;&nbsp;&nbsp;&nbsp;JUN', 'YYYY MON')</TT
> is correct, but
       <TT
CLASS="LITERAL"
>to_timestamp('2000&nbsp;&nbsp;&nbsp;&nbsp;JUN', 'FXYYYY MON')</TT
> returns an error,
       because <CODE
CLASS="FUNCTION"
>to_timestamp</CODE
> expects one space only.
      </P
></LI
><LI
><P
>       Ordinary text is allowed in <CODE
CLASS="FUNCTION"
>to_char</CODE
>
       templates and will be output literally.  You can put a substring
       in double quotes to force it to be interpreted as literal text
       even if it contains pattern key words.  For example, in
       <TT
CLASS="LITERAL"
>'"Hello Year "YYYY'</TT
>, the <TT
CLASS="LITERAL"
>YYYY</TT
>
       will be replaced by the year data, but the single <TT
CLASS="LITERAL"
>Y</TT
> in <TT
CLASS="LITERAL"
>Year</TT
>
       will not be.
      </P
></LI
><LI
><P
>       If you want to have a double quote in the output you must
       precede it with a backslash, for example <TT
CLASS="LITERAL"
>'\\"YYYY
       Month\\"'</TT
>. 
       (Two backslashes are necessary because the backslash already
       has a special meaning in a string constant.)
      </P
></LI
><LI
><P
>       The <TT
CLASS="LITERAL"
>YYYY</TT
> conversion from string to <TT
CLASS="TYPE"
>timestamp</TT
> or
       <TT
CLASS="TYPE"
>date</TT
> has a restriction if you use a year with more than 4 digits. You must
       use some non-digit character or template after <TT
CLASS="LITERAL"
>YYYY</TT
>,
       otherwise the year is always interpreted as 4 digits. For example
       (with the year 20000):
       <TT
CLASS="LITERAL"
>to_date('200001131', 'YYYYMMDD')</TT
> will be 
       interpreted as a 4-digit year; instead use a non-digit 
       separator after the year, like
       <TT
CLASS="LITERAL"
>to_date('20000-1131', 'YYYY-MMDD')</TT
> or
       <TT
CLASS="LITERAL"
>to_date('20000Nov31', 'YYYYMonDD')</TT
>.
      </P
></LI
><LI
><P
>       In conversions from string to <TT
CLASS="TYPE"
>timestamp</TT
> or
       <TT
CLASS="TYPE"
>date</TT
>, the <TT
CLASS="LITERAL"
>CC</TT
> field is ignored if there
       is a <TT
CLASS="LITERAL"
>YYY</TT
>, <TT
CLASS="LITERAL"
>YYYY</TT
> or
       <TT
CLASS="LITERAL"
>Y,YYY</TT
> field. If <TT
CLASS="LITERAL"
>CC</TT
> is used with
       <TT
CLASS="LITERAL"
>YY</TT
> or <TT
CLASS="LITERAL"
>Y</TT
> then the year is computed
       as <TT
CLASS="LITERAL"
>(CC-1)*100+YY</TT
>.
      </P
></LI
><LI
><P
>       Millisecond (<TT
CLASS="LITERAL"
>MS</TT
>) and microsecond (<TT
CLASS="LITERAL"
>US</TT
>)
       values in a conversion from string to <TT
CLASS="TYPE"
>timestamp</TT
> are used as part of the
       seconds after the decimal point. For example 
       <TT
CLASS="LITERAL"
>to_timestamp('12:3', 'SS:MS')</TT
> is not 3 milliseconds,
       but 300, because the conversion counts it as 12 + 0.3 seconds.
       This means for the format <TT
CLASS="LITERAL"
>SS:MS</TT
>, the input values
       <TT
CLASS="LITERAL"
>12:3</TT
>, <TT
CLASS="LITERAL"
>12:30</TT
>, and <TT
CLASS="LITERAL"
>12:300</TT
> specify the
       same number of milliseconds. To get three milliseconds, one must use
       <TT
CLASS="LITERAL"
>12:003</TT
>, which the conversion counts as
       12 + 0.003 = 12.003 seconds.
      </P
><P
>       Here is a more 
       complex example: 
       <TT
CLASS="LITERAL"
>to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US')</TT
>
       is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
       1230 microseconds = 2.021230 seconds. 
      </P
></LI
><LI
><P
><CODE
CLASS="FUNCTION"
>to_char</CODE
>'s day of the week numbering
        (see the 'D' formatting pattern) is different from that of the 
        <CODE
CLASS="FUNCTION"
>extract</CODE
> function.
      </P
></LI
><LI
><P
><CODE
CLASS="FUNCTION"
>to_char(interval)</CODE
> formats <TT
CLASS="LITERAL"
>HH</TT
> and 
        <TT
CLASS="LITERAL"
>HH12</TT
> as hours in a single day, while <TT
CLASS="LITERAL"
>HH24</TT
>
        can output hours exceeding a single day, e.g. &gt;24.
      </P
></LI
></UL
><P>
   </P
><P
>   <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-NUMERIC-TABLE"
>Table 9-23</A
> shows the
   template patterns available for formatting numeric values.
  </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-NUMERIC-TABLE"
></A
><P
><B
>Table 9-23. Template Patterns for Numeric Formatting</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Pattern</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>9</TT
></TD
><TD
>value with the specified number of digits</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>0</TT
></TD
><TD
>value with leading zeros</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>.</TT
> (period)</TD
><TD
>decimal point</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>,</TT
> (comma)</TD
><TD
>group (thousand) separator</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>PR</TT
></TD
><TD
>negative value in angle brackets</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>S</TT
></TD
><TD
>sign anchored to number (uses locale)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>L</TT
></TD
><TD
>currency symbol (uses locale)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>D</TT
></TD
><TD
>decimal point (uses locale)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>G</TT
></TD
><TD
>group separator (uses locale)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>MI</TT
></TD
><TD
>minus sign in specified position (if number &lt; 0)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>PL</TT
></TD
><TD
>plus sign in specified position (if number &gt; 0)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>SG</TT
></TD
><TD
>plus/minus sign in specified position</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>RN</TT
></TD
><TD
>roman numeral (input between 1 and 3999)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>TH</TT
> or <TT
CLASS="LITERAL"
>th</TT
></TD
><TD
>ordinal number suffix</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>V</TT
></TD
><TD
>shift specified number of digits (see notes)</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>EEEE</TT
></TD
><TD
>scientific notation (not implemented yet)</TD
></TR
></TBODY
></TABLE
></DIV
><P
>    Usage notes for numeric formatting:

    <P
></P
></P><UL
><LI
><P
>       A sign formatted using <TT
CLASS="LITERAL"
>SG</TT
>, <TT
CLASS="LITERAL"
>PL</TT
>, or
       <TT
CLASS="LITERAL"
>MI</TT
> is not anchored to
       the number; for example,
       <TT
CLASS="LITERAL"
>to_char(-12, 'S9999')</TT
> produces <TT
CLASS="LITERAL"
>'&nbsp;&nbsp;-12'</TT
>,
       but <TT
CLASS="LITERAL"
>to_char(-12, 'MI9999')</TT
> produces <TT
CLASS="LITERAL"
>'-&nbsp;&nbsp;12'</TT
>.
       The Oracle implementation does not allow the use of
       <TT
CLASS="LITERAL"
>MI</TT
> ahead of <TT
CLASS="LITERAL"
>9</TT
>, but rather
       requires that <TT
CLASS="LITERAL"
>9</TT
> precede
       <TT
CLASS="LITERAL"
>MI</TT
>.
      </P
></LI
><LI
><P
>       <TT
CLASS="LITERAL"
>9</TT
> results in a value with the same number of 
       digits as there are <TT
CLASS="LITERAL"
>9</TT
>s. If a digit is
       not available it outputs a space.
      </P
></LI
><LI
><P
>       <TT
CLASS="LITERAL"
>TH</TT
> does not convert values less than zero
       and does not convert fractional numbers.
      </P
></LI
><LI
><P
>       <TT
CLASS="LITERAL"
>PL</TT
>, <TT
CLASS="LITERAL"
>SG</TT
>, and
       <TT
CLASS="LITERAL"
>TH</TT
> are <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
       extensions. 
      </P
></LI
><LI
><P
>       <TT
CLASS="LITERAL"
>V</TT
> effectively
       multiplies the input values by
       <TT
CLASS="LITERAL"
>10^<TT
CLASS="REPLACEABLE"
><I
>n</I
></TT
></TT
>, where
       <TT
CLASS="REPLACEABLE"
><I
>n</I
></TT
> is the number of digits following
       <TT
CLASS="LITERAL"
>V</TT
>. 
       <CODE
CLASS="FUNCTION"
>to_char</CODE
> does not support the use of
       <TT
CLASS="LITERAL"
>V</TT
> combined with a decimal point.
       (E.g., <TT
CLASS="LITERAL"
>99.9V99</TT
> is not allowed.)
      </P
></LI
></UL
><P>
   </P
><P
>   <A
HREF="functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE"
>Table 9-24</A
> shows some
   examples of the use of the <CODE
CLASS="FUNCTION"
>to_char</CODE
> function.
  </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-FORMATTING-EXAMPLES-TABLE"
></A
><P
><B
>Table 9-24. <CODE
CLASS="FUNCTION"
>to_char</CODE
> Examples</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><THEAD
><TR
><TH
>Expression</TH
><TH
>Result</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(current_timestamp, 'Day,&nbsp;DD&nbsp;&nbsp;HH12:MI:SS')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'Tuesday&nbsp;&nbsp;,&nbsp;06&nbsp;&nbsp;05:39:18'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(current_timestamp, 'FMDay,&nbsp;FMDD&nbsp;&nbsp;HH12:MI:SS')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'Tuesday,&nbsp;6&nbsp;&nbsp;05:39:18'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-0.1, '99.99')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;&nbsp;-.10'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-0.1, 'FM9.99')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'-.1'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(0.1, '0.9')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;0.1'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(12, '9990999.9')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;&nbsp;&nbsp;&nbsp;0012.0'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(12, 'FM9990999.9')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'0012.'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, '999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, '999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'-485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, '9&nbsp;9&nbsp;9')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;4&nbsp;8&nbsp;5'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(1485, '9,999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;1,485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(1485, '9G999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;1&nbsp;485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(148.5, '999.999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;148.500'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(148.5, 'FM999.999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'148.5'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(148.5, 'FM999.990')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'148.500'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(148.5, '999D999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;148,500'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(3148.5, '9G999D999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;3&nbsp;148,500'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, '999S')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'485-'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, '999MI')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'485-'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, '999MI')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'485&nbsp;'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'FM999MI')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'PL999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'+485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'SG999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'+485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, 'SG999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'-485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, '9SG99')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'4-85'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(-485, '999PR')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&lt;485&gt;'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'L999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'DM&nbsp;485</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'RN')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CDLXXXV'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, 'FMRN')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'CDLXXXV'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(5.2, 'FMRN')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'V'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(482, '999th')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;482nd'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485, '"Good&nbsp;number:"999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'Good&nbsp;number:&nbsp;485'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(485.8, '"Pre:"999"&nbsp;Post:"&nbsp;.999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'Pre:&nbsp;485&nbsp;Post:&nbsp;.800'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(12, '99V999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;12000'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(12.4, '99V999')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;12400'</TT
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
>to_char(12.45, '99V9')</TT
></TD
><TD
><TT
CLASS="LITERAL"
>'&nbsp;125'</TT
></TD
></TR
></TBODY
></TABLE
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-matching.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-datetime.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Pattern Matching</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="functions.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Date/Time Functions and Operators</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>

Anon7 - 2021