Home Explore Blog CI



postgresql

3rd chunk of `doc/src/sgml/datetime.sgml`
89472c77d3951fae88d891e6ec798d281c9138ec6445d4720000000100000fa0
 <literal>America/New_York</literal>, consider
<programlisting>
=&gt; SELECT '2018-03-11 02:30'::timestamptz;
      timestamptz
------------------------
 2018-03-11 03:30:00-04
(1 row)
</programlisting>
    Because that day was a spring-forward transition date in that time zone,
    there was no civil time instant 2:30AM; clocks jumped forward from 2AM
    EST to 3AM EDT.  <productname>PostgreSQL</productname> interprets the
    given time as if it were standard time (UTC-5), which then renders as
    3:30AM EDT (UTC-4).
   </para>

   <para>
    Conversely, consider the behavior during a fall-back transition:
<programlisting>
=&gt; SELECT '2018-11-04 01:30'::timestamptz;
      timestamptz
------------------------
 2018-11-04 01:30:00-05
(1 row)
</programlisting>
    On that date, there were two possible interpretations of 1:30AM; there
    was 1:30AM EDT, and then an hour later after clocks jumped back from
    2AM EDT to 1AM EST, there was 1:30AM EST.
    Again, <productname>PostgreSQL</productname> interprets the given time
    as if it were standard time (UTC-5).  We can force the other
    interpretation by specifying daylight-savings time:
<programlisting>
=&gt; SELECT '2018-11-04 01:30 EDT'::timestamptz;
      timestamptz
------------------------
 2018-11-04 01:30:00-04
(1 row)
</programlisting>
   </para>

   <para>
    The precise rule that is applied in such cases is that an invalid
    timestamp that appears to fall within a jump-forward daylight savings
    transition is assigned the UTC offset that prevailed in the time zone
    just before the transition, while an ambiguous timestamp that could fall
    on either side of a jump-back transition is assigned the UTC offset that
    prevailed just after the transition.  In most time zones this is
    equivalent to saying that <quote>the standard-time interpretation is
    preferred when in doubt</quote>.
   </para>

   <para>
    In all cases, the UTC offset associated with a timestamp can be
    specified explicitly, using either a numeric UTC offset or a time zone
    abbreviation that corresponds to a fixed UTC offset.  The rule just
    given applies only when it is necessary to infer a UTC offset for a time
    zone in which the offset varies.
   </para>
  </sect1>


  <sect1 id="datetime-keywords">
   <title>Date/Time Key Words</title>

   <para>
    <xref linkend="datetime-month-table"/> shows the tokens that are
    recognized as names of months.
   </para>

    <table id="datetime-month-table">
     <title>Month Names</title>
     <tgroup cols="2">
      <thead>
       <row>
        <entry>Month</entry>
        <entry>Abbreviations</entry>
       </row>
      </thead>
      <tbody>
       <row>
        <entry>January</entry>
        <entry>Jan</entry>
       </row>
       <row>
        <entry>February</entry>
        <entry>Feb</entry>
       </row>
       <row>
        <entry>March</entry>
        <entry>Mar</entry>
       </row>
       <row>
        <entry>April</entry>
        <entry>Apr</entry>
       </row>
       <row>
        <entry>May</entry>
        <entry></entry>
       </row>
       <row>
        <entry>June</entry>
        <entry>Jun</entry>
       </row>
       <row>
        <entry>July</entry>
        <entry>Jul</entry>
       </row>
       <row>
        <entry>August</entry>
        <entry>Aug</entry>
       </row>
       <row>
        <entry>September</entry>
        <entry>Sep, Sept</entry>
       </row>
       <row>
        <entry>October</entry>
        <entry>Oct</entry>
       </row>
       <row>
        <entry>November</entry>
        <entry>Nov</entry>
       </row>
       <row>
        <entry>December</entry>
        <entry>Dec</entry>
       </row>
      </tbody>
     </tgroup>
    </table>

    <para>
     <xref linkend="datetime-dow-table"/> shows the tokens that are
     recognized as names of days of the week.
    </para>

     <table id="datetime-dow-table">
      <title>Day of the Week Names</title>
      <tgroup cols="2">
  

Title: PostgreSQL Date/Time Parsing and Time Zone Handling
Summary
PostgreSQL handles date and time input strings by applying rules to resolve ambiguities and errors, particularly during daylight-savings-time transitions, and also recognizes specific key words for months and days of the week to facilitate date and time parsing.