4
4
<title>8.5.�Date/Time Types</title>
5
5
<link rel="stylesheet" href="stylesheet.css" type="text/css">
6
6
<link rev="made" href="pgsql-docs@postgresql.org">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.64.1">
8
<link rel="home" href="index.html" title="PostgreSQL 8.0.0beta5 Documentation">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
8
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
9
9
<link rel="up" href="datatype.html" title="Chapter�8.�Data Types">
10
<link rel="previous" href="datatype-binary.html" title="8.4.�Binary Data Types">
10
<link rel="prev" href="datatype-binary.html" title="8.4.�Binary Data Types">
11
11
<link rel="next" href="datatype-boolean.html" title="8.6.�Boolean Type">
12
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
13
14
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
14
<div class="titlepage">
15
<div><div><h2 class="title" style="clear: both">
16
<a name="datatype-datetime"></a>8.5.�Date/Time Types</h2></div></div>
19
<a name="id2533558"></a><a name="id2533570"></a><a name="id2533579"></a><a name="id2533588"></a><a name="id2533597"></a><a name="id2533607"></a><a name="id2533616"></a><a name="id2533626"></a><a name="id2533635"></a><p> <span class="productname">PostgreSQL</span> supports the full set of
20
<span class="acronym">SQL</span> date and time types, shown in <a href="datatype-datetime.html#datatype-datetime-table" title="Table�8.9.�Date/Time Types">Table�8.9, “Date/Time Types”</a>.
15
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
16
<a name="datatype-datetime"></a>8.5.�Date/Time Types</h2></div></div></div>
17
<a name="id586767"></a><a name="id586779"></a><a name="id586790"></a><a name="id586800"></a><a name="id586810"></a><a name="id586822"></a><a name="id586832"></a><a name="id586843"></a><a name="id586854"></a><p> <span class="productname">PostgreSQL</span> supports the full set of
18
<acronym class="acronym">SQL</acronym> date and time types, shown in <a href="datatype-datetime.html#datatype-datetime-table" title="Table�8.9.�Date/Time Types">Table�8.9, “Date/Time Types”</a>. The operations available
19
on these data types are described in
20
<a href="functions-datetime.html" title="9.9.�Date/Time Functions and Operators">Section�9.9, “Date/Time Functions and Operators”</a>.
22
22
<div class="table">
23
23
<a name="datatype-datetime-table"></a><p class="title"><b>Table�8.9.�Date/Time Types</b></p>
24
<table summary="Date/Time Types" border="1">
24
<div class="table-contents"><table summary="Date/Time Types" border="1">
56
56
<td>1 microsecond / 14 digits</td>
59
<td><tt class="type">interval [ (<i class="replaceable"><tt>p</tt></i>) ]</tt></td>
59
<td><code class="type">interval [ (<em class="replaceable"><code>p</code></em>) ]</code></td>
61
61
<td>time intervals</td>
62
62
<td>-178000000 years</td>
63
63
<td>178000000 years</td>
64
<td>1 microsecond</td>
64
<td>1 microsecond / 14 digits</td>
67
<td><tt class="type">date</tt></td>
67
<td><code class="type">date</code></td>
69
69
<td>dates only</td>
75
<td><tt class="type">time [ (<i class="replaceable"><tt>p</tt></i>) ] [ without time zone ]</tt></td>
75
<td><code class="type">time [ (<em class="replaceable"><code>p</code></em>) ] [ without time zone ]</code></td>
77
77
<td>times of day only</td>
80
<td>1 microsecond</td>
80
<td>1 microsecond / 14 digits</td>
83
<td><tt class="type">time [ (<i class="replaceable"><tt>p</tt></i>) ] with time zone</tt></td>
83
<td><code class="type">time [ (<em class="replaceable"><code>p</code></em>) ] with time zone</code></td>
85
85
<td>times of day only, with time zone</td>
86
<td>00:00:00.00+12</td>
87
<td>23:59:59.99-12</td>
88
<td>1 microsecond</td>
86
<td>00:00:00+1359</td>
87
<td>24:00:00-1359</td>
88
<td>1 microsecond / 14 digits</td>
93
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
93
<br class="table-break"><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
94
94
<h3 class="title">Note</h3>
95
95
<p> Prior to <span class="productname">PostgreSQL</span> 7.3, writing just
96
<tt class="type">timestamp</tt> was equivalent to <tt class="type">timestamp with
97
time zone</tt>. This was changed for SQL compliance.
96
<code class="type">timestamp</code> was equivalent to <code class="type">timestamp with
97
time zone</code>. This was changed for SQL compliance.
100
<p> <tt class="type">time</tt>, <tt class="type">timestamp</tt>, and
101
<tt class="type">interval</tt> accept an optional precision value
102
<i class="replaceable"><tt>p</tt></i> which specifies the number of
100
<p> <code class="type">time</code>, <code class="type">timestamp</code>, and
101
<code class="type">interval</code> accept an optional precision value
102
<em class="replaceable"><code>p</code></em> which specifies the number of
103
103
fractional digits retained in the seconds field. By default, there
104
104
is no explicit bound on precision. The allowed range of
105
<i class="replaceable"><tt>p</tt></i> is from 0 to 6 for the
106
<tt class="type">timestamp</tt> and <tt class="type">interval</tt> types.
105
<em class="replaceable"><code>p</code></em> is from 0 to 6 for the
106
<code class="type">timestamp</code> and <code class="type">interval</code> types.
108
108
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
109
109
<h3 class="title">Note</h3>
110
<p> When <tt class="type">timestamp</tt> values are stored as double precision floating-point
110
<p> When <code class="type">timestamp</code> values are stored as double precision floating-point
111
111
numbers (currently the default), the effective limit of precision
112
may be less than 6. <tt class="type">timestamp</tt> values are stored as seconds
112
may be less than 6. <code class="type">timestamp</code> values are stored as seconds
113
113
before or after midnight 2000-01-01. Microsecond precision is achieved for
114
114
dates within a few years of 2000-01-01, but the precision degrades for
115
dates further away. When <tt class="type">timestamp</tt> values are stored as
115
dates further away. When <code class="type">timestamp</code> values are stored as
116
116
eight-byte integers (a compile-time
117
117
option), microsecond precision is available over the full range of
118
118
values. However eight-byte integer timestamps have a more limited range of
119
dates than shown above: from 4713 BC up to 294276 AD.
119
dates than shown above: from 4713 BC up to 294276 AD. The same
120
compile-time option also determines whether <code class="type">time</code> and
121
<code class="type">interval</code> values are stored as floating-point or eight-byte
122
integers. In the floating-point case, large <code class="type">interval</code> values
123
degrade in precision as the size of the interval increases.
122
<p> For the <tt class="type">time</tt> types, the allowed range of
123
<i class="replaceable"><tt>p</tt></i> is from 0 to 6 when eight-byte integer
126
<p> For the <code class="type">time</code> types, the allowed range of
127
<em class="replaceable"><code>p</code></em> is from 0 to 6 when eight-byte integer
124
128
storage is used, or from 0 to 10 when floating-point storage is used.
126
<p> The type <tt class="type">time with time zone</tt> is defined by the SQL
130
<p> The type <code class="type">time with time zone</code> is defined by the SQL
127
131
standard, but the definition exhibits properties which lead to
128
132
questionable usefulness. In most cases, a combination of
129
<tt class="type">date</tt>, <tt class="type">time</tt>, <tt class="type">timestamp without time
130
zone</tt>, and <tt class="type">timestamp with time zone</tt> should
133
<code class="type">date</code>, <code class="type">time</code>, <code class="type">timestamp without time
134
zone</code>, and <code class="type">timestamp with time zone</code> should
131
135
provide a complete range of date/time functionality required by
134
<p> The types <tt class="type">abstime</tt>
135
and <tt class="type">reltime</tt> are lower precision types which are used internally.
138
<p> The types <code class="type">abstime</code>
139
and <code class="type">reltime</code> are lower precision types which are used internally.
136
140
You are discouraged from using these types in new
137
141
applications and are encouraged to move any old
138
142
ones over when appropriate. Any or all of these internal types
139
143
might disappear in a future release.
141
145
<div class="sect2" lang="en">
142
<div class="titlepage">
143
<div><div><h3 class="title">
144
<a name="datatype-datetime-input"></a>8.5.1.�Date/Time Input</h3></div></div>
146
<div class="titlepage"><div><div><h3 class="title">
147
<a name="datatype-datetime-input"></a>8.5.1.�Date/Time Input</h3></div></div></div>
147
148
<p> Date and time input is accepted in almost any reasonable format, including
148
ISO 8601, <span class="acronym">SQL</span>-compatible,
149
ISO 8601, <acronym class="acronym">SQL</acronym>-compatible,
149
150
traditional <span class="productname">POSTGRES</span>, and others.
150
151
For some formats, ordering of month, day, and year in date input is
151
152
ambiguous and there is support for specifying the expected
152
ordering of these fields. Set the <a href="runtime-config.html#guc-datestyle">DateStyle</a> parameter
153
to <tt class="literal">MDY</tt> to select month-day-year interpretation,
154
<tt class="literal">DMY</tt> to select day-month-year interpretation, or
155
<tt class="literal">YMD</tt> to select year-month-day interpretation.
153
ordering of these fields. Set the <a href="runtime-config-client.html#guc-datestyle">DateStyle</a> parameter
154
to <code class="literal">MDY</code> to select month-day-year interpretation,
155
<code class="literal">DMY</code> to select day-month-year interpretation, or
156
<code class="literal">YMD</code> to select year-month-day interpretation.
157
158
<p> <span class="productname">PostgreSQL</span> is more flexible in
158
159
handling date/time input than the
159
<span class="acronym">SQL</span> standard requires.
160
<acronym class="acronym">SQL</acronym> standard requires.
160
161
See <a href="datetime-appendix.html" title="Appendix�B.�Date/Time Support">Appendix�B, <i>Date/Time Support</i></a>
161
162
for the exact parsing rules of date/time input and for the
162
163
recognized text fields including months, days of the week, and
166
167
in single quotes, like text strings. Refer to
167
168
<a href="sql-syntax.html#sql-syntax-constants-generic" title="4.1.2.5.�Constants of Other Types">Section�4.1.2.5, “Constants of Other Types”</a> for more
169
<span class="acronym">SQL</span> requires the following syntax
170
<acronym class="acronym">SQL</acronym> requires the following syntax
171
<pre class="synopsis"><i class="replaceable"><tt>type</tt></i> [ (<i class="replaceable"><tt>p</tt></i>) ] '<i class="replaceable"><tt>value</tt></i>'</pre>
172
<pre class="synopsis"><em class="replaceable"><code>type</code></em> [ (<em class="replaceable"><code>p</code></em>) ] '<em class="replaceable"><code>value</code></em>'</pre>
173
where <i class="replaceable"><tt>p</tt></i> in the optional precision
174
where <em class="replaceable"><code>p</code></em> in the optional precision
174
175
specification is an integer corresponding to the number of
175
176
fractional digits in the seconds field. Precision can be
176
specified for <tt class="type">time</tt>, <tt class="type">timestamp</tt>, and
177
<tt class="type">interval</tt> types. The allowed values are mentioned
177
specified for <code class="type">time</code>, <code class="type">timestamp</code>, and
178
<code class="type">interval</code> types. The allowed values are mentioned
178
179
above. If no precision is specified in a constant specification,
179
180
it defaults to the precision of the literal value.
181
182
<div class="sect3" lang="en">
182
<div class="titlepage">
183
<div><div><h4 class="title">
184
<a name="id2534063"></a>8.5.1.1.�Dates</h4></div></div>
187
<a name="id2534066"></a><p> <a href="datatype-datetime.html#datatype-datetime-date-table" title="Table�8.10.�Date Input">Table�8.10, “Date Input”</a> shows some possible
188
inputs for the <tt class="type">date</tt> type.
183
<div class="titlepage"><div><div><h4 class="title">
184
<a name="id587357"></a>8.5.1.1.�Dates</h4></div></div></div>
185
<a name="id587360"></a><p> <a href="datatype-datetime.html#datatype-datetime-date-table" title="Table�8.10.�Date Input">Table�8.10, “Date Input”</a> shows some possible
186
inputs for the <code class="type">date</code> type.
190
188
<div class="table">
191
189
<a name="datatype-datetime-date-table"></a><p class="title"><b>Table�8.10.�Date Input</b></p>
192
<table summary="Date Input" border="1">
190
<div class="table-contents"><table summary="Date Input" border="1">
307
<td><tt class="literal">04:05:06.789</tt></td>
311
<td><tt class="literal">04:05:06</tt></td>
315
<td><tt class="literal">04:05</tt></td>
319
<td><tt class="literal">040506</tt></td>
323
<td><tt class="literal">04:05 AM</tt></td>
303
<td><code class="literal">04:05:06.789</code></td>
307
<td><code class="literal">04:05:06</code></td>
311
<td><code class="literal">04:05</code></td>
315
<td><code class="literal">040506</code></td>
319
<td><code class="literal">04:05 AM</code></td>
324
320
<td>same as 04:05; AM does not affect value</td>
327
<td><tt class="literal">04:05 PM</tt></td>
323
<td><code class="literal">04:05 PM</code></td>
328
324
<td>same as 16:05; input hour must be <= 12</td>
331
<td><tt class="literal">04:05:06.789-8</tt></td>
335
<td><tt class="literal">04:05:06-08:00</tt></td>
339
<td><tt class="literal">04:05-08:00</tt></td>
343
<td><tt class="literal">040506-08</tt></td>
347
<td><tt class="literal">04:05:06 PST</tt></td>
327
<td><code class="literal">04:05:06.789-8</code></td>
331
<td><code class="literal">04:05:06-08:00</code></td>
335
<td><code class="literal">04:05-08:00</code></td>
339
<td><code class="literal">040506-08</code></td>
343
<td><code class="literal">04:05:06 PST</code></td>
348
344
<td>time zone specified by name</td>
349
<br class="table-break"><div class="table">
354
350
<a name="datatype-timezone-table"></a><p class="title"><b>Table�8.12.�Time Zone Input</b></p>
355
<table summary="Time Zone Input" border="1">
351
<div class="table-contents"><table summary="Time Zone Input" border="1">
366
<td><tt class="literal">PST</tt></td>
362
<td><code class="literal">PST</code></td>
367
363
<td>Pacific Standard Time</td>
370
<td><tt class="literal">-8:00</tt></td>
371
<td>ISO-8601 offset for PST</td>
374
<td><tt class="literal">-800</tt></td>
375
<td>ISO-8601 offset for PST</td>
378
<td><tt class="literal">-8</tt></td>
379
<td>ISO-8601 offset for PST</td>
382
<td><tt class="literal">zulu</tt></td>
366
<td><code class="literal">-8:00</code></td>
367
<td>ISO-8601 offset for PST</td>
370
<td><code class="literal">-800</code></td>
371
<td>ISO-8601 offset for PST</td>
374
<td><code class="literal">-8</code></td>
375
<td>ISO-8601 offset for PST</td>
378
<td><code class="literal">zulu</code></td>
383
379
<td>Military abbreviation for UTC</td>
386
<td><tt class="literal">z</tt></td>
387
<td>Short form of <tt class="literal">zulu</tt>
382
<td><code class="literal">z</code></td>
383
<td>Short form of <code class="literal">zulu</code>
393
<p> Refer to <a href="datetime-appendix.html" title="Appendix�B.�Date/Time Support">Appendix�B, <i>Date/Time Support</i></a> for a list of
389
<br class="table-break"><p> Refer to <a href="datetime-appendix.html" title="Appendix�B.�Date/Time Support">Appendix�B, <i>Date/Time Support</i></a> for a list of
394
390
time zone names that are recognized for input.
397
393
<div class="sect3" lang="en">
398
<div class="titlepage">
399
<div><div><h4 class="title">
400
<a name="id2534548"></a>8.5.1.3.�Time Stamps</h4></div></div>
403
<a name="id2534551"></a><a name="id2534557"></a><a name="id2534563"></a><p> Valid input for the time stamp types consists of a concatenation
394
<div class="titlepage"><div><div><h4 class="title">
395
<a name="id587895"></a>8.5.1.3.�Time Stamps</h4></div></div></div>
396
<a name="id587899"></a><a name="id587905"></a><a name="id587912"></a><p> Valid input for the time stamp types consists of a concatenation
404
397
of a date and a time, followed by an optional time zone,
405
followed by an optional <tt class="literal">AD</tt> or <tt class="literal">BC</tt>.
406
(Alternatively, <tt class="literal">AD</tt>/<tt class="literal">BC</tt> can appear
398
followed by an optional <code class="literal">AD</code> or <code class="literal">BC</code>.
399
(Alternatively, <code class="literal">AD</code>/<code class="literal">BC</code> can appear
407
400
before the time zone, but this is not the preferred ordering.)
426
<p> For <tt class="type">timestamp [without time zone]</tt>, any explicit time
427
zone specified in the input is silently ignored. That is, the
428
resulting date/time value is derived from the explicit date/time
419
<p> The <acronym class="acronym">SQL</acronym> standard differentiates <code class="type">timestamp without time zone</code>
420
and <code class="type">timestamp with time zone</code> literals by the presence of a
421
“<span class="quote">+</span>” or “<span class="quote">-</span>”. Hence, according to the standard,
423
<pre class="programlisting">TIMESTAMP '2004-10-19 10:23:54'</pre>
425
is a <code class="type">timestamp without time zone</code>, while
427
<pre class="programlisting">TIMESTAMP '2004-10-19 10:23:54+02'</pre>
429
is a <code class="type">timestamp with time zone</code>.
430
<span class="productname">PostgreSQL</span> never examines the content of a
431
literal string before determining its type, and therefore will treat
432
both of the above as <code class="type">timestamp without time zone</code>. To
433
ensure that a literal is treated as <code class="type">timestamp with time
434
zone</code>, give it the correct explicit type:
436
<pre class="programlisting">TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'</pre>
438
In a literal that has been decided to be <code class="type">timestamp without time
439
zone</code>, <span class="productname">PostgreSQL</span> will silently ignore
440
any time zone indication.
441
That is, the resulting value is derived from the date/time
429
442
fields in the input value, and is not adjusted for time zone.
431
<p> For <tt class="type">timestamp with time zone</tt>, the internally stored
444
<p> For <code class="type">timestamp with time zone</code>, the internally stored
432
445
value is always in UTC (Universal
433
446
Coordinated Time, traditionally known as Greenwich Mean Time,
434
<span class="acronym">GMT</span>). An input value that has an explicit
447
<acronym class="acronym">GMT</acronym>). An input value that has an explicit
435
448
time zone specified is converted to UTC using the appropriate offset
436
449
for that time zone. If no time zone is stated in the input string,
437
450
then it is assumed to be in the time zone indicated by the system's
438
<a href="runtime-config.html#guc-timezone">timezone</a> parameter, and is converted to UTC using the
439
offset for the <tt class="varname">timezone</tt> zone.
451
<a href="runtime-config-client.html#guc-timezone">timezone</a> parameter, and is converted to UTC using the
452
offset for the <code class="varname">timezone</code> zone.
441
<p> When a <tt class="type">timestamp with time
442
zone</tt> value is output, it is always converted from UTC to the
443
current <tt class="varname">timezone</tt> zone, and displayed as local time in that
454
<p> When a <code class="type">timestamp with time
455
zone</code> value is output, it is always converted from UTC to the
456
current <code class="varname">timezone</code> zone, and displayed as local time in that
444
457
zone. To see the time in another time zone, either change
445
<tt class="varname">timezone</tt> or use the <tt class="literal">AT TIME ZONE</tt> construct
446
(see <a href="functions-datetime.html#functions-datetime-zoneconvert" title="9.9.3.�AT TIME ZONE">Section�9.9.3, “AT TIME ZONE”</a>).
458
<code class="varname">timezone</code> or use the <code class="literal">AT TIME ZONE</code> construct
459
(see <a href="functions-datetime.html#functions-datetime-zoneconvert" title="9.9.3.�AT TIME ZONE">Section�9.9.3, “<code class="literal">AT TIME ZONE</code>”</a>).
448
<p> Conversions between <tt class="type">timestamp without time zone</tt> and
449
<tt class="type">timestamp with time zone</tt> normally assume that the
450
<tt class="type">timestamp without time zone</tt> value should be taken or given
451
as <tt class="varname">timezone</tt> local time. A different zone reference can
452
be specified for the conversion using <tt class="literal">AT TIME ZONE</tt>.
461
<p> Conversions between <code class="type">timestamp without time zone</code> and
462
<code class="type">timestamp with time zone</code> normally assume that the
463
<code class="type">timestamp without time zone</code> value should be taken or given
464
as <code class="varname">timezone</code> local time. A different zone reference can
465
be specified for the conversion using <code class="literal">AT TIME ZONE</code>.
455
468
<div class="sect3" lang="en">
456
<div class="titlepage">
457
<div><div><h4 class="title">
458
<a name="id2534720"></a>8.5.1.4.�Intervals</h4></div></div>
461
<a name="id2534723"></a><p> <tt class="type">interval</tt> values can be written with the following syntax:
469
<div class="titlepage"><div><div><h4 class="title">
470
<a name="id588164"></a>8.5.1.4.�Intervals</h4></div></div></div>
471
<a name="id588168"></a><p> <code class="type">interval</code> values can be written with the following syntax:
464
<pre class="programlisting">[<span class="optional">@</span>] <i class="replaceable"><tt>quantity</tt></i> <i class="replaceable"><tt>unit</tt></i> [<span class="optional"><i class="replaceable"><tt>quantity</tt></i> <i class="replaceable"><tt>unit</tt></i>...</span>] [<span class="optional"><i class="replaceable"><tt>direction</tt></i></span>]</pre>
474
<pre class="programlisting">[<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>]</pre>
467
Where: <i class="replaceable"><tt>quantity</tt></i> is a number (possibly signed);
468
<i class="replaceable"><tt>unit</tt></i> is <tt class="literal">second</tt>,
469
<tt class="literal">minute</tt>, <tt class="literal">hour</tt>, <tt class="literal">day</tt>,
470
<tt class="literal">week</tt>, <tt class="literal">month</tt>, <tt class="literal">year</tt>,
471
<tt class="literal">decade</tt>, <tt class="literal">century</tt>, <tt class="literal">millennium</tt>,
477
Where: <em class="replaceable"><code>quantity</code></em> is a number (possibly signed);
478
<em class="replaceable"><code>unit</code></em> is <code class="literal">second</code>,
479
<code class="literal">minute</code>, <code class="literal">hour</code>, <code class="literal">day</code>,
480
<code class="literal">week</code>, <code class="literal">month</code>, <code class="literal">year</code>,
481
<code class="literal">decade</code>, <code class="literal">century</code>, <code class="literal">millennium</code>,
472
482
or abbreviations or plurals of these units;
473
<i class="replaceable"><tt>direction</tt></i> can be <tt class="literal">ago</tt> or
474
empty. The at sign (<tt class="literal">@</tt>) is optional noise. The amounts
483
<em class="replaceable"><code>direction</code></em> can be <code class="literal">ago</code> or
484
empty. The at sign (<code class="literal">@</code>) is optional noise. The amounts
475
485
of different units are implicitly added up with appropriate
478
488
<p> Quantities of days, hours, minutes, and seconds can be specified without
479
explicit unit markings. For example, <tt class="literal">'1 12:59:10'</tt> is read
480
the same as <tt class="literal">'1 day 12 hours 59 min 10 sec'</tt>.
482
<p> The optional precision
483
<i class="replaceable"><tt>p</tt></i> should be between 0 and 6, and
484
defaults to the precision of the input literal.
489
explicit unit markings. For example, <code class="literal">'1 12:59:10'</code> is read
490
the same as <code class="literal">'1 day 12 hours 59 min 10 sec'</code>.
492
<p> The optional subsecond precision <em class="replaceable"><code>p</code></em> should
493
be between 0 and 6, and defaults to the precision of the input literal.
495
<p> Internally <code class="type">interval</code> values are stored as months, days,
496
and seconds. This is done because the number of days in a month
497
varies, and a day can have 23 or 25 hours if a daylight savings
498
time adjustment is involved. Because intervals are usually created
499
from constant strings or <code class="type">timestamp</code> subtraction, this
500
storage method works well in most cases. Functions
501
<code class="function">justify_days</code> and <code class="function">justify_hours</code> are
502
available for adjusting days and hours that overflow their normal
487
506
<div class="sect3" lang="en">
488
<div class="titlepage">
489
<div><div><h4 class="title">
490
<a name="id2534872"></a>8.5.1.5.�Special Values</h4></div></div>
493
<a name="id2534876"></a><a name="id2534884"></a><p> <span class="productname">PostgreSQL</span> supports several
507
<div class="titlepage"><div><div><h4 class="title">
508
<a name="id588366"></a>8.5.1.5.�Special Values</h4></div></div></div>
509
<a name="id588370"></a><a name="id588379"></a><p> <span class="productname">PostgreSQL</span> supports several
494
510
special date/time input values for convenience, as shown in <a href="datatype-datetime.html#datatype-datetime-special-table" title="Table�8.13.�Special Date/Time Inputs">Table�8.13, “Special Date/Time Inputs”</a>. The values
495
<tt class="literal">infinity</tt> and <tt class="literal">-infinity</tt>
511
<code class="literal">infinity</code> and <code class="literal">-infinity</code>
496
512
are specially represented inside the system and will be displayed
497
513
the same way; but the others are simply notational shorthands
498
514
that will be converted to ordinary date/time values when read.
499
All of these values are treated as normal constants and need to be
500
written in single quotes.
515
(In particular, <code class="literal">now</code> and related strings are converted
516
to a specific time value as soon as they are read.)
517
All of these values need to be written in single quotes when used
518
as constants in SQL commands.
502
520
<div class="table">
503
521
<a name="datatype-datetime-special-table"></a><p class="title"><b>Table�8.13.�Special Date/Time Inputs</b></p>
504
<table summary="Special Date/Time Inputs" border="1">
522
<div class="table-contents"><table summary="Special Date/Time Inputs" border="1">
516
<td><tt class="literal">epoch</tt></td>
534
<td><code class="literal">epoch</code></td>
518
<tt class="type">date</tt>, <tt class="type">timestamp</tt>
536
<code class="type">date</code>, <code class="type">timestamp</code>
520
538
<td>1970-01-01 00:00:00+00 (Unix system time zero)</td>
523
<td><tt class="literal">infinity</tt></td>
524
<td><tt class="type">timestamp</tt></td>
541
<td><code class="literal">infinity</code></td>
542
<td><code class="type">timestamp</code></td>
525
543
<td>later than all other time stamps</td>
528
<td><tt class="literal">-infinity</tt></td>
529
<td><tt class="type">timestamp</tt></td>
546
<td><code class="literal">-infinity</code></td>
547
<td><code class="type">timestamp</code></td>
530
548
<td>earlier than all other time stamps</td>
533
<td><tt class="literal">now</tt></td>
551
<td><code class="literal">now</code></td>
535
<tt class="type">date</tt>, <tt class="type">time</tt>, <tt class="type">timestamp</tt>
553
<code class="type">date</code>, <code class="type">time</code>, <code class="type">timestamp</code>
537
555
<td>current transaction's start time</td>
540
<td><tt class="literal">today</tt></td>
558
<td><code class="literal">today</code></td>
542
<tt class="type">date</tt>, <tt class="type">timestamp</tt>
560
<code class="type">date</code>, <code class="type">timestamp</code>
544
562
<td>midnight today</td>
547
<td><tt class="literal">tomorrow</tt></td>
565
<td><code class="literal">tomorrow</code></td>
549
<tt class="type">date</tt>, <tt class="type">timestamp</tt>
567
<code class="type">date</code>, <code class="type">timestamp</code>
551
569
<td>midnight tomorrow</td>
554
<td><tt class="literal">yesterday</tt></td>
572
<td><code class="literal">yesterday</code></td>
556
<tt class="type">date</tt>, <tt class="type">timestamp</tt>
574
<code class="type">date</code>, <code class="type">timestamp</code>
558
576
<td>midnight yesterday</td>
561
<td><tt class="literal">allballs</tt></td>
562
<td><tt class="type">time</tt></td>
579
<td><code class="literal">allballs</code></td>
580
<td><code class="type">time</code></td>
563
581
<td>00:00:00.00 UTC</td>
568
<p> The following <span class="acronym">SQL</span>-compatible functions can also
586
<br class="table-break"><p> The following <acronym class="acronym">SQL</acronym>-compatible functions can also
569
587
be used to obtain the current time value for the corresponding data
571
<tt class="literal">CURRENT_DATE</tt>, <tt class="literal">CURRENT_TIME</tt>,
572
<tt class="literal">CURRENT_TIMESTAMP</tt>, <tt class="literal">LOCALTIME</tt>,
573
<tt class="literal">LOCALTIMESTAMP</tt>. The latter four accept an
574
optional precision specification. (See also <a href="functions-datetime.html#functions-datetime-current" title="9.9.4.�Current Date/Time">Section�9.9.4, “Current Date/Time”</a>.) Note however that these are
589
<code class="literal">CURRENT_DATE</code>, <code class="literal">CURRENT_TIME</code>,
590
<code class="literal">CURRENT_TIMESTAMP</code>, <code class="literal">LOCALTIME</code>,
591
<code class="literal">LOCALTIMESTAMP</code>. The latter four accept an
592
optional subsecond precision specification. (See <a href="functions-datetime.html#functions-datetime-current" title="9.9.4.�Current Date/Time">Section�9.9.4, “Current Date/Time”</a>.) Note however that these are
575
593
SQL functions and are <span class="emphasis"><em>not</em></span> recognized as data input strings.
579
597
<div class="sect2" lang="en">
580
<div class="titlepage">
581
<div><div><h3 class="title">
582
<a name="datatype-datetime-output"></a>8.5.2.�Date/Time Output</h3></div></div>
585
<a name="id2535138"></a><a name="id2535149"></a><p> The output format of the date/time types can be set to one of the four
598
<div class="titlepage"><div><div><h3 class="title">
599
<a name="datatype-datetime-output"></a>8.5.2.�Date/Time Output</h3></div></div></div>
600
<a name="id588665"></a><a name="id588677"></a><p> The output format of the date/time types can be set to one of the four
587
<span class="acronym">SQL</span> (Ingres), traditional POSTGRES, and
588
German, using the command <tt class="literal">SET datestyle</tt>. The default
589
is the <span class="acronym">ISO</span> format. (The
590
<span class="acronym">SQL</span> standard requires the use of the ISO 8601
602
<acronym class="acronym">SQL</acronym> (Ingres), traditional POSTGRES, and
603
German, using the command <code class="literal">SET datestyle</code>. The default
604
is the <acronym class="acronym">ISO</acronym> format. (The
605
<acronym class="acronym">SQL</acronym> standard requires the use of the ISO 8601
591
606
format. The name of the “<span class="quote">SQL</span>” output format is a
592
607
historical accident.) <a href="datatype-datetime.html#datatype-datetime-output-table" title="Table�8.14.�Date/Time Output Styles">Table�8.14, “Date/Time Output Styles”</a> shows examples of each
593
output style. The output of the <tt class="type">date</tt> and
594
<tt class="type">time</tt> types is of course only the date or time part
608
output style. The output of the <code class="type">date</code> and
609
<code class="type">time</code> types is of course only the date or time part
595
610
in accordance with the given examples.
597
612
<div class="table">
598
613
<a name="datatype-datetime-output-table"></a><p class="title"><b>Table�8.14.�Date/Time Output Styles</b></p>
599
<table summary="Date/Time Output Styles" border="1">
614
<div class="table-contents"><table summary="Date/Time Output Styles" border="1">
652
<tt class="varname">datestyle</tt> Setting</th>
667
<code class="varname">datestyle</code> Setting</th>
653
668
<th>Input Ordering</th>
654
669
<th>Example Output</th>
658
<td><tt class="literal">SQL, DMY</tt></td>
673
<td><code class="literal">SQL, DMY</code></td>
660
<i class="replaceable"><tt>day</tt></i>/<i class="replaceable"><tt>month</tt></i>/<i class="replaceable"><tt>year</tt></i>
675
<em class="replaceable"><code>day</code></em>/<em class="replaceable"><code>month</code></em>/<em class="replaceable"><code>year</code></em>
662
677
<td>17/12/1997 15:37:16.00 CET</td>
665
<td><tt class="literal">SQL, MDY</tt></td>
680
<td><code class="literal">SQL, MDY</code></td>
667
<i class="replaceable"><tt>month</tt></i>/<i class="replaceable"><tt>day</tt></i>/<i class="replaceable"><tt>year</tt></i>
682
<em class="replaceable"><code>month</code></em>/<em class="replaceable"><code>day</code></em>/<em class="replaceable"><code>year</code></em>
669
684
<td>12/17/1997 07:37:16.00 PST</td>
672
<td><tt class="literal">Postgres, DMY</tt></td>
687
<td><code class="literal">Postgres, DMY</code></td>
674
<i class="replaceable"><tt>day</tt></i>/<i class="replaceable"><tt>month</tt></i>/<i class="replaceable"><tt>year</tt></i>
689
<em class="replaceable"><code>day</code></em>/<em class="replaceable"><code>month</code></em>/<em class="replaceable"><code>year</code></em>
676
691
<td>Wed 17 Dec 07:37:16 1997 PST</td>
681
<p> <tt class="type">interval</tt> output looks like the input format, except
682
that units like <tt class="literal">century</tt> or
683
<tt class="literal">week</tt> are converted to years and days and
684
<tt class="literal">ago</tt> is converted to an appropriate sign. In
696
<br class="table-break"><p> <code class="type">interval</code> output looks like the input format, except
697
that units like <code class="literal">century</code> or
698
<code class="literal">week</code> are converted to years and days and
699
<code class="literal">ago</code> is converted to an appropriate sign. In
685
700
ISO mode the output looks like
688
<pre class="programlisting">[<span class="optional"> <i class="replaceable"><tt>quantity</tt></i> <i class="replaceable"><tt>unit</tt></i> [<span class="optional"> ... </span>] </span>] [<span class="optional"> <i class="replaceable"><tt>days</tt></i> </span>] [<span class="optional"> <i class="replaceable"><tt>hours</tt></i>:<i class="replaceable"><tt>minutes</tt></i>:<i class="replaceable"><tt>seconds</tt></i> </span>]</pre>
703
<pre class="programlisting">[<span class="optional"> <em class="replaceable"><code>quantity</code></em> <em class="replaceable"><code>unit</code></em> [<span class="optional"> ... </span>] </span>] [<span class="optional"> <em class="replaceable"><code>days</code></em> </span>] [<span class="optional"> <em class="replaceable"><code>hours</code></em>:<em class="replaceable"><code>minutes</code></em>:<em class="replaceable"><code>seconds</code></em> </span>]</pre>
691
706
<p> The date/time styles can be selected by the user using the
692
<tt class="command">SET datestyle</tt> command, the <a href="runtime-config.html#guc-datestyle">DateStyle</a> parameter in the
693
<tt class="filename">postgresql.conf</tt> configuration file, or the
694
<tt class="envar">PGDATESTYLE</tt> environment variable on the server or
695
client. The formatting function <tt class="function">to_char</tt>
707
<code class="command">SET datestyle</code> command, the <a href="runtime-config-client.html#guc-datestyle">DateStyle</a> parameter in the
708
<code class="filename">postgresql.conf</code> configuration file, or the
709
<code class="envar">PGDATESTYLE</code> environment variable on the server or
710
client. The formatting function <code class="function">to_char</code>
696
711
(see <a href="functions-formatting.html" title="9.8.�Data Type Formatting Functions">Section�9.8, “Data Type Formatting Functions”</a>) is also available as
697
712
a more flexible way to format the date/time output.
700
715
<div class="sect2" lang="en">
701
<div class="titlepage">
702
<div><div><h3 class="title">
703
<a name="datatype-timezones"></a>8.5.3.�Time Zones</h3></div></div>
706
<a name="id2535483"></a><p> Time zones, and time-zone conventions, are influenced by
716
<div class="titlepage"><div><div><h3 class="title">
717
<a name="datatype-timezones"></a>8.5.3.�Time Zones</h3></div></div></div>
718
<a name="id589045"></a><p> Time zones, and time-zone conventions, are influenced by
707
719
political decisions, not just earth geometry. Time zones around the
708
720
world became somewhat standardized during the 1900's,
709
721
but continue to be prone to arbitrary changes, particularly with
715
727
matter what part of the year they fall in.
717
729
<p> <span class="productname">PostgreSQL</span> endeavors to be compatible with
718
the <span class="acronym">SQL</span> standard definitions for typical usage.
719
However, the <span class="acronym">SQL</span> standard has an odd mix of date and
730
the <acronym class="acronym">SQL</acronym> standard definitions for typical usage.
731
However, the <acronym class="acronym">SQL</acronym> standard has an odd mix of date and
720
732
time types and capabilities. Two obvious problems are:
723
735
<div class="itemizedlist"><ul type="disc">
724
<li><p> Although the <tt class="type">date</tt> type
736
<li><p> Although the <code class="type">date</code> type
725
737
does not have an associated time zone, the
726
<tt class="type">time</tt> type can.
738
<code class="type">time</code> type can.
727
739
Time zones in the real world have little meaning unless
728
740
associated with a date as well as a time,
729
741
since the offset may vary through the year with daylight-saving
732
744
<li><p> The default time zone is specified as a constant numeric offset
733
from <span class="acronym">UTC</span>. It is therefore not possible to adapt to
745
from <acronym class="acronym">UTC</acronym>. It is therefore not possible to adapt to
734
746
daylight-saving time when doing date/time arithmetic across
735
<span class="acronym">DST</span> boundaries.
747
<acronym class="acronym">DST</acronym> boundaries.
740
752
<p> To address these difficulties, we recommend using date/time types
741
753
that contain both date and time when using time zones. We
742
recommend <span class="emphasis"><em>not</em></span> using the type <tt class="type">time with
743
time zone</tt> (though it is supported by
754
recommend <span class="emphasis"><em>not</em></span> using the type <code class="type">time with
755
time zone</code> (though it is supported by
744
756
<span class="productname">PostgreSQL</span> for legacy applications and
745
for compliance with the <span class="acronym">SQL</span> standard).
757
for compliance with the <acronym class="acronym">SQL</acronym> standard).
746
758
<span class="productname">PostgreSQL</span> assumes
747
759
your local time zone for any type containing only date or time.
749
761
<p> All timezone-aware dates and times are stored internally in
750
<span class="acronym">UTC</span>. They are converted to local time
751
in the zone specified by the <a href="runtime-config.html#guc-timezone">timezone</a> configuration
762
<acronym class="acronym">UTC</acronym>. They are converted to local time
763
in the zone specified by the <a href="runtime-config-client.html#guc-timezone">timezone</a> configuration
752
764
parameter before being displayed to the client.
754
<p> The <a href="runtime-config.html#guc-timezone">timezone</a> configuration parameter can
755
be set in the file <tt class="filename">postgresql.conf</tt>, or in any of the
756
other standard ways described in <a href="runtime-config.html" title="16.4.�Run-time Configuration">Section�16.4, “Run-time Configuration”</a>.
766
<p> The <a href="runtime-config-client.html#guc-timezone">timezone</a> configuration parameter can
767
be set in the file <code class="filename">postgresql.conf</code>, or in any of the
768
other standard ways described in <a href="runtime-config.html" title="Chapter�17.�Server Configuration">Chapter�17, <i>Server Configuration</i></a>.
757
769
There are also several special ways to set it:
760
772
<div class="itemizedlist"><ul type="disc">
761
<li><p> If <tt class="varname">timezone</tt> is not specified in
762
<tt class="filename">postgresql.conf</tt> nor as a postmaster command-line switch,
763
the server attempts to use the value of the <tt class="envar">TZ</tt>
764
environment variable as the default time zone. If <tt class="envar">TZ</tt>
773
<li><p> If <code class="varname">timezone</code> is not specified in
774
<code class="filename">postgresql.conf</code> nor as a postmaster command-line switch,
775
the server attempts to use the value of the <code class="envar">TZ</code>
776
environment variable as the default time zone. If <code class="envar">TZ</code>
765
777
is not defined or is not any of the time zone names known to
766
778
<span class="productname">PostgreSQL</span>, the server attempts to
767
779
determine the operating system's default time zone by checking the
768
behavior of the C library function <tt class="literal">localtime()</tt>. The
780
behavior of the C library function <code class="literal">localtime()</code>. The
769
781
default time zone is selected as the closest match among
770
782
<span class="productname">PostgreSQL</span>'s known time zones.
772
<li><p> The <span class="acronym">SQL</span> command <tt class="command">SET TIME ZONE</tt>
784
<li><p> The <acronym class="acronym">SQL</acronym> command <code class="command">SET TIME ZONE</code>
773
785
sets the time zone for the session. This is an alternative spelling
774
of <tt class="command">SET TIMEZONE TO</tt> with a more SQL-spec-compatible syntax.
786
of <code class="command">SET TIMEZONE TO</code> with a more SQL-spec-compatible syntax.
776
<li><p> The <tt class="envar">PGTZ</tt> environment variable, if set at the
788
<li><p> The <code class="envar">PGTZ</code> environment variable, if set at the
777
789
client, is used by <span class="application">libpq</span>
778
applications to send a <tt class="command">SET TIME ZONE</tt>
790
applications to send a <code class="command">SET TIME ZONE</code>
779
791
command to the server upon connection.
added
removed
docs/en_US/pg/datatype-datetime.html
