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
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>9.9.Date/Time Functions and Operators</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="functions.html" title="Chapter9.Functions and Operators">
<link rel="prev" href="functions-formatting.html" title="9.8.Data Type Formatting Functions">
<link rel="next" href="functions-geometry.html" title="9.10.Geometric Functions and Operators">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="functions-datetime"></a>9.9.Date/Time Functions and Operators</h2></div></div></div>
<p> <a href="functions-datetime.html#functions-datetime-table" title="Table9.26.Date/Time Functions">Table9.26, “Date/Time Functions”</a> shows the available
functions for date/time value processing, with details appearing in
the following subsections. <a href="functions-datetime.html#operators-datetime-table" title="Table9.25.Date/Time Operators">Table9.25, “Date/Time Operators”</a> illustrates the behaviors of
the basic arithmetic operators (<code class="literal">+</code>,
<code class="literal">*</code>, etc.). For formatting functions, refer to
<a href="functions-formatting.html" title="9.8.Data Type Formatting Functions">Section9.8, “Data Type Formatting Functions”</a>. You should be familiar with
the background information on date/time data types from <a href="datatype-datetime.html" title="8.5.Date/Time Types">Section8.5, “Date/Time Types”</a>.
</p>
<p> All the functions and operators described below that take <code class="type">time</code> or <code class="type">timestamp</code>
inputs actually come in two variants: one that takes <code class="type">time with time zone</code> or <code class="type">timestamp
with time zone</code>, and one that takes <code class="type">time without time zone</code> or <code class="type">timestamp without time zone</code>.
For brevity, these variants are not shown separately. Also, the
<code class="literal">+</code> and <code class="literal">*</code> operators come in commutative pairs (for
example both date + integer and integer + date); we show only one of each
such pair.
</p>
<div class="table">
<a name="operators-datetime-table"></a><p class="title"><b>Table9.25.Date/Time Operators</b></p>
<div class="table-contents"><table summary="Date/Time Operators" border="1">
<colgroup>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Operator</th>
<th>Example</th>
<th>Result</th>
</tr></thead>
<tbody>
<tr>
<td> <code class="literal">+</code> </td>
<td><code class="literal">date '2001-09-28' + integer '7'</code></td>
<td><code class="literal">date '2001-10-05'</code></td>
</tr>
<tr>
<td> <code class="literal">+</code> </td>
<td><code class="literal">date '2001-09-28' + interval '1 hour'</code></td>
<td><code class="literal">timestamp '2001-09-28 01:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">+</code> </td>
<td><code class="literal">date '2001-09-28' + time '03:00'</code></td>
<td><code class="literal">timestamp '2001-09-28 03:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">+</code> </td>
<td><code class="literal">interval '1 day' + interval '1 hour'</code></td>
<td><code class="literal">interval '1 day 01:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">+</code> </td>
<td><code class="literal">timestamp '2001-09-28 01:00' + interval '23 hours'</code></td>
<td><code class="literal">timestamp '2001-09-29 00:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">+</code> </td>
<td><code class="literal">time '01:00' + interval '3 hours'</code></td>
<td><code class="literal">time '04:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">-</code> </td>
<td><code class="literal">- interval '23 hours'</code></td>
<td><code class="literal">interval '-23:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">-</code> </td>
<td><code class="literal">date '2001-10-01' - date '2001-09-28'</code></td>
<td><code class="literal">integer '3'</code></td>
</tr>
<tr>
<td> <code class="literal">-</code> </td>
<td><code class="literal">date '2001-10-01' - integer '7'</code></td>
<td><code class="literal">date '2001-09-24'</code></td>
</tr>
<tr>
<td> <code class="literal">-</code> </td>
<td><code class="literal">date '2001-09-28' - interval '1 hour'</code></td>
<td><code class="literal">timestamp '2001-09-27 23:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">-</code> </td>
<td><code class="literal">time '05:00' - time '03:00'</code></td>
<td><code class="literal">interval '02:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">-</code> </td>
<td><code class="literal">time '05:00' - interval '2 hours'</code></td>
<td><code class="literal">time '03:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">-</code> </td>
<td><code class="literal">timestamp '2001-09-28 23:00' - interval '23 hours'</code></td>
<td><code class="literal">timestamp '2001-09-28 00:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">-</code> </td>
<td><code class="literal">interval '1 day' - interval '1 hour'</code></td>
<td><code class="literal">interval '1 day -01:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">-</code> </td>
<td><code class="literal">timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'</code></td>
<td><code class="literal">interval '1 day 15:00:00'</code></td>
</tr>
<tr>
<td> <code class="literal">*</code> </td>
<td><code class="literal">900 * interval '1 second'</code></td>
<td><code class="literal">interval '00:15:00'</code></td>
</tr>
<tr>
<td> <code class="literal">*</code> </td>
<td><code class="literal">21 * interval '1 day'</code></td>
<td><code class="literal">interval '21 days'</code></td>
</tr>
<tr>
<td> <code class="literal">*</code> </td>
<td><code class="literal">double precision '3.5' * interval '1 hour'</code></td>
<td><code class="literal">interval '03:30:00'</code></td>
</tr>
<tr>
<td> <code class="literal">/</code> </td>
<td><code class="literal">interval '1 hour' / double precision '1.5'</code></td>
<td><code class="literal">interval '00:40:00'</code></td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break"><a name="id611336"></a><a name="id611342"></a><a name="id611349"></a><a name="id611355"></a><a name="id611362"></a><a name="id611368"></a><a name="id611374"></a><a name="id611381"></a><a name="id611387"></a><a name="id611394"></a><a name="id611400"></a><a name="id611406"></a><a name="id611413"></a><a name="id611419"></a><div class="table">
<a name="functions-datetime-table"></a><p class="title"><b>Table9.26.Date/Time Functions</b></p>
<div class="table-contents"><table summary="Date/Time Functions" border="1">
<colgroup>
<col>
<col>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Function</th>
<th>Return Type</th>
<th>Description</th>
<th>Example</th>
<th>Result</th>
</tr></thead>
<tbody>
<tr>
<td><code class="literal"><code class="function">age</code>(<code class="type">timestamp</code>, <code class="type">timestamp</code>)</code></td>
<td><code class="type">interval</code></td>
<td>Subtract arguments, producing a “<span class="quote">symbolic</span>” result that
uses years and months</td>
<td><code class="literal">age(timestamp '2001-04-10', timestamp '1957-06-13')</code></td>
<td><code class="literal">43 years 9 mons 27 days</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">age</code>(<code class="type">timestamp</code>)</code></td>
<td><code class="type">interval</code></td>
<td>Subtract from <code class="function">current_date</code>
</td>
<td><code class="literal">age(timestamp '1957-06-13')</code></td>
<td><code class="literal">43 years 8 mons 3 days</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">current_date</code></code></td>
<td><code class="type">date</code></td>
<td>Today's date; see <a href="functions-datetime.html#functions-datetime-current" title="9.9.4.Current Date/Time">Section9.9.4, “Current Date/Time”</a>
</td>
<td></td>
<td></td>
</tr>
<tr>
<td><code class="literal"><code class="function">current_time</code></code></td>
<td><code class="type">time with time zone</code></td>
<td>Time of day; see <a href="functions-datetime.html#functions-datetime-current" title="9.9.4.Current Date/Time">Section9.9.4, “Current Date/Time”</a>
</td>
<td></td>
<td></td>
</tr>
<tr>
<td><code class="literal"><code class="function">current_timestamp</code></code></td>
<td><code class="type">timestamp with time zone</code></td>
<td>Date and time; see <a href="functions-datetime.html#functions-datetime-current" title="9.9.4.Current Date/Time">Section9.9.4, “Current Date/Time”</a>
</td>
<td></td>
<td></td>
</tr>
<tr>
<td><code class="literal"><code class="function">date_part</code>(<code class="type">text</code>, <code class="type">timestamp</code>)</code></td>
<td><code class="type">double precision</code></td>
<td>Get subfield (equivalent to
<code class="function">extract</code>); see <a href="functions-datetime.html#functions-datetime-extract" title="9.9.1.EXTRACT, date_part">Section9.9.1, “<code class="function">EXTRACT</code>, <code class="function">date_part</code>”</a>
</td>
<td><code class="literal">date_part('hour', timestamp '2001-02-16 20:38:40')</code></td>
<td><code class="literal">20</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">date_part</code>(<code class="type">text</code>, <code class="type">interval</code>)</code></td>
<td><code class="type">double precision</code></td>
<td>Get subfield (equivalent to
<code class="function">extract</code>); see <a href="functions-datetime.html#functions-datetime-extract" title="9.9.1.EXTRACT, date_part">Section9.9.1, “<code class="function">EXTRACT</code>, <code class="function">date_part</code>”</a>
</td>
<td><code class="literal">date_part('month', interval '2 years 3 months')</code></td>
<td><code class="literal">3</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">date_trunc</code>(<code class="type">text</code>, <code class="type">timestamp</code>)</code></td>
<td><code class="type">timestamp</code></td>
<td>Truncate to specified precision; see also <a href="functions-datetime.html#functions-datetime-trunc" title="9.9.2.date_trunc">Section9.9.2, “<code class="function">date_trunc</code>”</a>
</td>
<td><code class="literal">date_trunc('hour', timestamp '2001-02-16 20:38:40')</code></td>
<td><code class="literal">2001-02-16 20:00:00</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">extract</code>(<em class="parameter"><code>field</code></em> from
<code class="type">timestamp</code>)</code></td>
<td><code class="type">double precision</code></td>
<td>Get subfield; see <a href="functions-datetime.html#functions-datetime-extract" title="9.9.1.EXTRACT, date_part">Section9.9.1, “<code class="function">EXTRACT</code>, <code class="function">date_part</code>”</a>
</td>
<td><code class="literal">extract(hour from timestamp '2001-02-16 20:38:40')</code></td>
<td><code class="literal">20</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">extract</code>(<em class="parameter"><code>field</code></em> from
<code class="type">interval</code>)</code></td>
<td><code class="type">double precision</code></td>
<td>Get subfield; see <a href="functions-datetime.html#functions-datetime-extract" title="9.9.1.EXTRACT, date_part">Section9.9.1, “<code class="function">EXTRACT</code>, <code class="function">date_part</code>”</a>
</td>
<td><code class="literal">extract(month from interval '2 years 3 months')</code></td>
<td><code class="literal">3</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">isfinite</code>(<code class="type">timestamp</code>)</code></td>
<td><code class="type">boolean</code></td>
<td>Test for finite time stamp (not equal to infinity)</td>
<td><code class="literal">isfinite(timestamp '2001-02-16 21:28:30')</code></td>
<td><code class="literal">true</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">isfinite</code>(<code class="type">interval</code>)</code></td>
<td><code class="type">boolean</code></td>
<td>Test for finite interval</td>
<td><code class="literal">isfinite(interval '4 hours')</code></td>
<td><code class="literal">true</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">justify_hours</code>(<code class="type">interval</code>)</code></td>
<td><code class="type">interval</code></td>
<td>Adjust interval so 24-hour time periods are represented as days</td>
<td><code class="literal">justify_hours(interval '24 hours')</code></td>
<td><code class="literal">1 day</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">justify_days</code>(<code class="type">interval</code>)</code></td>
<td><code class="type">interval</code></td>
<td>Adjust interval so 30-day time periods are represented as months</td>
<td><code class="literal">justify_days(interval '30 days')</code></td>
<td><code class="literal">1 month</code></td>
</tr>
<tr>
<td><code class="literal"><code class="function">localtime</code></code></td>
<td><code class="type">time</code></td>
<td>Time of day; see <a href="functions-datetime.html#functions-datetime-current" title="9.9.4.Current Date/Time">Section9.9.4, “Current Date/Time”</a>
</td>
<td></td>
<td></td>
</tr>
<tr>
<td><code class="literal"><code class="function">localtimestamp</code></code></td>
<td><code class="type">timestamp</code></td>
<td>Date and time; see <a href="functions-datetime.html#functions-datetime-current" title="9.9.4.Current Date/Time">Section9.9.4, “Current Date/Time”</a>
</td>
<td></td>
<td></td>
</tr>
<tr>
<td><code class="literal"><code class="function">now</code>()</code></td>
<td><code class="type">timestamp with time zone</code></td>
<td>Current date and time (equivalent to
<code class="function">current_timestamp</code>); see <a href="functions-datetime.html#functions-datetime-current" title="9.9.4.Current Date/Time">Section9.9.4, “Current Date/Time”</a>
</td>
<td></td>
<td></td>
</tr>
<tr>
<td><code class="literal"><code class="function">timeofday</code>()</code></td>
<td><code class="type">text</code></td>
<td>Current date and time; see <a href="functions-datetime.html#functions-datetime-current" title="9.9.4.Current Date/Time">Section9.9.4, “Current Date/Time”</a>
</td>
<td></td>
<td></td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break"><p> If you are using both <code class="function">justify_hours</code> and
<code class="function">justify_days</code>, it is best to use <code class="function">justify_hours</code>
first so any additional days will be included in the
<code class="function">justify_days</code> calculation.
</p>
<p> In addition to these functions, the SQL <code class="literal">OVERLAPS</code> operator is
supported:
</p>
<pre class="synopsis">(<em class="replaceable"><code>start1</code></em>, <em class="replaceable"><code>end1</code></em>) OVERLAPS (<em class="replaceable"><code>start2</code></em>, <em class="replaceable"><code>end2</code></em>)
(<em class="replaceable"><code>start1</code></em>, <em class="replaceable"><code>length1</code></em>) OVERLAPS (<em class="replaceable"><code>start2</code></em>, <em class="replaceable"><code>length2</code></em>)</pre>
<p>
This expression yields true when two time periods (defined by their
endpoints) overlap, false when they do not overlap. The endpoints
can be specified as pairs of dates, times, or time stamps; or as
a date, time, or time stamp followed by an interval.
</p>
<pre class="screen">SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">true</code>
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">false</code></pre>
<p> When adding an <code class="type">interval</code> value to (or subtracting an
<code class="type">interval</code> value from) a <code class="type">timestamp with time zone</code>
value, the days component advances (or decrements) the date of the
<code class="type">timestamp with time zone</code> by the indicated number of days.
Across daylight saving time changes (with the session time zone set to a
time zone that recognizes DST), this means <code class="literal">interval '1 day'</code>
does not necessarily equal <code class="literal">interval '24 hours'</code>.
For example, with the session time zone set to <code class="literal">CST7CDT</code>,
<code class="literal">timestamp with time zone '2005-04-02 12:00-07' + interval '1 day' </code>
will produce <code class="literal">timestamp with time zone '2005-04-03 12:00-06'</code>,
while adding <code class="literal">interval '24 hours'</code> to the same initial
<code class="type">timestamp with time zone</code> produces
<code class="literal">timestamp with time zone '2005-04-03 13:00-06'</code>, as there is
a change in daylight saving time at <code class="literal">2005-04-03 02:00</code> in time zone
<code class="literal">CST7CDT</code>.
</p>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="functions-datetime-extract"></a>9.9.1.<code class="function">EXTRACT</code>, <code class="function">date_part</code></h3></div></div></div>
<a name="id612373"></a><a name="id612380"></a><pre class="synopsis">EXTRACT(<em class="replaceable"><code>field</code></em> FROM <em class="replaceable"><code>source</code></em>)</pre>
<p> The <code class="function">extract</code> function retrieves subfields
such as year or hour from date/time values.
<em class="replaceable"><code>source</code></em> must be a value expression of
type <code class="type">timestamp</code>, <code class="type">time</code>, or <code class="type">interval</code>.
(Expressions of type <code class="type">date</code> will
be cast to <code class="type">timestamp</code> and can therefore be used as
well.) <em class="replaceable"><code>field</code></em> is an identifier or
string that selects what field to extract from the source value.
The <code class="function">extract</code> function returns values of type
<code class="type">double precision</code>.
The following are valid field names:
</p>
<div class="variablelist"><dl>
<dt><span class="term"><code class="literal">century</code></span></dt>
<dd>
<p> The century
</p>
<pre class="screen">SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">20</code>
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">21</code></pre>
<p> The first century starts at 0001-01-01 00:00:00 AD, although
they did not know it at the time. This definition applies to all
Gregorian calendar countries. There is no century number 0,
you go from -1 to 1.
If you disagree with this, please write your complaint to:
Pope, Cathedral Saint-Peter of Roma, Vatican.
</p>
<p> <span class="productname">PostgreSQL</span> releases before 8.0 did not
follow the conventional numbering of centuries, but just returned
the year field divided by 100.
</p>
</dd>
<dt><span class="term"><code class="literal">day</code></span></dt>
<dd>
<p> The day (of the month) field (1 - 31)
</p>
<pre class="screen">SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">16</code></pre>
</dd>
<dt><span class="term"><code class="literal">decade</code></span></dt>
<dd>
<p> The year field divided by 10
</p>
<pre class="screen">SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">200</code></pre>
</dd>
<dt><span class="term"><code class="literal">dow</code></span></dt>
<dd>
<p> The day of the week (0 - 6; Sunday is 0) (for
<code class="type">timestamp</code> values only)
</p>
<pre class="screen">SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">5</code></pre>
<p> Note that <code class="function">extract</code>'s day of the week numbering is
different from that of the <code class="function">to_char</code> function.
</p>
</dd>
<dt><span class="term"><code class="literal">doy</code></span></dt>
<dd>
<p> The day of the year (1 - 365/366) (for <code class="type">timestamp</code> values only)
</p>
<pre class="screen">SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">47</code></pre>
</dd>
<dt><span class="term"><code class="literal">epoch</code></span></dt>
<dd>
<p> For <code class="type">date</code> and <code class="type">timestamp</code> values, the
number of seconds since 1970-01-01 00:00:00-00 (can be negative);
for <code class="type">interval</code> values, the total number
of seconds in the interval
</p>
<pre class="screen">SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-08');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">982384720</code>
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">442800</code></pre>
<p> Here is how you can convert an epoch value back to a time
stamp:
</p>
<pre class="screen">SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';</pre>
</dd>
<dt><span class="term"><code class="literal">hour</code></span></dt>
<dd>
<p> The hour field (0 - 23)
</p>
<pre class="screen">SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">20</code></pre>
</dd>
<dt><span class="term"><code class="literal">microseconds</code></span></dt>
<dd>
<p> The seconds field, including fractional parts, multiplied by 1
000 000. Note that this includes full seconds.
</p>
<pre class="screen">SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">28500000</code></pre>
</dd>
<dt><span class="term"><code class="literal">millennium</code></span></dt>
<dd>
<p> The millennium
</p>
<pre class="screen">SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">3</code></pre>
<p> Years in the 1900s are in the second millennium.
The third millennium starts January 1, 2001.
</p>
<p> <span class="productname">PostgreSQL</span> releases before 8.0 did not
follow the conventional numbering of millennia, but just returned
the year field divided by 1000.
</p>
</dd>
<dt><span class="term"><code class="literal">milliseconds</code></span></dt>
<dd>
<p> The seconds field, including fractional parts, multiplied by
1000. Note that this includes full seconds.
</p>
<pre class="screen">SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">28500</code></pre>
</dd>
<dt><span class="term"><code class="literal">minute</code></span></dt>
<dd>
<p> The minutes field (0 - 59)
</p>
<pre class="screen">SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">38</code></pre>
</dd>
<dt><span class="term"><code class="literal">month</code></span></dt>
<dd>
<p> For <code class="type">timestamp</code> values, the number of the month
within the year (1 - 12) ; for <code class="type">interval</code> values
the number of months, modulo 12 (0 - 11)
</p>
<pre class="screen">SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2</code>
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">3</code>
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">1</code></pre>
</dd>
<dt><span class="term"><code class="literal">quarter</code></span></dt>
<dd>
<p> The quarter of the year (1 - 4) that the day is in (for
<code class="type">timestamp</code> values only)
</p>
<pre class="screen">SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">1</code></pre>
</dd>
<dt><span class="term"><code class="literal">second</code></span></dt>
<dd>
<p> The seconds field, including fractional parts (0 -
59<sup>[<a name="id612950" href="#ftn.id612950">5</a>]</sup>)
</p>
<pre class="screen">SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">40</code>
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">28.5</code></pre>
</dd>
<dt><span class="term"><code class="literal">timezone</code></span></dt>
<dd><p> The time zone offset from UTC, measured in seconds. Positive values
correspond to time zones east of UTC, negative values to
zones west of UTC.
</p></dd>
<dt><span class="term"><code class="literal">timezone_hour</code></span></dt>
<dd><p> The hour component of the time zone offset
</p></dd>
<dt><span class="term"><code class="literal">timezone_minute</code></span></dt>
<dd><p> The minute component of the time zone offset
</p></dd>
<dt><span class="term"><code class="literal">week</code></span></dt>
<dd>
<p> The number of the week of the year that the day is in. By definition
(<acronym class="acronym">ISO</acronym> 8601), the first week of a year
contains January 4 of that year. (The <acronym class="acronym">ISO</acronym>-8601
week starts on Monday.) In other words, the first Thursday of
a year is in week 1 of that year. (for <code class="type">timestamp</code> values only)
</p>
<p> Because of this, it is possible for early January dates to be part of the
52nd or 53rd week of the previous year. For example, <code class="literal">2005-01-01</code>
is part of the 53rd week of year 2004, and <code class="literal">2006-01-01</code> is part of
the 52nd week of year 2005.
</p>
<pre class="screen">SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">7</code></pre>
</dd>
<dt><span class="term"><code class="literal">year</code></span></dt>
<dd>
<p> The year field. Keep in mind there is no <code class="literal">0 AD</code>, so subtracting
<code class="literal">BC</code> years from <code class="literal">AD</code> years should be done with care.
</p>
<pre class="screen">SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001</code></pre>
</dd>
</dl></div>
<p>
</p>
<p> The <code class="function">extract</code> function is primarily intended
for computational processing. For formatting date/time values for
display, see <a href="functions-formatting.html" title="9.8.Data Type Formatting Functions">Section9.8, “Data Type Formatting Functions”</a>.
</p>
<p> The <code class="function">date_part</code> function is modeled on the traditional
<span class="productname">Ingres</span> equivalent to the
<acronym class="acronym">SQL</acronym>-standard function <code class="function">extract</code>:
</p>
<pre class="synopsis">date_part('<em class="replaceable"><code>field</code></em>', <em class="replaceable"><code>source</code></em>)</pre>
<p>
Note that here the <em class="replaceable"><code>field</code></em> parameter needs to
be a string value, not a name. The valid field names for
<code class="function">date_part</code> are the same as for
<code class="function">extract</code>.
</p>
<pre class="screen">SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">16</code>
SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">4</code></pre>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="functions-datetime-trunc"></a>9.9.2.<code class="function">date_trunc</code></h3></div></div></div>
<a name="id613245"></a><p> The function <code class="function">date_trunc</code> is conceptually
similar to the <code class="function">trunc</code> function for numbers.
</p>
<pre class="synopsis">date_trunc('<em class="replaceable"><code>field</code></em>', <em class="replaceable"><code>source</code></em>)</pre>
<p>
<em class="replaceable"><code>source</code></em> is a value expression of type
<code class="type">timestamp</code> or <code class="type">interval</code>.
(Values of type <code class="type">date</code> and
<code class="type">time</code> are cast automatically, to <code class="type">timestamp</code> or
<code class="type">interval</code> respectively.)
<em class="replaceable"><code>field</code></em> selects to which precision to
truncate the input value. The return value is of type
<code class="type">timestamp</code> or <code class="type">interval</code>
with all fields that are less significant than the
selected one set to zero (or one, for day and month).
</p>
<p> Valid values for <em class="replaceable"><code>field</code></em> are:
</p>
<table class="simplelist" border="0" summary="Simple list">
<tr><td><code class="literal">microseconds</code></td></tr>
<tr><td><code class="literal">milliseconds</code></td></tr>
<tr><td><code class="literal">second</code></td></tr>
<tr><td><code class="literal">minute</code></td></tr>
<tr><td><code class="literal">hour</code></td></tr>
<tr><td><code class="literal">day</code></td></tr>
<tr><td><code class="literal">week</code></td></tr>
<tr><td><code class="literal">month</code></td></tr>
<tr><td><code class="literal">year</code></td></tr>
<tr><td><code class="literal">decade</code></td></tr>
<tr><td><code class="literal">century</code></td></tr>
<tr><td><code class="literal">millennium</code></td></tr>
</table>
<p>
</p>
<p> Examples:
</p>
<pre class="screen">SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 20:00:00</code>
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-01-01 00:00:00</code></pre>
<p>
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="functions-datetime-zoneconvert"></a>9.9.3.<code class="literal">AT TIME ZONE</code></h3></div></div></div>
<a name="id613459"></a><a name="id613468"></a><p> The <code class="literal">AT TIME ZONE</code> construct allows conversions
of time stamps to different time zones. <a href="functions-datetime.html#functions-datetime-zoneconvert-table" title="Table9.27.AT TIME ZONE Variants">Table9.27, “<code class="literal">AT TIME ZONE</code> Variants”</a> shows its
variants.
</p>
<div class="table">
<a name="functions-datetime-zoneconvert-table"></a><p class="title"><b>Table9.27.<code class="literal">AT TIME ZONE</code> Variants</b></p>
<div class="table-contents"><table summary="AT TIME ZONE Variants" border="1">
<colgroup>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Expression</th>
<th>Return Type</th>
<th>Description</th>
</tr></thead>
<tbody>
<tr>
<td> <code class="literal"><code class="type">timestamp without time zone</code> AT TIME ZONE <em class="replaceable"><code>zone</code></em></code>
</td>
<td><code class="type">timestamp with time zone</code></td>
<td>Treat given time stamp <span class="emphasis"><em>without time zone</em></span> as located in the specified time zone</td>
</tr>
<tr>
<td> <code class="literal"><code class="type">timestamp with time zone</code> AT TIME ZONE <em class="replaceable"><code>zone</code></em></code>
</td>
<td><code class="type">timestamp without time zone</code></td>
<td>Convert given time stamp <span class="emphasis"><em>with time zone</em></span> to the new time zone</td>
</tr>
<tr>
<td> <code class="literal"><code class="type">time with time zone</code> AT TIME ZONE <em class="replaceable"><code>zone</code></em></code>
</td>
<td><code class="type">time with time zone</code></td>
<td>Convert given time <span class="emphasis"><em>with time zone</em></span> to the new time zone</td>
</tr>
</tbody>
</table></div>
</div>
<br class="table-break"><p> In these expressions, the desired time zone <em class="replaceable"><code>zone</code></em> can be
specified either as a text string (e.g., <code class="literal">'PST'</code>)
or as an interval (e.g., <code class="literal">INTERVAL '-08:00'</code>).
In the text case, the available zone names are those shown in either
<a href="datetime-keywords.html#datetime-timezone-set-table" title="TableB.6.Time Zone Names for Setting timezone">TableB.6, “Time Zone Names for Setting <code class="varname">timezone</code>”</a> or
<a href="datetime-keywords.html#datetime-timezone-input-table" title="TableB.4.Time Zone Abbreviations for Input">TableB.4, “Time Zone Abbreviations for Input”</a>.
</p>
<p> Examples (supposing that the local time zone is <code class="literal">PST8PDT</code>):
</p>
<pre class="screen">SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 19:38:40-08</code>
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 18:38:40</code></pre>
<p>
The first example takes a time stamp without time zone and interprets it as MST time
(UTC-7), which is then converted to PST (UTC-8) for display. The second example takes
a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).
</p>
<p> The function <code class="literal"><code class="function">timezone</code>(<em class="replaceable"><code>zone</code></em>,
<em class="replaceable"><code>timestamp</code></em>)</code> is equivalent to the SQL-conforming construct
<code class="literal"><em class="replaceable"><code>timestamp</code></em> AT TIME ZONE
<em class="replaceable"><code>zone</code></em></code>.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="functions-datetime-current"></a>9.9.4.Current Date/Time</h3></div></div></div>
<a name="id613733"></a><a name="id613742"></a><p> The following functions are available to obtain the current date and/or
time:
</p>
<pre class="synopsis">CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME (<em class="replaceable"><code>precision</code></em>)
CURRENT_TIMESTAMP (<em class="replaceable"><code>precision</code></em>)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME (<em class="replaceable"><code>precision</code></em>)
LOCALTIMESTAMP (<em class="replaceable"><code>precision</code></em>)</pre>
<p>
</p>
<p> <code class="function">CURRENT_TIME</code> and
<code class="function">CURRENT_TIMESTAMP</code> deliver values with time zone;
<code class="function">LOCALTIME</code> and
<code class="function">LOCALTIMESTAMP</code> deliver values without time zone.
</p>
<p> <code class="function">CURRENT_TIME</code>,
<code class="function">CURRENT_TIMESTAMP</code>,
<code class="function">LOCALTIME</code>, and
<code class="function">LOCALTIMESTAMP</code>
can optionally be given
a precision parameter, which causes the result to be rounded
to that many fractional digits in the seconds field. Without a precision parameter,
the result is given to the full available precision.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> Prior to <span class="productname">PostgreSQL</span> 7.2, the precision
parameters were unimplemented, and the result was always given
in integer seconds.
</p>
</div>
<p> Some examples:
</p>
<pre class="screen">SELECT CURRENT_TIME;
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">14:39:53.662522-05</code>
SELECT CURRENT_DATE;
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-12-23</code>
SELECT CURRENT_TIMESTAMP;
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-12-23 14:39:53.662522-05</code>
SELECT CURRENT_TIMESTAMP(2);
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-12-23 14:39:53.66-05</code>
SELECT LOCALTIMESTAMP;
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-12-23 14:39:53.662522</code></pre>
<p>
</p>
<p> The function <code class="function">now()</code> is the traditional
<span class="productname">PostgreSQL</span> equivalent to
<code class="function">CURRENT_TIMESTAMP</code>.
</p>
<p> It is important to know that
<code class="function">CURRENT_TIMESTAMP</code> and related functions return
the start time of the current transaction; their values do not
change during the transaction. This is considered a feature:
the intent is to allow a single transaction to have a consistent
notion of the “<span class="quote">current</span>” time, so that multiple
modifications within the same transaction bear the same
time stamp.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> Other database systems may advance these values more
frequently.
</p>
</div>
<p> There is also the function <code class="function">timeofday()</code> which
returns the wall-clock time and advances during transactions. For
historical reasons <code class="function">timeofday()</code> returns a
<code class="type">text</code> string rather than a <code class="type">timestamp</code>
value:
</p>
<pre class="screen">SELECT timeofday();
<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">Sat Feb 17 19:07:32.000126 2001 EST</code></pre>
<p>
</p>
<p> All the date/time data types also accept the special literal value
<code class="literal">now</code> to specify the current date and time. Thus,
the following three all return the same result:
</p>
<pre class="programlisting">SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT</pre>
<p>
</p>
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Tip</h3>
<p> You do not want to use the third form when specifying a <code class="literal">DEFAULT</code>
clause while creating a table. The system will convert <code class="literal">now</code>
to a <code class="type">timestamp</code> as soon as the constant is parsed, so that when
the default value is needed,
the time of the table creation would be used! The first two
forms will not be evaluated until the default value is used,
because they are function calls. Thus they will give the desired
behavior of defaulting to the time of row insertion.
</p>
</div>
</div>
<div class="footnotes">
<br><hr width="100" align="left">
<div class="footnote"><p><sup>[<a name="ftn.id612950" href="#id612950">5</a>] </sup>60 if leap seconds are
implemented by the operating system</p></div>
</div>
</div></body>
</html>
|
