Home • Explore • Blog • CI
☰



postgresql
28th chunk of `doc/src/sgml/datatype.sgml`
dd1bfdf36f87cac9b9fafc08b2c8c8974e3dfe978b163f690000000100000fa1
 ZONE</literal>.
     </para>
    </sect3>

    <sect3 id="datatype-datetime-special-values">
     <title>Special Values</title>

     <indexterm>
      <primary>time</primary>
      <secondary>constants</secondary>
     </indexterm>

     <indexterm>
      <primary>date</primary>
      <secondary>constants</secondary>
     </indexterm>

     <para>
      <productname>PostgreSQL</productname> supports several
      special date/time input values for convenience, as shown in <xref
      linkend="datatype-datetime-special-table"/>.  The values
      <literal>infinity</literal> and <literal>-infinity</literal>
      are specially represented inside the system and will be displayed
      unchanged; but the others are simply notational shorthands
      that will be converted to ordinary date/time values when read.
      (In particular, <literal>now</literal> and related strings are converted
      to a specific time value as soon as they are read.)
      All of these values need to be enclosed in single quotes when used
      as constants in SQL commands.
     </para>

      <table id="datatype-datetime-special-table">
       <title>Special Date/Time Inputs</title>
       <tgroup cols="3">
        <thead>
         <row>
          <entry>Input String</entry>
          <entry>Valid Types</entry>
          <entry>Description</entry>
         </row>
        </thead>
        <tbody>
         <row>
          <entry><literal>epoch</literal></entry>
          <entry><type>date</type>, <type>timestamp</type></entry>
          <entry>1970-01-01 00:00:00+00 (Unix system time zero)</entry>
         </row>
         <row>
          <entry><literal>infinity</literal></entry>
          <entry><type>date</type>, <type>timestamp</type>, <type>interval</type></entry>
          <entry>later than all other time stamps</entry>
         </row>
         <row>
          <entry><literal>-infinity</literal></entry>
          <entry><type>date</type>, <type>timestamp</type>, <type>interval</type></entry>
          <entry>earlier than all other time stamps</entry>
         </row>
         <row>
          <entry><literal>now</literal></entry>
          <entry><type>date</type>, <type>time</type>, <type>timestamp</type></entry>
          <entry>current transaction's start time</entry>
         </row>
         <row>
          <entry><literal>today</literal></entry>
          <entry><type>date</type>, <type>timestamp</type></entry>
          <entry>midnight (<literal>00:00</literal>) today</entry>
         </row>
         <row>
          <entry><literal>tomorrow</literal></entry>
          <entry><type>date</type>, <type>timestamp</type></entry>
          <entry>midnight (<literal>00:00</literal>) tomorrow</entry>
         </row>
         <row>
          <entry><literal>yesterday</literal></entry>
          <entry><type>date</type>, <type>timestamp</type></entry>
          <entry>midnight (<literal>00:00</literal>) yesterday</entry>
         </row>
         <row>
          <entry><literal>allballs</literal></entry>
          <entry><type>time</type></entry>
          <entry>00:00:00.00 UTC</entry>
         </row>
        </tbody>
       </tgroup>
      </table>

     <para>
      The following <acronym>SQL</acronym>-compatible functions can also
      be used to obtain the current time value for the corresponding data
      type:
      <literal>CURRENT_DATE</literal>, <literal>CURRENT_TIME</literal>,
      <literal>CURRENT_TIMESTAMP</literal>, <literal>LOCALTIME</literal>,
      <literal>LOCALTIMESTAMP</literal>.  (See <xref
      linkend="functions-datetime-current"/>.)  Note that these are
      SQL functions and are <emphasis>not</emphasis> recognized in data input strings.
     </para>

     <caution>
      <para>
       While the input strings <literal>now</literal>,
       <literal>today</literal>, <literal>tomorrow</literal>,
       and <literal>yesterday</literal> are fine to use in interactive SQL
       commands, they can have surprising behavior when the
Title: Special Date and Time Values
Summary
PostgreSQL supports special date and time input values, including 'infinity', '-infinity', 'now', 'today', 'tomorrow', and 'yesterday', which can be used as constants in SQL commands. These values are converted to ordinary date and time values when read, and some have specific meanings, such as 'now' referring to the current transaction's start time. Additionally, SQL-compatible functions like 'CURRENT_DATE' and 'CURRENT_TIMESTAMP' can be used to obtain the current time value.