1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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 15.6 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</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>
<span class="productname">PostgreSQL</span> supports the full set of
<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
on these data types are described in
<a class="xref" href="functions-datetime.html" title="9.9. Date/Time Functions and Operators">Section 9.9</a>.
Dates are counted according to the Gregorian calendar, even in
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).
</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>
The SQL standard requires that writing just <code class="type">timestamp</code>
be equivalent to <code class="type">timestamp without time
zone</code>, and <span class="productname">PostgreSQL</span> honors that
behavior. <code class="type">timestamptz</code> is accepted as an
abbreviation for <code class="type">timestamp with time zone</code>; this is a
<span class="productname">PostgreSQL</span> extension.
</p></div><p>
<code class="type">time</code>, <code class="type">timestamp</code>, and
<code class="type">interval</code> accept an optional precision value
<em class="replaceable"><code>p</code></em> which specifies the number of
fractional digits retained in the seconds field. By default, there
is no explicit bound on precision. The allowed range of
<em class="replaceable"><code>p</code></em> is from 0 to 6.
</p><p>
The <code class="type">interval</code> type has an additional option, which is
to restrict the set of stored fields by writing one of these phrases:
</p><pre class="literallayout">
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
</pre><p>
Note that if both <em class="replaceable"><code>fields</code></em> and
<em class="replaceable"><code>p</code></em> are specified, the
<em class="replaceable"><code>fields</code></em> must include <code class="literal">SECOND</code>,
since the precision applies only to the seconds.
</p><p>
The type <code class="type">time with time zone</code> is defined by the SQL
standard, but the definition exhibits properties which lead to
questionable usefulness. In most cases, a combination of
<code class="type">date</code>, <code class="type">time</code>, <code class="type">timestamp without time
zone</code>, and <code class="type">timestamp with time zone</code> should
provide a complete range of date/time functionality required by
any application.
</p><div class="sect2" id="DATATYPE-DATETIME-INPUT"><div class="titlepage"><div><div><h3 class="title">8.5.1. Date/Time Input</h3></div></div></div><p>
Date and time input is accepted in almost any reasonable format, including
ISO 8601, <acronym class="acronym">SQL</acronym>-compatible,
traditional <span class="productname">POSTGRES</span>, and others.
For some formats, ordering of day, month, and year in date input is
ambiguous and there is support for specifying the expected
ordering of these fields. Set the <a class="xref" href="runtime-config-client.html#GUC-DATESTYLE">DateStyle</a> parameter
to <code class="literal">MDY</code> to select month-day-year interpretation,
<code class="literal">DMY</code> to select day-month-year interpretation, or
<code class="literal">YMD</code> to select year-month-day interpretation.
</p><p>
<span class="productname">PostgreSQL</span> is more flexible in
handling date/time input than the
<acronym class="acronym">SQL</acronym> standard requires.
See <a class="xref" href="datetime-appendix.html" title="Appendix B. Date/Time Support">Appendix B</a>
for the exact parsing rules of date/time input and for the
recognized text fields including months, days of the week, and
time zones.
</p><p>
Remember that any date or time literal input needs to be enclosed
in single quotes, like text strings. Refer to
<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
information.
<acronym class="acronym">SQL</acronym> requires the following syntax
</p><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><p>
where <em class="replaceable"><code>p</code></em> is an optional precision
specification giving the number of
fractional digits in the seconds field. Precision can be
specified for <code class="type">time</code>, <code class="type">timestamp</code>, and
<code class="type">interval</code> types, and can range from 0 to 6.
If no precision is specified in a constant specification,
it defaults to the precision of the literal value (but not
more than 6 digits).
</p><div class="sect3" id="id-1.5.7.13.18.5"><div class="titlepage"><div><div><h4 class="title">8.5.1.1. Dates</h4></div></div></div><a id="id-1.5.7.13.18.5.2" class="indexterm"></a><p>
<a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE" title="Table 8.10. Date Input">Table 8.10</a> shows some possible
inputs for the <code class="type">date</code> type.
</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
(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;
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;
rejected in other modes</td></tr><tr><td>01/02/03</td><td>January 2, 2003 in <code class="literal">MDY</code> mode;
February 1, 2003 in <code class="literal">DMY</code> mode;
February 3, 2001 in <code class="literal">YMD</code> mode
</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="id-1.5.7.13.18.6"><div class="titlepage"><div><div><h4 class="title">8.5.1.2. Times</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>
The time-of-day types are <code class="type">time [
(<em class="replaceable"><code>p</code></em>) ] without time zone</code> and
<code class="type">time [ (<em class="replaceable"><code>p</code></em>) ] with time
zone</code>. <code class="type">time</code> alone is equivalent to
<code class="type">time without time zone</code>.
</p><p>
Valid input for these types consists of a time of day followed
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>
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
specified in the input for <code class="type">time without time zone</code>,
it is silently ignored. You can also specify a date but it will
be ignored, except when you use a time zone name that involves a
daylight-savings rule, such as
<code class="literal">America/New_York</code>. In this case specifying the date
is required in order to determine whether standard or daylight-savings
time applies. The appropriate time zone offset is recorded in the
<code class="type">time with time zone</code> value and is output as stored;
it is not adjusted to the active time zone.
</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>
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
to specify time zones.
</p></div><div class="sect3" id="id-1.5.7.13.18.7"><div class="titlepage"><div><div><h4 class="title">8.5.1.3. Time Stamps</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>
Valid input for the time stamp types consists of the concatenation
of a date and a time, followed by an optional time zone,
followed by an optional <code class="literal">AD</code> or <code class="literal">BC</code>.
(Alternatively, <code class="literal">AD</code>/<code class="literal">BC</code> can appear
before the time zone, but this is not the preferred ordering.)
Thus:
</p><pre class="programlisting">
1999-01-08 04:05:06
</pre><p>
and:
</p><pre class="programlisting">
1999-01-08 04:05:06 -8:00
</pre><p>
are valid values, which follow the <acronym class="acronym">ISO</acronym> 8601
standard. In addition, the common format:
</p><pre class="programlisting">
January 8 04:05:06 1999 PST
</pre><p>
is supported.
</p><p>
The <acronym class="acronym">SQL</acronym> standard differentiates
<code class="type">timestamp without time zone</code>
and <code class="type">timestamp with time zone</code> literals by the presence of a
<span class="quote">“<span class="quote">+</span>”</span> or <span class="quote">“<span class="quote">-</span>”</span> symbol and time zone offset after
the time. Hence, according to the standard,
</p><pre class="programlisting">
TIMESTAMP '2004-10-19 10:23:54'
</pre><p>
is a <code class="type">timestamp without time zone</code>, while
</p><pre class="programlisting">
TIMESTAMP '2004-10-19 10:23:54+02'
</pre><p>
is a <code class="type">timestamp with time zone</code>.
<span class="productname">PostgreSQL</span> never examines the content of a
literal string before determining its type, and therefore will treat
both of the above as <code class="type">timestamp without time zone</code>. To
ensure that a literal is treated as <code class="type">timestamp with time
zone</code>, give it the correct explicit type:
</p><pre class="programlisting">
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
</pre><p>
In a literal that has been determined to be <code class="type">timestamp without time
zone</code>, <span class="productname">PostgreSQL</span> will silently ignore
any time zone indication.
That is, the resulting value is derived from the date/time
fields in the input value, and is not adjusted for time zone.
</p><p>
For <code class="type">timestamp with time zone</code>, the internally stored
value is always in UTC (Universal
Coordinated Time, traditionally known as Greenwich Mean Time,
<acronym class="acronym">GMT</acronym>). An input value that has an explicit
time zone specified is converted to UTC using the appropriate offset
for that time zone. If no time zone is stated in the input string,
then it is assumed to be in the time zone indicated by the system's
<a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> parameter, and is converted to UTC using the
offset for the <code class="varname">timezone</code> zone.
</p><p>
When a <code class="type">timestamp with time
zone</code> value is output, it is always converted from UTC to the
current <code class="varname">timezone</code> zone, and displayed as local time in that
zone. To see the time in another time zone, either change
<code class="varname">timezone</code> or use the <code class="literal">AT TIME ZONE</code> construct
(see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT" title="9.9.4. AT TIME ZONE">Section 9.9.4</a>).
</p><p>
Conversions between <code class="type">timestamp without time zone</code> and
<code class="type">timestamp with time zone</code> normally assume that the
<code class="type">timestamp without time zone</code> value should be taken or given
as <code class="varname">timezone</code> local time. A different time zone can
be specified for the conversion using <code class="literal">AT TIME ZONE</code>.
</p></div><div class="sect3" id="DATATYPE-DATETIME-SPECIAL-VALUES"><div class="titlepage"><div><div><h4 class="title">8.5.1.4. Special Values</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>
<span class="productname">PostgreSQL</span> supports several
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
<code class="literal">infinity</code> and <code class="literal">-infinity</code>
are specially represented inside the system and will be displayed
unchanged; but the others are simply notational shorthands
that will be converted to ordinary date/time values when read.
(In particular, <code class="literal">now</code> and related strings are converted
to a specific time value as soon as they are read.)
All of these values need to be enclosed in single quotes when used
as constants in SQL commands.
</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></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></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>
The following <acronym class="acronym">SQL</acronym>-compatible functions can also
be used to obtain the current time value for the corresponding data
type:
<code class="literal">CURRENT_DATE</code>, <code class="literal">CURRENT_TIME</code>,
<code class="literal">CURRENT_TIMESTAMP</code>, <code class="literal">LOCALTIME</code>,
<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
SQL functions and are <span class="emphasis"><em>not</em></span> recognized in data input strings.
</p><div class="caution"><h3 class="title">Caution</h3><p>
While the input strings <code class="literal">now</code>,
<code class="literal">today</code>, <code class="literal">tomorrow</code>,
and <code class="literal">yesterday</code> are fine to use in interactive SQL
commands, they can have surprising behavior when the command is
saved to be executed later, for example in prepared statements,
views, and function definitions. The string can be converted to a
specific time value that continues to be used long after it becomes
stale. Use one of the SQL functions instead in such contexts.
For example, <code class="literal">CURRENT_DATE + 1</code> is safer than
<code class="literal">'tomorrow'::date</code>.
</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</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>
The output format of the date/time types can be set to one of the four
styles ISO 8601,
<acronym class="acronym">SQL</acronym> (Ingres), traditional <span class="productname">POSTGRES</span>
(Unix <span class="application">date</span> format), or
German. The default
is the <acronym class="acronym">ISO</acronym> format. (The
<acronym class="acronym">SQL</acronym> standard requires the use of the ISO 8601
format. The name of the <span class="quote">“<span class="quote">SQL</span>”</span> output format is a
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
output style. The output of the <code class="type">date</code> and
<code class="type">time</code> types is generally only the date or time part
in accordance with the given examples. However, the
<span class="productname">POSTGRES</span> style outputs date-only values in
<acronym class="acronym">ISO</acronym> format.
</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>
ISO 8601 specifies the use of uppercase letter <code class="literal">T</code> to separate
the date and time. <span class="productname">PostgreSQL</span> accepts that format on
input, but on output it uses a space rather than <code class="literal">T</code>, as shown
above. This is for readability and for consistency with
<a class="ulink" href="https://tools.ietf.org/html/rfc3339" target="_top">RFC 3339</a> as
well as some other database systems.
</p></div><p>
In the <acronym class="acronym">SQL</acronym> and POSTGRES styles, day appears before
month if DMY field ordering has been specified, otherwise month appears
before day.
(See <a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-INPUT" title="8.5.1. Date/Time Input">Section 8.5.1</a>
for how this setting also affects interpretation of input values.)
<a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-OUTPUT2-TABLE" title="Table 8.15. Date Order Conventions">Table 8.15</a> shows examples.
</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>
In the <acronym class="acronym">ISO</acronym> style, the time zone is always shown as
a signed numeric offset from UTC, with positive sign used for zones
east of Greenwich. The offset will be shown
as <em class="replaceable"><code>hh</code></em> (hours only) if it is an integral
number of hours, else
as <em class="replaceable"><code>hh</code></em>:<em class="replaceable"><code>mm</code></em> if it
is an integral number of minutes, else as
<em class="replaceable"><code>hh</code></em>:<em class="replaceable"><code>mm</code></em>:<em class="replaceable"><code>ss</code></em>.
(The third case is not possible with any modern time zone standard,
but it can appear when working with timestamps that predate the
adoption of standardized time zones.)
In the other date styles, the time zone is shown as an alphabetic
abbreviation if one is in common use in the current zone. Otherwise
it appears as a signed numeric offset in ISO 8601 basic format
(<em class="replaceable"><code>hh</code></em> or <em class="replaceable"><code>hhmm</code></em>).
</p><p>
The date/time style can be selected by the user using the
<code class="command">SET datestyle</code> command, the <a class="xref" href="runtime-config-client.html#GUC-DATESTYLE">DateStyle</a> parameter in the
<code class="filename">postgresql.conf</code> configuration file, or the
<code class="envar">PGDATESTYLE</code> environment variable on the server or
client.
</p><p>
The formatting function <code class="function">to_char</code>
(see <a class="xref" href="functions-formatting.html" title="9.8. Data Type Formatting Functions">Section 9.8</a>) is also available as
a more flexible way to format date/time output.
</p></div><div class="sect2" id="DATATYPE-TIMEZONES"><div class="titlepage"><div><div><h3 class="title">8.5.3. Time Zones</h3></div></div></div><a id="id-1.5.7.13.20.2" class="indexterm"></a><p>
Time zones, and time-zone conventions, are influenced by
political decisions, not just earth geometry. Time zones around the
world became somewhat standardized during the 1900s,
but continue to be prone to arbitrary changes, particularly with
respect to daylight-savings rules.
<span class="productname">PostgreSQL</span> uses the widely-used
IANA (Olson) time zone database for information about
historical time zone rules. For times in the future, the assumption
is that the latest known rules for a given time zone will
continue to be observed indefinitely far into the future.
</p><p>
<span class="productname">PostgreSQL</span> endeavors to be compatible with
the <acronym class="acronym">SQL</acronym> standard definitions for typical usage.
However, the <acronym class="acronym">SQL</acronym> standard has an odd mix of date and
time types and capabilities. Two obvious problems are:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
Although the <code class="type">date</code> type
cannot have an associated time zone, the
<code class="type">time</code> type can.
Time zones in the real world have little meaning unless
associated with a date as well as a time,
since the offset can vary through the year with daylight-saving
time boundaries.
</p></li><li class="listitem"><p>
The default time zone is specified as a constant numeric offset
from <acronym class="acronym">UTC</acronym>. It is therefore impossible to adapt to
daylight-saving time when doing date/time arithmetic across
<acronym class="acronym">DST</acronym> boundaries.
</p></li></ul></div><p>
</p><p>
To address these difficulties, we recommend using date/time types
that contain both date and time when using time zones. We
do <span class="emphasis"><em>not</em></span> recommend using the type <code class="type">time with
time zone</code> (though it is supported by
<span class="productname">PostgreSQL</span> for legacy applications and
for compliance with the <acronym class="acronym">SQL</acronym> standard).
<span class="productname">PostgreSQL</span> assumes
your local time zone for any type containing only date or time.
</p><p>
All timezone-aware dates and times are stored internally in
<acronym class="acronym">UTC</acronym>. They are converted to local time
in the zone specified by the <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> configuration
parameter before being displayed to the client.
</p><p>
<span class="productname">PostgreSQL</span> allows you to specify time zones in
three different forms:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
A full time zone name, for example <code class="literal">America/New_York</code>.
The recognized time zone names are listed in the
<code class="literal">pg_timezone_names</code> view (see <a class="xref" href="view-pg-timezone-names.html" title="54.32. pg_timezone_names">Section 54.32</a>).
<span class="productname">PostgreSQL</span> uses the widely-used IANA
time zone data for this purpose, so the same time zone
names are also recognized by other software.
</p></li><li class="listitem"><p>
A time zone abbreviation, for example <code class="literal">PST</code>. Such a
specification merely defines a particular offset from UTC, in
contrast to full time zone names which can imply a set of daylight
savings transition rules as well. The recognized abbreviations
are listed in the <code class="literal">pg_timezone_abbrevs</code> view (see <a class="xref" href="view-pg-timezone-abbrevs.html" title="54.31. pg_timezone_abbrevs">Section 54.31</a>). You cannot set the
configuration parameters <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> or
<a class="xref" href="runtime-config-logging.html#GUC-LOG-TIMEZONE">log_timezone</a> to a time
zone abbreviation, but you can use abbreviations in
date/time input values and with the <code class="literal">AT TIME ZONE</code>
operator.
</p></li><li class="listitem"><p>
In addition to the timezone names and abbreviations,
<span class="productname">PostgreSQL</span> will accept POSIX-style time zone
specifications, as described in
<a class="xref" href="datetime-posix-timezone-specs.html" title="B.5. POSIX Time Zone Specifications">Section B.5</a>. This option is not
normally preferable to using a named time zone, but it may be
necessary if no suitable IANA time zone entry is available.
</p></li></ul></div><p>
In short, this is the difference between abbreviations
and full names: abbreviations represent a specific offset from UTC,
whereas many of the full names imply a local daylight-savings time
rule, and so have two possible UTC offsets. As an example,
<code class="literal">2014-06-04 12:00 America/New_York</code> represents noon local
time in New York, which for this particular date was Eastern Daylight
Time (UTC-4). So <code class="literal">2014-06-04 12:00 EDT</code> specifies that
same time instant. But <code class="literal">2014-06-04 12:00 EST</code> specifies
noon Eastern Standard Time (UTC-5), regardless of whether daylight
savings was nominally in effect on that date.
</p><p>
To complicate matters, some jurisdictions have used the same timezone
abbreviation to mean different UTC offsets at different times; for
example, in Moscow <code class="literal">MSK</code> has meant UTC+3 in some years and
UTC+4 in others. <span class="application">PostgreSQL</span> interprets such
abbreviations according to whatever they meant (or had most recently
meant) on the specified date; but, as with the <code class="literal">EST</code> example
above, this is not necessarily the same as local civil time on that date.
</p><p>
In all cases, timezone names and abbreviations are recognized
case-insensitively. (This is a change from <span class="productname">PostgreSQL</span>
versions prior to 8.2, which were case-sensitive in some contexts but
not others.)
</p><p>
Neither timezone names nor abbreviations are hard-wired into the server;
they are obtained from configuration files stored under
<code class="filename">.../share/timezone/</code> and <code class="filename">.../share/timezonesets/</code>
of the installation directory
(see <a class="xref" href="datetime-config-files.html" title="B.4. Date/Time Configuration Files">Section B.4</a>).
</p><p>
The <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> configuration parameter can
be set in the file <code class="filename">postgresql.conf</code>, or in any of the
other standard ways described in <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a>.
There are also some special ways to set it:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
The <acronym class="acronym">SQL</acronym> command <code class="command">SET TIME ZONE</code>
sets the time zone for the session. This is an alternative spelling
of <code class="command">SET TIMEZONE TO</code> with a more SQL-spec-compatible syntax.
</p></li><li class="listitem"><p>
The <code class="envar">PGTZ</code> environment variable is used by
<span class="application">libpq</span> clients
to send a <code class="command">SET TIME ZONE</code>
command to the server upon connection.
</p></li></ul></div><p>
</p></div><div class="sect2" id="DATATYPE-INTERVAL-INPUT"><div class="titlepage"><div><div><h3 class="title">8.5.4. Interval Input</h3></div></div></div><a id="id-1.5.7.13.21.2" class="indexterm"></a><p>
<code class="type">interval</code> values can be written using the following
verbose syntax:
</p><pre class="synopsis">
[<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><p>
where <em class="replaceable"><code>quantity</code></em> is a number (possibly signed);
<em class="replaceable"><code>unit</code></em> is <code class="literal">microsecond</code>,
<code class="literal">millisecond</code>, <code class="literal">second</code>,
<code class="literal">minute</code>, <code class="literal">hour</code>, <code class="literal">day</code>,
<code class="literal">week</code>, <code class="literal">month</code>, <code class="literal">year</code>,
<code class="literal">decade</code>, <code class="literal">century</code>, <code class="literal">millennium</code>,
or abbreviations or plurals of these units;
<em class="replaceable"><code>direction</code></em> can be <code class="literal">ago</code> or
empty. The at sign (<code class="literal">@</code>) is optional noise. The amounts
of the different units are implicitly added with appropriate
sign accounting. <code class="literal">ago</code> negates all the fields.
This syntax is also used for interval output, if
<a class="xref" href="runtime-config-client.html#GUC-INTERVALSTYLE">IntervalStyle</a> is set to
<code class="literal">postgres_verbose</code>.
</p><p>
Quantities of days, hours, minutes, and seconds can be specified without
explicit unit markings. For example, <code class="literal">'1 12:59:10'</code> is read
the same as <code class="literal">'1 day 12 hours 59 min 10 sec'</code>. Also,
a combination of years and months can be specified with a dash;
for example <code class="literal">'200-10'</code> is read the same as <code class="literal">'200 years
10 months'</code>. (These shorter forms are in fact the only ones allowed
by the <acronym class="acronym">SQL</acronym> standard, and are used for output when
<code class="varname">IntervalStyle</code> is set to <code class="literal">sql_standard</code>.)
</p><p>
Interval values can also be written as ISO 8601 time intervals, using
either the <span class="quote">“<span class="quote">format with designators</span>”</span> of the standard's section
4.4.3.2 or the <span class="quote">“<span class="quote">alternative format</span>”</span> of section 4.4.3.3. The
format with designators looks like this:
</p><pre class="synopsis">
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>]
</pre><p>
The string must start with a <code class="literal">P</code>, and may include a
<code class="literal">T</code> that introduces the time-of-day units. The
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
omitted, and may be specified in any order, but units smaller than
a day must appear after <code class="literal">T</code>. In particular, the meaning of
<code class="literal">M</code> depends on whether it is before or after
<code class="literal">T</code>.
</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>
In the alternative format:
</p><pre class="synopsis">
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>]
</pre><p>
the string must begin with <code class="literal">P</code>, and a
<code class="literal">T</code> separates the date and time parts of the interval.
The values are given as numbers similar to ISO 8601 dates.
</p><p>
When writing an interval constant with a <em class="replaceable"><code>fields</code></em>
specification, or when assigning a string to an interval column that was
defined with a <em class="replaceable"><code>fields</code></em> specification, the interpretation of
unmarked quantities depends on the <em class="replaceable"><code>fields</code></em>. For
example <code class="literal">INTERVAL '1' YEAR</code> is read as 1 year, whereas
<code class="literal">INTERVAL '1'</code> means 1 second. Also, field values
<span class="quote">“<span class="quote">to the right</span>”</span> of the least significant field allowed by the
<em class="replaceable"><code>fields</code></em> specification are silently discarded. For
example, writing <code class="literal">INTERVAL '1 day 2:03:04' HOUR TO MINUTE</code>
results in dropping the seconds field, but not the day field.
</p><p>
According to the <acronym class="acronym">SQL</acronym> standard all fields of an interval
value must have the same sign, so a leading negative sign applies to all
fields; for example the negative sign in the interval literal
<code class="literal">'-1 2:03:04'</code> applies to both the days and hour/minute/second
parts. <span class="productname">PostgreSQL</span> allows the fields to have different
signs, and traditionally treats each field in the textual representation
as independently signed, so that the hour/minute/second part is
considered positive in this example. If <code class="varname">IntervalStyle</code> is
set to <code class="literal">sql_standard</code> then a leading sign is considered
to apply to all fields (but only if no additional signs appear).
Otherwise the traditional <span class="productname">PostgreSQL</span> interpretation is
used. To avoid ambiguity, it's recommended to attach an explicit sign
to each field if any field is negative.
</p><p>
Field values can have fractional parts: for example, <code class="literal">'1.5
weeks'</code> or <code class="literal">'01:02:03.45'</code>. However,
because interval internally stores only three integer units (months,
days, microseconds), fractional units must be spilled to smaller
units. Fractional parts of units greater than months are rounded to
be an integer number of months, e.g. <code class="literal">'1.5 years'</code>
becomes <code class="literal">'1 year 6 mons'</code>. Fractional parts of
weeks and days are computed to be an integer number of days and
microseconds, assuming 30 days per month and 24 hours per day, e.g.,
<code class="literal">'1.75 months'</code> becomes <code class="literal">1 mon 22 days
12:00:00</code>. Only seconds will ever be shown as fractional
on output.
</p><p>
<a class="xref" href="datatype-datetime.html#DATATYPE-INTERVAL-INPUT-EXAMPLES" title="Table 8.17. Interval Input">Table 8.17</a> shows some examples
of valid <code class="type">interval</code> input.
</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" /><p>
Internally <code class="type">interval</code> values are stored as months, days,
and microseconds. This is done because the number of days in a month
varies, and a day can have 23 or 25 hours if a daylight savings
time adjustment is involved. The months and days fields are integers
while the microseconds field can store fractional seconds. Because intervals are
usually created from constant strings or <code class="type">timestamp</code> subtraction,
this storage method works well in most cases, but can cause unexpected
results:
</p><pre class="programlisting">
SELECT EXTRACT(hours from '80 minutes'::interval);
date_part
-----------
1
SELECT EXTRACT(days from '80 hours'::interval);
date_part
-----------
0
</pre><p>
Functions <code class="function">justify_days</code> and
<code class="function">justify_hours</code> are available for adjusting days
and hours that overflow their normal ranges.
</p></div><div class="sect2" id="DATATYPE-INTERVAL-OUTPUT"><div class="titlepage"><div><div><h3 class="title">8.5.5. Interval Output</h3></div></div></div><a id="id-1.5.7.13.22.2" class="indexterm"></a><p>
The output format of the interval type can be set to one of the
four styles <code class="literal">sql_standard</code>, <code class="literal">postgres</code>,
<code class="literal">postgres_verbose</code>, or <code class="literal">iso_8601</code>,
using the command <code class="literal">SET intervalstyle</code>.
The default is the <code class="literal">postgres</code> format.
<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
output style.
</p><p>
The <code class="literal">sql_standard</code> style produces output that conforms to
the SQL standard's specification for interval literal strings, if
the interval value meets the standard's restrictions (either year-month
only or day-time only, with no mixing of positive
and negative components). Otherwise the output looks like a standard
year-month literal string followed by a day-time literal string,
with explicit signs added to disambiguate mixed-sign intervals.
</p><p>
The output of the <code class="literal">postgres</code> style matches the output of
<span class="productname">PostgreSQL</span> releases prior to 8.4 when the
<a class="xref" href="runtime-config-client.html#GUC-DATESTYLE">DateStyle</a> parameter was set to <code class="literal">ISO</code>.
</p><p>
The output of the <code class="literal">postgres_verbose</code> style matches the output of
<span class="productname">PostgreSQL</span> releases prior to 8.4 when the
<code class="varname">DateStyle</code> parameter was set to non-<code class="literal">ISO</code> output.
</p><p>
The output of the <code class="literal">iso_8601</code> style matches the <span class="quote">“<span class="quote">format
with designators</span>”</span> described in section 4.4.3.2 of the
ISO 8601 standard.
</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 15.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 8.6. Boolean Type</td></tr></table></div></body></html>
|
