Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf176e37

Browse files
committed
Document a bunch of formerly-undocumented date/time operators,
including the SQL-spec OVERLAPS construct.
1 parent05dddf4 commitf176e37

File tree

1 file changed

+118
-21
lines changed

1 file changed

+118
-21
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 118 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.169 2003/08/31 17:32:18 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.170 2003/09/08 19:38:02 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -4575,8 +4575,8 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
45754575
<tbody>
45764576
<row>
45774577
<entry> <literal>+</literal> </entry>
4578-
<entry><literal>timestamp '2001-09-28 01:00' +interval '23 hours'</literal></entry>
4579-
<entry><literal>timestamp '2001-09-29 00:00'</literal></entry>
4578+
<entry><literal>date '2001-09-28' +integer '7'</literal></entry>
4579+
<entry><literal>date '2001-10-05'</literal></entry>
45804580
</row>
45814581

45824582
<row>
@@ -4585,16 +4585,58 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
45854585
<entry><literal>timestamp '2001-09-28 01:00'</literal></entry>
45864586
</row>
45874587

4588+
<row>
4589+
<entry> <literal>+</literal> </entry>
4590+
<entry><literal>date '2001-09-28' + time '03:00'</literal></entry>
4591+
<entry><literal>timestamp '2001-09-28 03:00'</literal></entry>
4592+
</row>
4593+
4594+
<row>
4595+
<entry> <literal>+</literal> </entry>
4596+
<entry><literal>time '03:00' + date '2001-09-28'</literal></entry>
4597+
<entry><literal>timestamp '2001-09-28 03:00'</literal></entry>
4598+
</row>
4599+
4600+
<row>
4601+
<entry> <literal>+</literal> </entry>
4602+
<entry><literal>interval '1 day' + interval '1 hour'</literal></entry>
4603+
<entry><literal>interval '1 day 01:00'</literal></entry>
4604+
</row>
4605+
4606+
<row>
4607+
<entry> <literal>+</literal> </entry>
4608+
<entry><literal>timestamp '2001-09-28 01:00' + interval '23 hours'</literal></entry>
4609+
<entry><literal>timestamp '2001-09-29 00:00'</literal></entry>
4610+
</row>
4611+
45884612
<row>
45894613
<entry> <literal>+</literal> </entry>
45904614
<entry><literal>time '01:00' + interval '3 hours'</literal></entry>
45914615
<entry><literal>time '04:00'</literal></entry>
45924616
</row>
45934617

4618+
<row>
4619+
<entry> <literal>+</literal> </entry>
4620+
<entry><literal>interval '3 hours' + time '01:00'</literal></entry>
4621+
<entry><literal>time '04:00'</literal></entry>
4622+
</row>
4623+
45944624
<row>
45954625
<entry> <literal>-</literal> </entry>
4596-
<entry><literal>timestamp '2001-09-28 23:00' - interval '23 hours'</literal></entry>
4597-
<entry><literal>timestamp '2001-09-28'</literal></entry>
4626+
<entry><literal>- interval '23 hours'</literal></entry>
4627+
<entry><literal>interval '-23:00'</literal></entry>
4628+
</row>
4629+
4630+
<row>
4631+
<entry> <literal>-</literal> </entry>
4632+
<entry><literal>date '2001-10-01' - date '2001-09-28'</literal></entry>
4633+
<entry><literal>integer '3'</literal></entry>
4634+
</row>
4635+
4636+
<row>
4637+
<entry> <literal>-</literal> </entry>
4638+
<entry><literal>date '2001-10-01' - integer '7'</literal></entry>
4639+
<entry><literal>date '2001-09-24'</literal></entry>
45984640
</row>
45994641

46004642
<row>
@@ -4603,28 +4645,58 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
46034645
<entry><literal>timestamp '2001-09-27 23:00'</literal></entry>
46044646
</row>
46054647

4648+
<row>
4649+
<entry> <literal>-</literal> </entry>
4650+
<entry><literal>time '05:00' - time '03:00'</literal></entry>
4651+
<entry><literal>interval '02:00'</literal></entry>
4652+
</row>
4653+
46064654
<row>
46074655
<entry> <literal>-</literal> </entry>
46084656
<entry><literal>time '05:00' - interval '2 hours'</literal></entry>
46094657
<entry><literal>time '03:00'</literal></entry>
46104658
</row>
46114659

4660+
<row>
4661+
<entry> <literal>-</literal> </entry>
4662+
<entry><literal>timestamp '2001-09-28 23:00' - interval '23 hours'</literal></entry>
4663+
<entry><literal>timestamp '2001-09-28 00:00'</literal></entry>
4664+
</row>
4665+
4666+
<row>
4667+
<entry> <literal>-</literal> </entry>
4668+
<entry><literal>interval '1 day' - interval '1 hour'</literal></entry>
4669+
<entry><literal>interval '23:00'</literal></entry>
4670+
</row>
4671+
46124672
<row>
46134673
<entry> <literal>-</literal> </entry>
46144674
<entry><literal>interval '2 hours' - time '05:00'</literal></entry>
4615-
<entry><literal>time '03:00:00'</literal></entry>
4675+
<entry><literal>time '03:00'</literal></entry>
4676+
</row>
4677+
4678+
<row>
4679+
<entry> <literal>-</literal> </entry>
4680+
<entry><literal>timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'</literal></entry>
4681+
<entry><literal>interval '1 day 15:00'</literal></entry>
4682+
</row>
4683+
4684+
<row>
4685+
<entry> <literal>*</literal> </entry>
4686+
<entry><literal>double precision '3.5' * interval '1 hour'</literal></entry>
4687+
<entry><literal>interval '03:30'</literal></entry>
46164688
</row>
46174689

