1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>8.5. Date/Time Types</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="datatype-binary.html" title="8.4. Binary Data Types" /><link rel="next" href="datatype-boolean.html" title="8.6. Boolean Type" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">8.5. Date/Time Types</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="datatype-binary.html" title="8.4. Binary Data Types">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><th width="60%" align="center">Chapter 8. Data Types</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="datatype-boolean.html" title="8.6. Boolean Type">Next</a></td></tr></table><hr /></div><div class="sect1" id="DATATYPE-DATETIME"><div class="titlepage"><div><div><h2 class="title" style="clear: both">8.5. Date/Time Types <a href="#DATATYPE-DATETIME" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="datatype-datetime.html#DATATYPE-DATETIME-INPUT">8.5.1. Date/Time Input</a></span></dt><dt><span class="sect2"><a href="datatype-datetime.html#DATATYPE-DATETIME-OUTPUT">8.5.2. Date/Time Output</a></span></dt><dt><span class="sect2"><a href="datatype-datetime.html#DATATYPE-TIMEZONES">8.5.3. Time Zones</a></span></dt><dt><span class="sect2"><a href="datatype-datetime.html#DATATYPE-INTERVAL-INPUT">8.5.4. Interval Input</a></span></dt><dt><span class="sect2"><a href="datatype-datetime.html#DATATYPE-INTERVAL-OUTPUT">8.5.5. Interval Output</a></span></dt></dl></div><a id="id-1.5.7.13.2" class="indexterm"></a><a id="id-1.5.7.13.3" class="indexterm"></a><a id="id-1.5.7.13.4" class="indexterm"></a><a id="id-1.5.7.13.5" class="indexterm"></a><a id="id-1.5.7.13.6" class="indexterm"></a><a id="id-1.5.7.13.7" class="indexterm"></a><a id="id-1.5.7.13.8" class="indexterm"></a><a id="id-1.5.7.13.9" class="indexterm"></a><a id="id-1.5.7.13.10" class="indexterm"></a><a id="id-1.5.7.13.11" class="indexterm"></a><p>
3 <span class="productname">PostgreSQL</span> supports the full set of
4 <acronym class="acronym">SQL</acronym> date and time types, shown in <a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-TABLE" title="Table 8.9. Date/Time Types">Table 8.9</a>. The operations available
5 on these data types are described in
6 <a class="xref" href="functions-datetime.html" title="9.9. Date/Time Functions and Operators">Section 9.9</a>.
7 Dates are counted according to the Gregorian calendar, even in
8 years before that calendar was introduced (see <a class="xref" href="datetime-units-history.html" title="B.6. History of Units">Section B.6</a> for more information).
9 </p><div class="table" id="DATATYPE-DATETIME-TABLE"><p class="title"><strong>Table 8.9. Date/Time Types</strong></p><div class="table-contents"><table class="table" summary="Date/Time Types" border="1"><colgroup><col /><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Name</th><th>Storage Size</th><th>Description</th><th>Low Value</th><th>High Value</th><th>Resolution</th></tr></thead><tbody><tr><td><code class="type">timestamp [ (<em class="replaceable"><code>p</code></em>) ] [ without time zone ]</code></td><td>8 bytes</td><td>both date and time (no time zone)</td><td>4713 BC</td><td>294276 AD</td><td>1 microsecond</td></tr><tr><td><code class="type">timestamp [ (<em class="replaceable"><code>p</code></em>) ] with time zone</code></td><td>8 bytes</td><td>both date and time, with time zone</td><td>4713 BC</td><td>294276 AD</td><td>1 microsecond</td></tr><tr><td><code class="type">date</code></td><td>4 bytes</td><td>date (no time of day)</td><td>4713 BC</td><td>5874897 AD</td><td>1 day</td></tr><tr><td><code class="type">time [ (<em class="replaceable"><code>p</code></em>) ] [ without time zone ]</code></td><td>8 bytes</td><td>time of day (no date)</td><td>00:00:00</td><td>24:00:00</td><td>1 microsecond</td></tr><tr><td><code class="type">time [ (<em class="replaceable"><code>p</code></em>) ] with time zone</code></td><td>12 bytes</td><td>time of day (no date), with time zone</td><td>00:00:00+1559</td><td>24:00:00-1559</td><td>1 microsecond</td></tr><tr><td><code class="type">interval [ <em class="replaceable"><code>fields</code></em> ] [ (<em class="replaceable"><code>p</code></em>) ]</code></td><td>16 bytes</td><td>time interval</td><td>-178000000 years</td><td>178000000 years</td><td>1 microsecond</td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
10 The SQL standard requires that writing just <code class="type">timestamp</code>
11 be equivalent to <code class="type">timestamp without time
12 zone</code>, and <span class="productname">PostgreSQL</span> honors that
13 behavior. <code class="type">timestamptz</code> is accepted as an
14 abbreviation for <code class="type">timestamp with time zone</code>; this is a
15 <span class="productname">PostgreSQL</span> extension.
17 <code class="type">time</code>, <code class="type">timestamp</code>, and
18 <code class="type">interval</code> accept an optional precision value
19 <em class="replaceable"><code>p</code></em> which specifies the number of
20 fractional digits retained in the seconds field. By default, there
21 is no explicit bound on precision. The allowed range of
22 <em class="replaceable"><code>p</code></em> is from 0 to 6.
24 The <code class="type">interval</code> type has an additional option, which is
25 to restrict the set of stored fields by writing one of these phrases:
26 </p><pre class="literallayout">
41 Note that if both <em class="replaceable"><code>fields</code></em> and
42 <em class="replaceable"><code>p</code></em> are specified, the
43 <em class="replaceable"><code>fields</code></em> must include <code class="literal">SECOND</code>,
44 since the precision applies only to the seconds.
46 The type <code class="type">time with time zone</code> is defined by the SQL
47 standard, but the definition exhibits properties which lead to
48 questionable usefulness. In most cases, a combination of
49 <code class="type">date</code>, <code class="type">time</code>, <code class="type">timestamp without time
50 zone</code>, and <code class="type">timestamp with time zone</code> should
51 provide a complete range of date/time functionality required by
53 </p><div class="sect2" id="DATATYPE-DATETIME-INPUT"><div class="titlepage"><div><div><h3 class="title">8.5.1. Date/Time Input <a href="#DATATYPE-DATETIME-INPUT" class="id_link">#</a></h3></div></div></div><p>
54 Date and time input is accepted in almost any reasonable format, including
55 ISO 8601, <acronym class="acronym">SQL</acronym>-compatible,
56 traditional <span class="productname">POSTGRES</span>, and others.
57 For some formats, ordering of day, month, and year in date input is
58 ambiguous and there is support for specifying the expected
59 ordering of these fields. Set the <a class="xref" href="runtime-config-client.html#GUC-DATESTYLE">DateStyle</a> parameter
60 to <code class="literal">MDY</code> to select month-day-year interpretation,
61 <code class="literal">DMY</code> to select day-month-year interpretation, or
62 <code class="literal">YMD</code> to select year-month-day interpretation.
64 <span class="productname">PostgreSQL</span> is more flexible in
65 handling date/time input than the
66 <acronym class="acronym">SQL</acronym> standard requires.
67 See <a class="xref" href="datetime-appendix.html" title="Appendix B. Date/Time Support">Appendix B</a>
68 for the exact parsing rules of date/time input and for the
69 recognized text fields including months, days of the week, and
72 Remember that any date or time literal input needs to be enclosed
73 in single quotes, like text strings. Refer to
74 <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC" title="4.1.2.7. Constants of Other Types">Section 4.1.2.7</a> for more
76 <acronym class="acronym">SQL</acronym> requires the following syntax
77 </p><pre class="synopsis">
78 <em class="replaceable"><code>type</code></em> [ (<em class="replaceable"><code>p</code></em>) ] '<em class="replaceable"><code>value</code></em>'
80 where <em class="replaceable"><code>p</code></em> is an optional precision
81 specification giving the number of
82 fractional digits in the seconds field. Precision can be
83 specified for <code class="type">time</code>, <code class="type">timestamp</code>, and
84 <code class="type">interval</code> types, and can range from 0 to 6.
85 If no precision is specified in a constant specification,
86 it defaults to the precision of the literal value (but not
88 </p><div class="sect3" id="DATATYPE-DATETIME-INPUT-DATES"><div class="titlepage"><div><div><h4 class="title">8.5.1.1. Dates <a href="#DATATYPE-DATETIME-INPUT-DATES" class="id_link">#</a></h4></div></div></div><a id="id-1.5.7.13.18.5.2" class="indexterm"></a><p>
89 <a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE" title="Table 8.10. Date Input">Table 8.10</a> shows some possible
90 inputs for the <code class="type">date</code> type.
91 </p><div class="table" id="DATATYPE-DATETIME-DATE-TABLE"><p class="title"><strong>Table 8.10. Date Input</strong></p><div class="table-contents"><table class="table" summary="Date Input" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th>Example</th><th>Description</th></tr></thead><tbody><tr><td>1999-01-08</td><td>ISO 8601; January 8 in any mode
92 (recommended format)</td></tr><tr><td>January 8, 1999</td><td>unambiguous in any <code class="varname">datestyle</code> input mode</td></tr><tr><td>1/8/1999</td><td>January 8 in <code class="literal">MDY</code> mode;
93 August 1 in <code class="literal">DMY</code> mode</td></tr><tr><td>1/18/1999</td><td>January 18 in <code class="literal">MDY</code> mode;
94 rejected in other modes</td></tr><tr><td>01/02/03</td><td>January 2, 2003 in <code class="literal">MDY</code> mode;
95 February 1, 2003 in <code class="literal">DMY</code> mode;
96 February 3, 2001 in <code class="literal">YMD</code> mode
97 </td></tr><tr><td>1999-Jan-08</td><td>January 8 in any mode</td></tr><tr><td>Jan-08-1999</td><td>January 8 in any mode</td></tr><tr><td>08-Jan-1999</td><td>January 8 in any mode</td></tr><tr><td>99-Jan-08</td><td>January 8 in <code class="literal">YMD</code> mode, else error</td></tr><tr><td>08-Jan-99</td><td>January 8, except error in <code class="literal">YMD</code> mode</td></tr><tr><td>Jan-08-99</td><td>January 8, except error in <code class="literal">YMD</code> mode</td></tr><tr><td>19990108</td><td>ISO 8601; January 8, 1999 in any mode</td></tr><tr><td>990108</td><td>ISO 8601; January 8, 1999 in any mode</td></tr><tr><td>1999.008</td><td>year and day of year</td></tr><tr><td>J2451187</td><td>Julian date</td></tr><tr><td>January 8, 99 BC</td><td>year 99 BC</td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect3" id="DATATYPE-DATETIME-INPUT-TIMES"><div class="titlepage"><div><div><h4 class="title">8.5.1.2. Times <a href="#DATATYPE-DATETIME-INPUT-TIMES" class="id_link">#</a></h4></div></div></div><a id="id-1.5.7.13.18.6.2" class="indexterm"></a><a id="id-1.5.7.13.18.6.3" class="indexterm"></a><a id="id-1.5.7.13.18.6.4" class="indexterm"></a><p>
98 The time-of-day types are <code class="type">time [
99 (<em class="replaceable"><code>p</code></em>) ] without time zone</code> and
100 <code class="type">time [ (<em class="replaceable"><code>p</code></em>) ] with time
101 zone</code>. <code class="type">time</code> alone is equivalent to
102 <code class="type">time without time zone</code>.
104 Valid input for these types consists of a time of day followed
105 by an optional time zone. (See <a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-TIME-TABLE" title="Table 8.11. Time Input">Table 8.11</a>
106 and <a class="xref" href="datatype-datetime.html#DATATYPE-TIMEZONE-TABLE" title="Table 8.12. Time Zone Input">Table 8.12</a>.) If a time zone is
107 specified in the input for <code class="type">time without time zone</code>,
108 it is silently ignored. You can also specify a date but it will
109 be ignored, except when you use a time zone name that involves a
110 daylight-savings rule, such as
111 <code class="literal">America/New_York</code>. In this case specifying the date
112 is required in order to determine whether standard or daylight-savings
113 time applies. The appropriate time zone offset is recorded in the
114 <code class="type">time with time zone</code> value and is output as stored;
115 it is not adjusted to the active time zone.
116 </p><div class="table" id="DATATYPE-DATETIME-TIME-TABLE"><p class="title"><strong>Table 8.11. Time Input</strong></p><div class="table-contents"><table class="table" summary="Time Input" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th>Example</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">04:05:06.789</code></td><td>ISO 8601</td></tr><tr><td><code class="literal">04:05:06</code></td><td>ISO 8601</td></tr><tr><td><code class="literal">04:05</code></td><td>ISO 8601</td></tr><tr><td><code class="literal">040506</code></td><td>ISO 8601</td></tr><tr><td><code class="literal">04:05 AM</code></td><td>same as 04:05; AM does not affect value</td></tr><tr><td><code class="literal">04:05 PM</code></td><td>same as 16:05; input hour must be <= 12</td></tr><tr><td><code class="literal">04:05:06.789-8</code></td><td>ISO 8601, with time zone as UTC offset</td></tr><tr><td><code class="literal">04:05:06-08:00</code></td><td>ISO 8601, with time zone as UTC offset</td></tr><tr><td><code class="literal">04:05-08:00</code></td><td>ISO 8601, with time zone as UTC offset</td></tr><tr><td><code class="literal">040506-08</code></td><td>ISO 8601, with time zone as UTC offset</td></tr><tr><td><code class="literal">040506+0730</code></td><td>ISO 8601, with fractional-hour time zone as UTC offset</td></tr><tr><td><code class="literal">040506+07:30:00</code></td><td>UTC offset specified to seconds (not allowed in ISO 8601)</td></tr><tr><td><code class="literal">04:05:06 PST</code></td><td>time zone specified by abbreviation</td></tr><tr><td><code class="literal">2003-04-12 04:05:06 America/New_York</code></td><td>time zone specified by full name</td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="DATATYPE-TIMEZONE-TABLE"><p class="title"><strong>Table 8.12. Time Zone Input</strong></p><div class="table-contents"><table class="table" summary="Time Zone Input" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Example</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">PST</code></td><td>Abbreviation (for Pacific Standard Time)</td></tr><tr><td><code class="literal">America/New_York</code></td><td>Full time zone name</td></tr><tr><td><code class="literal">PST8PDT</code></td><td>POSIX-style time zone specification</td></tr><tr><td><code class="literal">-8:00:00</code></td><td>UTC offset for PST</td></tr><tr><td><code class="literal">-8:00</code></td><td>UTC offset for PST (ISO 8601 extended format)</td></tr><tr><td><code class="literal">-800</code></td><td>UTC offset for PST (ISO 8601 basic format)</td></tr><tr><td><code class="literal">-8</code></td><td>UTC offset for PST (ISO 8601 basic format)</td></tr><tr><td><code class="literal">zulu</code></td><td>Military abbreviation for UTC</td></tr><tr><td><code class="literal">z</code></td><td>Short form of <code class="literal">zulu</code> (also in ISO 8601)</td></tr></tbody></table></div></div><br class="table-break" /><p>
117 Refer to <a class="xref" href="datatype-datetime.html#DATATYPE-TIMEZONES" title="8.5.3. Time Zones">Section 8.5.3</a> for more information on how
118 to specify time zones.
119 </p></div><div class="sect3" id="DATATYPE-DATETIME-INPUT-TIME-STAMPS"><div class="titlepage"><div><div><h4 class="title">8.5.1.3. Time Stamps <a href="#DATATYPE-DATETIME-INPUT-TIME-STAMPS" class="id_link">#</a></h4></div></div></div><a id="id-1.5.7.13.18.7.2" class="indexterm"></a><a id="id-1.5.7.13.18.7.3" class="indexterm"></a><a id="id-1.5.7.13.18.7.4" class="indexterm"></a><p>
120 Valid input for the time stamp types consists of the concatenation
121 of a date and a time, followed by an optional time zone,
122 followed by an optional <code class="literal">AD</code> or <code class="literal">BC</code>.
123 (Alternatively, <code class="literal">AD</code>/<code class="literal">BC</code> can appear
124 before the time zone, but this is not the preferred ordering.)
127 </p><pre class="programlisting">
131 </p><pre class="programlisting">
132 1999-01-08 04:05:06 -8:00
135 are valid values, which follow the <acronym class="acronym">ISO</acronym> 8601
136 standard. In addition, the common format:
137 </p><pre class="programlisting">
138 January 8 04:05:06 1999 PST
142 The <acronym class="acronym">SQL</acronym> standard differentiates
143 <code class="type">timestamp without time zone</code>
144 and <code class="type">timestamp with time zone</code> literals by the presence of a
145 <span class="quote">“<span class="quote">+</span>”</span> or <span class="quote">“<span class="quote">-</span>”</span> symbol and time zone offset after
146 the time. Hence, according to the standard,
148 </p><pre class="programlisting">
149 TIMESTAMP '2004-10-19 10:23:54'
152 is a <code class="type">timestamp without time zone</code>, while
154 </p><pre class="programlisting">
155 TIMESTAMP '2004-10-19 10:23:54+02'
158 is a <code class="type">timestamp with time zone</code>.
159 <span class="productname">PostgreSQL</span> never examines the content of a
160 literal string before determining its type, and therefore will treat
161 both of the above as <code class="type">timestamp without time zone</code>. To
162 ensure that a literal is treated as <code class="type">timestamp with time
163 zone</code>, give it the correct explicit type:
165 </p><pre class="programlisting">
166 TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
169 In a value that has been determined to be <code class="type">timestamp without time
170 zone</code>, <span class="productname">PostgreSQL</span> will silently ignore
171 any time zone indication.
172 That is, the resulting value is derived from the date/time
173 fields in the input string, and is not adjusted for time zone.
175 For <code class="type">timestamp with time zone</code> values, an input string
176 that includes an explicit time zone will be converted to UTC
177 (<a class="glossterm" href="glossary.html#GLOSSARY-UTC"><em class="glossterm"><a class="glossterm" href="glossary.html#GLOSSARY-UTC" title="UTC">Universal Coordinated
178 Time</a></em></a>) using the appropriate offset
179 for that time zone. If no time zone is stated in the input string,
180 then it is assumed to be in the time zone indicated by the system's
181 <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> parameter, and is converted to UTC using the
182 offset for the <code class="varname">timezone</code> zone.
183 In either case, the value is stored internally as UTC, and the
184 originally stated or assumed time zone is not retained.
186 When a <code class="type">timestamp with time
187 zone</code> value is output, it is always converted from UTC to the
188 current <code class="varname">timezone</code> zone, and displayed as local time in that
189 zone. To see the time in another time zone, either change
190 <code class="varname">timezone</code> or use the <code class="literal">AT TIME ZONE</code> construct
191 (see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT" title="9.9.4. AT TIME ZONE and AT LOCAL">Section 9.9.4</a>).
193 Conversions between <code class="type">timestamp without time zone</code> and
194 <code class="type">timestamp with time zone</code> normally assume that the
195 <code class="type">timestamp without time zone</code> value should be taken or given
196 as <code class="varname">timezone</code> local time. A different time zone can
197 be specified for the conversion using <code class="literal">AT TIME ZONE</code>.
198 </p></div><div class="sect3" id="DATATYPE-DATETIME-SPECIAL-VALUES"><div class="titlepage"><div><div><h4 class="title">8.5.1.4. Special Values <a href="#DATATYPE-DATETIME-SPECIAL-VALUES" class="id_link">#</a></h4></div></div></div><a id="id-1.5.7.13.18.8.2" class="indexterm"></a><a id="id-1.5.7.13.18.8.3" class="indexterm"></a><p>
199 <span class="productname">PostgreSQL</span> supports several
200 special date/time input values for convenience, as shown in <a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-TABLE" title="Table 8.13. Special Date/Time Inputs">Table 8.13</a>. The values
201 <code class="literal">infinity</code> and <code class="literal">-infinity</code>
202 are specially represented inside the system and will be displayed
203 unchanged; but the others are simply notational shorthands
204 that will be converted to ordinary date/time values when read.
205 (In particular, <code class="literal">now</code> and related strings are converted
206 to a specific time value as soon as they are read.)
207 All of these values need to be enclosed in single quotes when used
208 as constants in SQL commands.
209 </p><div class="table" id="DATATYPE-DATETIME-SPECIAL-TABLE"><p class="title"><strong>Table 8.13. Special Date/Time Inputs</strong></p><div class="table-contents"><table class="table" summary="Special Date/Time Inputs" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Input String</th><th>Valid Types</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">epoch</code></td><td><code class="type">date</code>, <code class="type">timestamp</code></td><td>1970-01-01 00:00:00+00 (Unix system time zero)</td></tr><tr><td><code class="literal">infinity</code></td><td><code class="type">date</code>, <code class="type">timestamp</code>, <code class="type">interval</code></td><td>later than all other time stamps</td></tr><tr><td><code class="literal">-infinity</code></td><td><code class="type">date</code>, <code class="type">timestamp</code>, <code class="type">interval</code></td><td>earlier than all other time stamps</td></tr><tr><td><code class="literal">now</code></td><td><code class="type">date</code>, <code class="type">time</code>, <code class="type">timestamp</code></td><td>current transaction's start time</td></tr><tr><td><code class="literal">today</code></td><td><code class="type">date</code>, <code class="type">timestamp</code></td><td>midnight (<code class="literal">00:00</code>) today</td></tr><tr><td><code class="literal">tomorrow</code></td><td><code class="type">date</code>, <code class="type">timestamp</code></td><td>midnight (<code class="literal">00:00</code>) tomorrow</td></tr><tr><td><code class="literal">yesterday</code></td><td><code class="type">date</code>, <code class="type">timestamp</code></td><td>midnight (<code class="literal">00:00</code>) yesterday</td></tr><tr><td><code class="literal">allballs</code></td><td><code class="type">time</code></td><td>00:00:00.00 UTC</td></tr></tbody></table></div></div><br class="table-break" /><p>
210 The following <acronym class="acronym">SQL</acronym>-compatible functions can also
211 be used to obtain the current time value for the corresponding data
213 <code class="literal">CURRENT_DATE</code>, <code class="literal">CURRENT_TIME</code>,
214 <code class="literal">CURRENT_TIMESTAMP</code>, <code class="literal">LOCALTIME</code>,
215 <code class="literal">LOCALTIMESTAMP</code>. (See <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>.) Note that these are
216 SQL functions and are <span class="emphasis"><em>not</em></span> recognized in data input strings.
217 </p><div class="caution"><h3 class="title">Caution</h3><p>
218 While the input strings <code class="literal">now</code>,
219 <code class="literal">today</code>, <code class="literal">tomorrow</code>,
220 and <code class="literal">yesterday</code> are fine to use in interactive SQL
221 commands, they can have surprising behavior when the command is
222 saved to be executed later, for example in prepared statements,
223 views, and function definitions. The string can be converted to a
224 specific time value that continues to be used long after it becomes
225 stale. Use one of the SQL functions instead in such contexts.
226 For example, <code class="literal">CURRENT_DATE + 1</code> is safer than
227 <code class="literal">'tomorrow'::date</code>.
228 </p></div></div></div><div class="sect2" id="DATATYPE-DATETIME-OUTPUT"><div class="titlepage"><div><div><h3 class="title">8.5.2. Date/Time Output <a href="#DATATYPE-DATETIME-OUTPUT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.7.13.19.2" class="indexterm"></a><a id="id-1.5.7.13.19.3" class="indexterm"></a><p>
229 The output format of the date/time types can be set to one of the four
231 <acronym class="acronym">SQL</acronym> (Ingres), traditional <span class="productname">POSTGRES</span>
232 (Unix <span class="application">date</span> format), or
234 is the <acronym class="acronym">ISO</acronym> format. (The
235 <acronym class="acronym">SQL</acronym> standard requires the use of the ISO 8601
236 format. The name of the <span class="quote">“<span class="quote">SQL</span>”</span> output format is a
237 historical accident.) <a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-OUTPUT-TABLE" title="Table 8.14. Date/Time Output Styles">Table 8.14</a> shows examples of each
238 output style. The output of the <code class="type">date</code> and
239 <code class="type">time</code> types is generally only the date or time part
240 in accordance with the given examples. However, the
241 <span class="productname">POSTGRES</span> style outputs date-only values in
242 <acronym class="acronym">ISO</acronym> format.
243 </p><div class="table" id="DATATYPE-DATETIME-OUTPUT-TABLE"><p class="title"><strong>Table 8.14. Date/Time Output Styles</strong></p><div class="table-contents"><table class="table" summary="Date/Time Output Styles" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>Style Specification</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td><code class="literal">ISO</code></td><td>ISO 8601, SQL standard</td><td><code class="literal">1997-12-17 07:37:16-08</code></td></tr><tr><td><code class="literal">SQL</code></td><td>traditional style</td><td><code class="literal">12/17/1997 07:37:16.00 PST</code></td></tr><tr><td><code class="literal">Postgres</code></td><td>original style</td><td><code class="literal">Wed Dec 17 07:37:16 1997 PST</code></td></tr><tr><td><code class="literal">German</code></td><td>regional style</td><td><code class="literal">17.12.1997 07:37:16.00 PST</code></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
244 ISO 8601 specifies the use of uppercase letter <code class="literal">T</code> to separate
245 the date and time. <span class="productname">PostgreSQL</span> accepts that format on
246 input, but on output it uses a space rather than <code class="literal">T</code>, as shown
247 above. This is for readability and for consistency with
248 <a class="ulink" href="https://datatracker.ietf.org/doc/html/rfc3339" target="_top">RFC 3339</a> as
249 well as some other database systems.
251 In the <acronym class="acronym">SQL</acronym> and POSTGRES styles, day appears before
252 month if DMY field ordering has been specified, otherwise month appears
254 (See <a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-INPUT" title="8.5.1. Date/Time Input">Section 8.5.1</a>
255 for how this setting also affects interpretation of input values.)
256 <a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-OUTPUT2-TABLE" title="Table 8.15. Date Order Conventions">Table 8.15</a> shows examples.
257 </p><div class="table" id="DATATYPE-DATETIME-OUTPUT2-TABLE"><p class="title"><strong>Table 8.15. Date Order Conventions</strong></p><div class="table-contents"><table class="table" summary="Date Order Conventions" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th><code class="varname">datestyle</code> Setting</th><th>Input Ordering</th><th>Example Output</th></tr></thead><tbody><tr><td><code class="literal">SQL, DMY</code></td><td><em class="replaceable"><code>day</code></em>/<em class="replaceable"><code>month</code></em>/<em class="replaceable"><code>year</code></em></td><td><code class="literal">17/12/1997 15:37:16.00 CET</code></td></tr><tr><td><code class="literal">SQL, MDY</code></td><td><em class="replaceable"><code>month</code></em>/<em class="replaceable"><code>day</code></em>/<em class="replaceable"><code>year</code></em></td><td><code class="literal">12/17/1997 07:37:16.00 PST</code></td></tr><tr><td><code class="literal">Postgres, DMY</code></td><td><em class="replaceable"><code>day</code></em>/<em class="replaceable"><code>month</code></em>/<em class="replaceable"><code>year</code></em></td><td><code class="literal">Wed 17 Dec 07:37:16 1997 PST</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
258 In the <acronym class="acronym">ISO</acronym> style, the time zone is always shown as
259 a signed numeric offset from UTC, with positive sign used for zones
260 east of Greenwich. The offset will be shown
261 as <em class="replaceable"><code>hh</code></em> (hours only) if it is an integral
262 number of hours, else
263 as <em class="replaceable"><code>hh</code></em>:<em class="replaceable"><code>mm</code></em> if it
264 is an integral number of minutes, else as
265 <em class="replaceable"><code>hh</code></em>:<em class="replaceable"><code>mm</code></em>:<em class="replaceable"><code>ss</code></em>.
266 (The third case is not possible with any modern time zone standard,
267 but it can appear when working with timestamps that predate the
268 adoption of standardized time zones.)
269 In the other date styles, the time zone is shown as an alphabetic
270 abbreviation if one is in common use in the current zone. Otherwise
271 it appears as a signed numeric offset in ISO 8601 basic format
272 (<em class="replaceable"><code>hh</code></em> or <em class="replaceable"><code>hhmm</code></em>).
273 The alphabetic abbreviations shown in these styles are taken from the
274 IANA time zone database entry currently selected by the
275 <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> run-time parameter; they are not
276 affected by the <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE-ABBREVIATIONS">timezone_abbreviations</a> setting.
278 The date/time style can be selected by the user using the
279 <code class="command">SET datestyle</code> command, the <a class="xref" href="runtime-config-client.html#GUC-DATESTYLE">DateStyle</a> parameter in the
280 <code class="filename">postgresql.conf</code> configuration file, or the
281 <code class="envar">PGDATESTYLE</code> environment variable on the server or
284 The formatting function <code class="function">to_char</code>
285 (see <a class="xref" href="functions-formatting.html" title="9.8. Data Type Formatting Functions">Section 9.8</a>) is also available as
286 a more flexible way to format date/time output.
287 </p></div><div class="sect2" id="DATATYPE-TIMEZONES"><div class="titlepage"><div><div><h3 class="title">8.5.3. Time Zones <a href="#DATATYPE-TIMEZONES" class="id_link">#</a></h3></div></div></div><a id="id-1.5.7.13.20.2" class="indexterm"></a><p>
288 Time zones, and time-zone conventions, are influenced by
289 political decisions, not just earth geometry. Time zones around the
290 world became somewhat standardized during the 1900s,
291 but continue to be prone to arbitrary changes, particularly with
292 respect to daylight-savings rules.
293 <span class="productname">PostgreSQL</span> uses the widely-used
294 IANA (Olson) time zone database for information about
295 historical time zone rules. For times in the future, the assumption
296 is that the latest known rules for a given time zone will
297 continue to be observed indefinitely far into the future.
299 <span class="productname">PostgreSQL</span> endeavors to be compatible with
300 the <acronym class="acronym">SQL</acronym> standard definitions for typical usage.
301 However, the <acronym class="acronym">SQL</acronym> standard has an odd mix of date and
302 time types and capabilities. Two obvious problems are:
304 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
305 Although the <code class="type">date</code> type
306 cannot have an associated time zone, the
307 <code class="type">time</code> type can.
308 Time zones in the real world have little meaning unless
309 associated with a date as well as a time,
310 since the offset can vary through the year with daylight-saving
312 </p></li><li class="listitem"><p>
313 The default time zone is specified as a constant numeric offset
314 from <acronym class="acronym">UTC</acronym>. It is therefore impossible to adapt to
315 daylight-saving time when doing date/time arithmetic across
316 <acronym class="acronym">DST</acronym> boundaries.
317 </p></li></ul></div><p>
319 To address these difficulties, we recommend using date/time types
320 that contain both date and time when using time zones. We
321 do <span class="emphasis"><em>not</em></span> recommend using the type <code class="type">time with
322 time zone</code> (though it is supported by
323 <span class="productname">PostgreSQL</span> for legacy applications and
324 for compliance with the <acronym class="acronym">SQL</acronym> standard).
325 <span class="productname">PostgreSQL</span> assumes
326 your local time zone for any type containing only date or time.
328 All timezone-aware dates and times are stored internally in
329 <acronym class="acronym">UTC</acronym>. They are converted to local time
330 in the zone specified by the <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> configuration
331 parameter before being displayed to the client.
333 <span class="productname">PostgreSQL</span> allows you to specify time zones in
334 three different forms:
335 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
336 A full time zone name, for example <code class="literal">America/New_York</code>.
337 The recognized time zone names are listed in the
338 <code class="literal">pg_timezone_names</code> view (see <a class="xref" href="view-pg-timezone-names.html" title="53.34. pg_timezone_names">Section 53.34</a>).
339 <span class="productname">PostgreSQL</span> uses the widely-used IANA
340 time zone data for this purpose, so the same time zone
341 names are also recognized by other software.
342 </p></li><li class="listitem"><p>
343 A time zone abbreviation, for example <code class="literal">PST</code>. Such a
344 specification merely defines a particular offset from UTC, in
345 contrast to full time zone names which can imply a set of daylight
346 savings transition rules as well. The recognized abbreviations
347 are listed in the <code class="literal">pg_timezone_abbrevs</code> view (see <a class="xref" href="view-pg-timezone-abbrevs.html" title="53.33. pg_timezone_abbrevs">Section 53.33</a>). You cannot set the
348 configuration parameters <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> or
349 <a class="xref" href="runtime-config-logging.html#GUC-LOG-TIMEZONE">log_timezone</a> to a time
350 zone abbreviation, but you can use abbreviations in
351 date/time input values and with the <code class="literal">AT TIME ZONE</code>
353 </p></li><li class="listitem"><p>
354 In addition to the timezone names and abbreviations,
355 <span class="productname">PostgreSQL</span> will accept POSIX-style time zone
356 specifications, as described in
357 <a class="xref" href="datetime-posix-timezone-specs.html" title="B.5. POSIX Time Zone Specifications">Section B.5</a>. This option is not
358 normally preferable to using a named time zone, but it may be
359 necessary if no suitable IANA time zone entry is available.
360 </p></li></ul></div><p>
362 In short, this is the difference between abbreviations
363 and full names: abbreviations represent a specific offset from UTC,
364 whereas many of the full names imply a local daylight-savings time
365 rule, and so have two possible UTC offsets. As an example,
366 <code class="literal">2014-06-04 12:00 America/New_York</code> represents noon local
367 time in New York, which for this particular date was Eastern Daylight
368 Time (UTC-4). So <code class="literal">2014-06-04 12:00 EDT</code> specifies that
369 same time instant. But <code class="literal">2014-06-04 12:00 EST</code> specifies
370 noon Eastern Standard Time (UTC-5), regardless of whether daylight
371 savings was nominally in effect on that date.
372 </p><div class="note"><h3 class="title">Note</h3><p>
373 The sign in POSIX-style time zone specifications has the opposite meaning
374 of the sign in ISO-8601 datetime values. For example, the POSIX time zone
375 for <code class="literal">2014-06-04 12:00+04</code> would be UTC-4.
377 To complicate matters, some jurisdictions have used the same timezone
378 abbreviation to mean different UTC offsets at different times; for
379 example, in Moscow <code class="literal">MSK</code> has meant UTC+3 in some years and
380 UTC+4 in others. <span class="productname">PostgreSQL</span> interprets such
381 abbreviations according to whatever they meant (or had most recently
382 meant) on the specified date; but, as with the <code class="literal">EST</code> example
383 above, this is not necessarily the same as local civil time on that date.
385 In all cases, timezone names and abbreviations are recognized
386 case-insensitively. (This is a change from <span class="productname">PostgreSQL</span>
387 versions prior to 8.2, which were case-sensitive in some contexts but
390 Neither timezone names nor abbreviations are hard-wired into the server;
391 they are obtained from configuration files stored under
392 <code class="filename">.../share/timezone/</code> and <code class="filename">.../share/timezonesets/</code>
393 of the installation directory
394 (see <a class="xref" href="datetime-config-files.html" title="B.4. Date/Time Configuration Files">Section B.4</a>).
396 The <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> configuration parameter can
397 be set in the file <code class="filename">postgresql.conf</code>, or in any of the
398 other standard ways described in <a class="xref" href="runtime-config.html" title="Chapter 19. Server Configuration">Chapter 19</a>.
399 There are also some special ways to set it:
401 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
402 The <acronym class="acronym">SQL</acronym> command <code class="command">SET TIME ZONE</code>
403 sets the time zone for the session. This is an alternative spelling
404 of <code class="command">SET TIMEZONE TO</code> with a more SQL-spec-compatible syntax.
405 </p></li><li class="listitem"><p>
406 The <code class="envar">PGTZ</code> environment variable is used by
407 <span class="application">libpq</span> clients
408 to send a <code class="command">SET TIME ZONE</code>
409 command to the server upon connection.
410 </p></li></ul></div><p>
411 </p></div><div class="sect2" id="DATATYPE-INTERVAL-INPUT"><div class="titlepage"><div><div><h3 class="title">8.5.4. Interval Input <a href="#DATATYPE-INTERVAL-INPUT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.7.13.21.2" class="indexterm"></a><p>
412 <code class="type">interval</code> values can be written using the following
415 </p><pre class="synopsis">
416 [<span class="optional">@</span>] <em class="replaceable"><code>quantity</code></em> <em class="replaceable"><code>unit</code></em> [<span class="optional"><em class="replaceable"><code>quantity</code></em> <em class="replaceable"><code>unit</code></em>...</span>] [<span class="optional"><em class="replaceable"><code>direction</code></em></span>]
419 where <em class="replaceable"><code>quantity</code></em> is a number (possibly signed);
420 <em class="replaceable"><code>unit</code></em> is <code class="literal">microsecond</code>,
421 <code class="literal">millisecond</code>, <code class="literal">second</code>,
422 <code class="literal">minute</code>, <code class="literal">hour</code>, <code class="literal">day</code>,
423 <code class="literal">week</code>, <code class="literal">month</code>, <code class="literal">year</code>,
424 <code class="literal">decade</code>, <code class="literal">century</code>, <code class="literal">millennium</code>,
425 or abbreviations or plurals of these units;
426 <em class="replaceable"><code>direction</code></em> can be <code class="literal">ago</code> or
427 empty. The at sign (<code class="literal">@</code>) is optional noise. The amounts
428 of the different units are implicitly added with appropriate
429 sign accounting. <code class="literal">ago</code> negates all the fields.
430 This syntax is also used for interval output, if
431 <a class="xref" href="runtime-config-client.html#GUC-INTERVALSTYLE">IntervalStyle</a> is set to
432 <code class="literal">postgres_verbose</code>.
434 Quantities of days, hours, minutes, and seconds can be specified without
435 explicit unit markings. For example, <code class="literal">'1 12:59:10'</code> is read
436 the same as <code class="literal">'1 day 12 hours 59 min 10 sec'</code>. Also,
437 a combination of years and months can be specified with a dash;
438 for example <code class="literal">'200-10'</code> is read the same as <code class="literal">'200 years
439 10 months'</code>. (These shorter forms are in fact the only ones allowed
440 by the <acronym class="acronym">SQL</acronym> standard, and are used for output when
441 <code class="varname">IntervalStyle</code> is set to <code class="literal">sql_standard</code>.)
443 Interval values can also be written as ISO 8601 time intervals, using
444 either the <span class="quote">“<span class="quote">format with designators</span>”</span> of the standard's section
445 4.4.3.2 or the <span class="quote">“<span class="quote">alternative format</span>”</span> of section 4.4.3.3. The
446 format with designators looks like this:
447 </p><pre class="synopsis">
448 P <em class="replaceable"><code>quantity</code></em> <em class="replaceable"><code>unit</code></em> [<span class="optional"> <em class="replaceable"><code>quantity</code></em> <em class="replaceable"><code>unit</code></em> ...</span>] [<span class="optional"> T [<span class="optional"> <em class="replaceable"><code>quantity</code></em> <em class="replaceable"><code>unit</code></em> ...</span>]</span>]
450 The string must start with a <code class="literal">P</code>, and may include a
451 <code class="literal">T</code> that introduces the time-of-day units. The
452 available unit abbreviations are given in <a class="xref" href="datatype-datetime.html#DATATYPE-INTERVAL-ISO8601-UNITS" title="Table 8.16. ISO 8601 Interval Unit Abbreviations">Table 8.16</a>. Units may be
453 omitted, and may be specified in any order, but units smaller than
454 a day must appear after <code class="literal">T</code>. In particular, the meaning of
455 <code class="literal">M</code> depends on whether it is before or after
456 <code class="literal">T</code>.
457 </p><div class="table" id="DATATYPE-INTERVAL-ISO8601-UNITS"><p class="title"><strong>Table 8.16. ISO 8601 Interval Unit Abbreviations</strong></p><div class="table-contents"><table class="table" summary="ISO 8601 Interval Unit Abbreviations" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Abbreviation</th><th>Meaning</th></tr></thead><tbody><tr><td>Y</td><td>Years</td></tr><tr><td>M</td><td>Months (in the date part)</td></tr><tr><td>W</td><td>Weeks</td></tr><tr><td>D</td><td>Days</td></tr><tr><td>H</td><td>Hours</td></tr><tr><td>M</td><td>Minutes (in the time part)</td></tr><tr><td>S</td><td>Seconds</td></tr></tbody></table></div></div><br class="table-break" /><p>
458 In the alternative format:
459 </p><pre class="synopsis">
460 P [<span class="optional"> <em class="replaceable"><code>years</code></em>-<em class="replaceable"><code>months</code></em>-<em class="replaceable"><code>days</code></em> </span>] [<span class="optional"> T <em class="replaceable"><code>hours</code></em>:<em class="replaceable"><code>minutes</code></em>:<em class="replaceable"><code>seconds</code></em> </span>]
462 the string must begin with <code class="literal">P</code>, and a
463 <code class="literal">T</code> separates the date and time parts of the interval.
464 The values are given as numbers similar to ISO 8601 dates.
466 When writing an interval constant with a <em class="replaceable"><code>fields</code></em>
467 specification, or when assigning a string to an interval column that was
468 defined with a <em class="replaceable"><code>fields</code></em> specification, the interpretation of
469 unmarked quantities depends on the <em class="replaceable"><code>fields</code></em>. For
470 example <code class="literal">INTERVAL '1' YEAR</code> is read as 1 year, whereas
471 <code class="literal">INTERVAL '1'</code> means 1 second. Also, field values
472 <span class="quote">“<span class="quote">to the right</span>”</span> of the least significant field allowed by the
473 <em class="replaceable"><code>fields</code></em> specification are silently discarded. For
474 example, writing <code class="literal">INTERVAL '1 day 2:03:04' HOUR TO MINUTE</code>
475 results in dropping the seconds field, but not the day field.
477 According to the <acronym class="acronym">SQL</acronym> standard all fields of an interval
478 value must have the same sign, so a leading negative sign applies to all
479 fields; for example the negative sign in the interval literal
480 <code class="literal">'-1 2:03:04'</code> applies to both the days and hour/minute/second
481 parts. <span class="productname">PostgreSQL</span> allows the fields to have different
482 signs, and traditionally treats each field in the textual representation
483 as independently signed, so that the hour/minute/second part is
484 considered positive in this example. If <code class="varname">IntervalStyle</code> is
485 set to <code class="literal">sql_standard</code> then a leading sign is considered
486 to apply to all fields (but only if no additional signs appear).
487 Otherwise the traditional <span class="productname">PostgreSQL</span> interpretation is
488 used. To avoid ambiguity, it's recommended to attach an explicit sign
489 to each field if any field is negative.
491 Internally, <code class="type">interval</code> values are stored as three integral
492 fields: months, days, and microseconds. These fields are kept
493 separate because the number of days in a month varies, while a day
494 can have 23 or 25 hours if a daylight savings time transition is
495 involved. An interval input string that uses other units is
496 normalized into this format, and then reconstructed in a standardized
497 way for output, for example:
499 </p><pre class="programlisting">
500 SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval;
502 ---------------------------------------
503 3 years 3 mons 700 days 133:17:36.789
506 Here weeks, which are understood as <span class="quote">“<span class="quote">7 days</span>”</span>, have been
507 kept separate, while the smaller and larger time units were
508 combined and normalized.
510 Input field values can have fractional parts, for example <code class="literal">'1.5
511 weeks'</code> or <code class="literal">'01:02:03.45'</code>. However,
512 because <code class="type">interval</code> internally stores only integral fields,
513 fractional values must be converted into smaller
514 units. Fractional parts of units greater than months are rounded to
515 be an integer number of months, e.g. <code class="literal">'1.5 years'</code>
516 becomes <code class="literal">'1 year 6 mons'</code>. Fractional parts of
517 weeks and days are computed to be an integer number of days and
518 microseconds, assuming 30 days per month and 24 hours per day, e.g.,
519 <code class="literal">'1.75 months'</code> becomes <code class="literal">1 mon 22 days
520 12:00:00</code>. Only seconds will ever be shown as fractional
523 <a class="xref" href="datatype-datetime.html#DATATYPE-INTERVAL-INPUT-EXAMPLES" title="Table 8.17. Interval Input">Table 8.17</a> shows some examples
524 of valid <code class="type">interval</code> input.
525 </p><div class="table" id="DATATYPE-INTERVAL-INPUT-EXAMPLES"><p class="title"><strong>Table 8.17. Interval Input</strong></p><div class="table-contents"><table class="table" summary="Interval Input" border="1"><colgroup><col /><col /></colgroup><thead><tr><th>Example</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">1-2</code></td><td>SQL standard format: 1 year 2 months</td></tr><tr><td><code class="literal">3 4:05:06</code></td><td>SQL standard format: 3 days 4 hours 5 minutes 6 seconds</td></tr><tr><td><code class="literal">1 year 2 months 3 days 4 hours 5 minutes 6 seconds</code></td><td>Traditional Postgres format: 1 year 2 months 3 days 4 hours 5 minutes 6 seconds</td></tr><tr><td><code class="literal">P1Y2M3DT4H5M6S</code></td><td>ISO 8601 <span class="quote">“<span class="quote">format with designators</span>”</span>: same meaning as above</td></tr><tr><td><code class="literal">P0001-02-03T04:05:06</code></td><td>ISO 8601 <span class="quote">“<span class="quote">alternative format</span>”</span>: same meaning as above</td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="DATATYPE-INTERVAL-OUTPUT"><div class="titlepage"><div><div><h3 class="title">8.5.5. Interval Output <a href="#DATATYPE-INTERVAL-OUTPUT" class="id_link">#</a></h3></div></div></div><a id="id-1.5.7.13.22.2" class="indexterm"></a><p>
526 As previously explained, <span class="productname">PostgreSQL</span>
527 stores <code class="type">interval</code> values as months, days, and
528 microseconds. For output, the months field is converted to years and
529 months by dividing by 12. The days field is shown as-is. The
530 microseconds field is converted to hours, minutes, seconds, and
531 fractional seconds. Thus months, minutes, and seconds will never be
532 shown as exceeding the ranges 0–11, 0–59, and 0–59
533 respectively, while the displayed years, days, and hours fields can
534 be quite large. (The <a class="link" href="functions-datetime.html#FUNCTION-JUSTIFY-DAYS"><code class="function">justify_days</code></a>
535 and <a class="link" href="functions-datetime.html#FUNCTION-JUSTIFY-HOURS"><code class="function">justify_hours</code></a>
536 functions can be used if it is desirable to transpose large days or
537 hours values into the next higher field.)
539 The output format of the interval type can be set to one of the
540 four styles <code class="literal">sql_standard</code>, <code class="literal">postgres</code>,
541 <code class="literal">postgres_verbose</code>, or <code class="literal">iso_8601</code>,
542 using the command <code class="literal">SET intervalstyle</code>.
543 The default is the <code class="literal">postgres</code> format.
544 <a class="xref" href="datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE" title="Table 8.18. Interval Output Style Examples">Table 8.18</a> shows examples of each
547 The <code class="literal">sql_standard</code> style produces output that conforms to
548 the SQL standard's specification for interval literal strings, if
549 the interval value meets the standard's restrictions (either year-month
550 only or day-time only, with no mixing of positive
551 and negative components). Otherwise the output looks like a standard
552 year-month literal string followed by a day-time literal string,
553 with explicit signs added to disambiguate mixed-sign intervals.
555 The output of the <code class="literal">postgres</code> style matches the output of
556 <span class="productname">PostgreSQL</span> releases prior to 8.4 when the
557 <a class="xref" href="runtime-config-client.html#GUC-DATESTYLE">DateStyle</a> parameter was set to <code class="literal">ISO</code>.
559 The output of the <code class="literal">postgres_verbose</code> style matches the output of
560 <span class="productname">PostgreSQL</span> releases prior to 8.4 when the
561 <code class="varname">DateStyle</code> parameter was set to non-<code class="literal">ISO</code> output.
563 The output of the <code class="literal">iso_8601</code> style matches the <span class="quote">“<span class="quote">format
564 with designators</span>”</span> described in section 4.4.3.2 of the
566 </p><div class="table" id="INTERVAL-STYLE-OUTPUT-TABLE"><p class="title"><strong>Table 8.18. Interval Output Style Examples</strong></p><div class="table-contents"><table class="table" summary="Interval Output Style Examples" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Style Specification</th><th>Year-Month Interval</th><th>Day-Time Interval</th><th>Mixed Interval</th></tr></thead><tbody><tr><td><code class="literal">sql_standard</code></td><td>1-2</td><td>3 4:05:06</td><td>-1-2 +3 -4:05:06</td></tr><tr><td><code class="literal">postgres</code></td><td>1 year 2 mons</td><td>3 days 04:05:06</td><td>-1 year -2 mons +3 days -04:05:06</td></tr><tr><td><code class="literal">postgres_verbose</code></td><td>@ 1 year 2 mons</td><td>@ 3 days 4 hours 5 mins 6 secs</td><td>@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago</td></tr><tr><td><code class="literal">iso_8601</code></td><td>P1Y2M</td><td>P3DT4H5M6S</td><td>P-1Y-2M3DT-4H-5M-6S</td></tr></tbody></table></div></div><br class="table-break" /></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="datatype-binary.html" title="8.4. Binary Data Types">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="datatype.html" title="Chapter 8. Data Types">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="datatype-boolean.html" title="8.6. Boolean Type">Next</a></td></tr><tr><td width="40%" align="left" valign="top">8.4. Binary Data Types </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.6. Boolean Type</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob