Home • Explore • Blog • CI
☰



postgresql
100th chunk of `doc/src/sgml/func.sgml`
5cd80b7cd7d02709d1b97c7a481ce6eeebd7c5cba5d8bdf40000000100000fa2
 <type>text</type>, <type>text</type> )
        <returnvalue>timestamp with time zone</returnvalue>
       </para>
       <para>
        Converts string to time stamp according to the given format.
        (See also <function>to_timestamp(double precision)</function> in
        <xref linkend="functions-datetime-table"/>.)
       </para>
       <para>
        <literal>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</literal>
        <returnvalue>2000-12-05 00:00:00-05</returnvalue>
       </para></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <tip>
    <para>
     <function>to_timestamp</function> and <function>to_date</function>
     exist to handle input formats that cannot be converted by
     simple casting.  For most standard date/time formats, simply casting the
     source string to the required data type works, and is much easier.
     Similarly, <function>to_number</function> is unnecessary for standard numeric
     representations.
    </para>
   </tip>

   <para>
    In a <function>to_char</function> output template string, there are certain
    patterns that are recognized and replaced with appropriately-formatted
    data based on the given value.  Any text that is not a template pattern is
    simply copied verbatim.  Similarly, in an input template string (for the
    other functions), template patterns identify the values to be supplied by
    the input data string.  If there are characters in the template string
    that are not template patterns, the corresponding characters in the input
    data string are simply skipped over (whether or not they are equal to the
    template string characters).
   </para>

  <para>
   <xref linkend="functions-formatting-datetime-table"/> shows the
   template patterns available for formatting date and time values.
  </para>

    <table id="functions-formatting-datetime-table">
     <title>Template Patterns for Date/Time Formatting</title>
     <tgroup cols="2">
      <thead>
       <row>
        <entry>Pattern</entry>
        <entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
        <entry><literal>HH</literal></entry>
        <entry>hour of day (01&ndash;12)</entry>
       </row>
       <row>
        <entry><literal>HH12</literal></entry>
        <entry>hour of day (01&ndash;12)</entry>
       </row>
       <row>
        <entry><literal>HH24</literal></entry>
        <entry>hour of day (00&ndash;23)</entry>
       </row>
       <row>
        <entry><literal>MI</literal></entry>
        <entry>minute (00&ndash;59)</entry>
       </row>
       <row>
        <entry><literal>SS</literal></entry>
        <entry>second (00&ndash;59)</entry>
       </row>
       <row>
        <entry><literal>MS</literal></entry>
        <entry>millisecond (000&ndash;999)</entry>
       </row>
       <row>
        <entry><literal>US</literal></entry>
        <entry>microsecond (000000&ndash;999999)</entry>
       </row>
       <row>
        <entry><literal>FF1</literal></entry>
        <entry>tenth of second (0&ndash;9)</entry>
       </row>
       <row>
        <entry><literal>FF2</literal></entry>
        <entry>hundredth of second (00&ndash;99)</entry>
       </row>
       <row>
        <entry><literal>FF3</literal></entry>
        <entry>millisecond (000&ndash;999)</entry>
       </row>
       <row>
        <entry><literal>FF4</literal></entry>
        <entry>tenth of a millisecond (0000&ndash;9999)</entry>
       </row>
       <row>
        <entry><literal>FF5</literal></entry>
        <entry>hundredth of a millisecond (00000&ndash;99999)</entry>
       </row>
       <row>
        <entry><literal>FF6</literal></entry>
        <entry>microsecond (000000&ndash;999999)</entry>
       </row>
       <row>
        <entry><literal>SSSS</literal>, <literal>SSSSS</literal></entry>
        <entry>seconds past midnight (0&ndash;86399)</entry>
       </row>
       <row>
        <entry><literal>AM</literal>, <literal>am</literal>,
        <literal>PM</literal> or
Title: PostgreSQL Formatting Functions: to_timestamp and Date/Time Template Patterns
Summary
This section covers the `to_timestamp` function, which converts a string to a timestamp with time zone based on a given format. It also emphasizes that for standard date/time formats, simple casting is often easier than using `to_timestamp` and `to_date`. The section then discusses the usage of template patterns in `to_char` for formatting data and in other functions for identifying values in input data strings. A table lists the available template patterns for formatting date and time values, including `HH`, `MI`, `SS`, `MS`, `US`, `FF1-FF6`, and `SSSS`.