46184690
<row>
46194691
<entry> <literal>*</literal> </entry>
4620-
<entry><literal>interval '1 hour' *int '3'</literal></entry>
4621-
<entry><literal>interval '03:00'</literal></entry>
4692+
<entry><literal>interval '1 hour' *double precision '3.5'</literal></entry>
4693+
<entry><literal>interval '03:30'</literal></entry>
46224694
</row>
46234695

46244696
<row>
46254697
<entry> <literal>/</literal> </entry>
4626-
<entry><literal>interval '1 hour' /int '3'</literal></entry>
4627-
<entry><literal>interval '00:20'</literal></entry>
4698+
<entry><literal>interval '1 hour' /double precision '1.5'</literal></entry>
4699+
<entry><literal>interval '00:40'</literal></entry>
46284700
</row>
46294701
</tbody>
46304702
</tgroup>
@@ -4714,7 +4786,7 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
47144786
linkend="functions-datetime-trunc">
47154787
</entry>
47164788
<entry><literal>date_trunc('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
4717-
<entry><literal>2001-02-16 20:00:00+00</literal></entry>
4789+
<entry><literal>2001-02-16 20:00:00</literal></entry>
47184790
</row>
47194791

47204792
<row>
@@ -4788,14 +4860,36 @@ substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
47884860
<entry>Current date and time; see <xref
47894861
linkend="functions-datetime-current">
47904862
</entry>
4791-
<entry><literal>timeofday()</literal></entry>
4792-
<entry><literal>Wed Feb 21 17:01:13.000126 2001 EST</literal></entry>
4863+
<entry></entry>
4864+
<entry></entry>
47934865
</row>
47944866

47954867
</tbody>
47964868
</tgroup>
47974869
</table>
47984870

4871+
<para>
4872+
In addition to these functions, the SQL <literal>OVERLAPS</> keyword is
4873+
supported:
4874+
<synopsis>
4875+
( <replaceable>start1</replaceable>, <replaceable>end1</replaceable> ) OVERLAPS ( <replaceable>start2</replaceable>, <replaceable>end2</replaceable> )
4876+
( <replaceable>start1</replaceable>, <replaceable>length1</replaceable> ) OVERLAPS ( <replaceable>start2</replaceable>, <replaceable>length2</replaceable> )
4877+
</synopsis>
4878+
This expression yields true when two time periods (defined by their
4879+
endpoints) overlap, false when they do not overlap. The endpoints
4880+
can be specified as pairs of dates, times, or timestamps; or as
4881+
a date, time, or timestamp followed by an interval.
4882+
</para>
4883+
4884+
<screen>
4885+
SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
4886+
(DATE '2001-10-30', DATE '2002-10-30');
4887+
<lineannotation>Result: </lineannotation><computeroutput>true</computeroutput>
4888+
SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
4889+
(DATE '2001-10-30', DATE '2002-10-30');
4890+
<lineannotation>Result: </lineannotation><computeroutput>false</computeroutput>
4891+
</screen>
4892+
47994893
<sect2 id="functions-datetime-extract">
48004894
<title><function>EXTRACT</function>, <function>date_part</function></title>
48014895

@@ -4906,8 +5000,8 @@ SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
49065000
</para>
49075001

49085002
<screen>
4909-
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
4910-
<lineannotation>Result: </lineannotation><computeroutput>982352320</computeroutput>
5003+
SELECT EXTRACT(EPOCH FROM TIMESTAMPWITH TIME ZONE'2001-02-16 20:38:40-08');
5004+
<lineannotation>Result: </lineannotation><computeroutput>982384720</computeroutput>
49115005

49125006
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
49135007
<lineannotation>Result: </lineannotation><computeroutput>442800</computeroutput>
@@ -5156,11 +5250,14 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
51565250
date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
51575251
</synopsis>
51585252
<replaceable>source</replaceable> is a value expression of type
5159-
<type>timestamp</type>. (Values of type <type>date</type> and
5160-
<type>time</type> are cast automatically.)
5253+
<type>timestamp</type> or <type>interval</>.
5254+
(Values of type <type>date</type> and
5255+
<type>time</type> are cast automatically, to <type>timestamp</type> or
5256+
<type>interval</> respectively.)
51615257
<replaceable>field</replaceable> selects to which precision to
5162-
truncate the time stamp value. The return value is of type
5163-
<type>timestamp</type> with all fields that are less than the
5258+
truncate the input value. The return value is of type
5259+
<type>timestamp</type> or <type>interval</>
5260+
with all fields that are less significant than the
51645261
selected one set to zero (or one, for day and month).
51655262
</para>
51665263

@@ -5185,10 +5282,10 @@ date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>
51855282
Examples:
51865283
<screen>
51875284
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
5188-
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00+00</computeroutput>
5285+
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00</computeroutput>
51895286

51905287
SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
5191-
<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00+00</computeroutput>
5288+
<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00</computeroutput>
51925289
</screen>
51935290
</para>
51945291
</sect2>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp