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

Commitce543b2

Browse files
author
Thomas G. Lockhart
committed
Write up new TIME WITH TIME ZONE type.
1 parentc40a0a1 commitce543b2

File tree

1 file changed

+150
-66
lines changed

1 file changed

+150
-66
lines changed

‎doc/src/sgml/datatype.sgml

Lines changed: 150 additions & 66 deletions
Original file line numberDiff line numberDiff line change
@@ -12,8 +12,7 @@
1212
<productname>Postgres</productname> has a rich set of native data
1313
types available to users.
1414
Users may add new types to <productname>Postgres</productname> using the
15-
<command>DEFINE TYPE</command>
16-
command described elsewhere.
15+
<command>DEFINE TYPE</command> command.
1716
</para>
1817

1918
<para>
@@ -80,14 +79,14 @@
8079
<entry>exact numeric for p <= 9, s = 0</entry>
8180
</row>
8281
<row>
83-
<entry>float4/8</entry>
84-
<entry>float(p)</entry>
85-
<entry>floating-point number with precisionp</entry>
82+
<entry>float4</entry>
83+
<entry>float(<replaceable>p</replaceable>), <replaceable>p</replaceable> < 7</entry>
84+
<entry>floating-point number with precision<replaceable>p</replaceable></entry>
8685
</row>
8786
<row>
8887
<entry>float8</entry>
89-
<entry>real, double precision</entry>
90-
<entry>double-precisionfloating-point number</entry>
88+
<entry>float(<replaceable>p</replaceable>), 7 <= <replaceable>p</replaceable> < 16</entry>
89+
<entry>floating-point number with precision <replaceable>p</replaceable></entry>
9190
</row>
9291
<row>
9392
<entry>inet</entry>
@@ -109,6 +108,11 @@
109108
<entry></entry>
110109
<entry>signed 8-byte integer</entry>
111110
</row>
111+
<row>
112+
<entry>interval</entry>
113+
<entry>interval</entry>
114+
<entry>general-use time span</entry>
115+
</row>
112116
<row>
113117
<entry>line</entry>
114118
<entry></entry>
@@ -155,9 +159,9 @@
155159
<entry>time of day</entry>
156160
</row>
157161
<row>
158-
<entry>timespan</entry>
159-
<entry>interval</entry>
160-
<entry>general-usetimespan</entry>
162+
<entry>timetz</entry>
163+
<entry>time with time zone</entry>
164+
<entry>time of day, includingtimezone</entry>
161165
</row>
162166
<row>
163167
<entry>timestamp</entry>
@@ -179,8 +183,11 @@
179183
<para>
180184
The <type>cidr</type> and <type>inet</type> types are designed to handle any IP type
181185
but only ipv4 is handled in the current implementation.
182-
Everything here that talks about ipv4 will apply to ipv6 in a future release.</para>
183-
</note></para>
186+
Everything here that talks about ipv4 will apply to ipv6 in a
187+
future release.
188+
</para>
189+
</note>
190+
</para>
184191

185192
<para>
186193
<table tocentry="1">
@@ -230,7 +237,8 @@
230237
which are ill considered and which should not live through subsequent standards.
231238
<productname>Postgres</productname> will not make great efforts to
232239
conform to these features; however, these tend to apply in little-used
233-
or obsure cases, and a typical user is not likely to run into them.</para>
240+
or obsure cases, and a typical user is not likely to run into them.
241+
</para>
234242

235243
<para>
236244
Most of the input and output functions corresponding to the
@@ -244,7 +252,7 @@
244252
</para>
245253

246254
<para>
247-
Note that some of the input and output functions are not invertible. That is,
255+
Some of the input and output functions are not invertible. That is,
248256
the result of an output function may lose precision when compared to
249257
the original input.
250258

@@ -256,7 +264,8 @@
256264
most of the intrinsic precision of the type (typically 15 digits for doubles,
257265
6 digits for 4-byte floats).
258266
Other types with underlying floating point fields (e.g. geometric
259-
types) carry similar precision.</para>
267+
types) carry similar precision.
268+
</para>
260269
</note>
261270
</para>
262271

@@ -390,7 +399,6 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
390399
<command>DROP SEQUENCE</command>.
391400
</para>
392401
</sect2>
393-
394402
</sect1>
395403

396404
<sect1>
@@ -399,8 +407,9 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
399407
<note>
400408
<title>Obsolete Type</title>
401409
<para>
402-
The <type>money</type> is now obsolete. Use <type>numeric</type>
403-
or <type>decimal</type> instead.
410+
The <type>money</type> is now deprecated. Use <type>numeric</type>
411+
or <type>decimal</type> instead. The money type may become a
412+
locale-aware layer over the numeric type in a future release.
404413
</para>
405414
</note>
406415

@@ -453,8 +462,8 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
453462
<productname>Postgres</productname> supports these types, in
454463
addition to the more general <type>text</type> type,
455464
which unlike <type>varchar</type>
456-
does not require an upper
457-
limitto be declaredon the size of the field.
465+
does not require anexplicit declaredupper
466+
limit on the size of the field.
458467
</para>
459468

460469
<para>
@@ -501,15 +510,15 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
501510
</para>
502511

503512
<para>
504-
There is one other fixed-length character type.
513+
There is one other fixed-length character type in <productname>Postgres</productname>.
505514
The <type>name</type> type
506-
only has one purpose and that is to provide
507-
<productname>Postgres</productname> with a
508-
special type to use for internal names.
515+
only has one purpose and that is for storage of internal catalog
516+
names.
509517
It is not intended for use by the general user.
510-
It's length is currently defined as 32chars
518+
Its length is currently defined as 32bytes (31 characters plus terminator)
511519
but should be reference using NAMEDATALEN.
512-
This is set at compile time and may change in a future release.
520+
The length is set at compile time (and is therefore adjustable for
521+
special uses); the default maximum length may change in a future release.
513522
</para>
514523

515524
<para>
@@ -528,7 +537,7 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
528537
<row>
529538
<entry>name</entry>
530539
<entry>32 bytes</entry>
531-
<entry>Thirty-two character internal type</entry>
540+
<entry>Thirty-one character internal type</entry>
532541
</row>
533542
</tbody>
534543
</tgroup>
@@ -569,6 +578,14 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
569578
<entry>AD 1465001</entry>
570579
<entry>1 microsec / 14 digits</entry>
571580
</row>
581+
<row>
582+
<entry><type>timestamp with time zone</type></entry>
583+
<entry>date and time including time zone</entry>
584+
<entry>8 bytes</entry>
585+
<entry>1903 AD</entry>
586+
<entry>2037 AD</entry>
587+
<entry>1 microsec / 14 digits</entry>
588+
</row>
572589
<row>
573590
<entry><type>interval</type></entry>
574591
<entry>for time intervals</entry>
@@ -593,6 +610,14 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
593610
<entry>23:59:59.99</entry>
594611
<entry>1 microsecond</entry>
595612
</row>
613+
<row>
614+
<entry><type>time with time zone</type></entry>
615+
<entry>times of the day</entry>
616+
<entry>4 bytes</entry>
617+
<entry>00:00:00.00+12</entry>
618+
<entry>23:59:59.99-12</entry>
619+
<entry>1 microsecond</entry>
620+
</row>
596621
</tbody>
597622
</tgroup>
598623
</table>
@@ -603,8 +628,9 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
603628
we also continue to provide <type>datetime</type> (equivalent to <type>timestamp</type>) and
604629
<type>timespan</type> (equivalent to <type>interval</type>). The types <type>abstime</type>
605630
and <type>reltime</type> are lower precision types which are used internally.
606-
You are discouraged from using any of these types in new applications and move any old
607-
ones over when appropriate. Any or all of these type might disappear in a future release.
631+
You are discouraged from using any of these types in new
632+
applications and are encouraged to move any old
633+
ones over when appropriate. Any or all of these types might disappear in a future release.
608634
</para>
609635
</note>
610636
</para>
@@ -615,18 +641,19 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
615641

616642
<para>
617643
Date and time input is accepted in almost any reasonable format, including
618-
<acronym>ISO</acronym>-compatible, <acronym>SQL</acronym>-compatible,
644+
<acronym>ISO-8601</acronym>, <acronym>SQL</acronym>-compatible,
619645
traditional <productname>Postgres</productname>, and others.
620646
The ordering of month and day in date input can be ambiguous, therefore a setting
621-
exists, to specify how it should be interpreted. The command
647+
exists to specify how it should be interpreted. The command
622648
<literal>SET DateStyle TO 'US'</literal> or <literal>SET DateStyle TO 'NonEuropean'</literal>
623649
specifies the variant <quote>month before day</quote>, the command
624650
<literal>SET DateStyle TO 'European'</literal> sets the variant
625-
<quote>day before month</quote>. The former is the default.
651+
<quote>day before month</quote>. The <literal>US</literal> style
652+
is the default but this default can be changed at compile time or at run time.
626653
</para>
627654

628655
<para>
629-
See <xreflinkend="datetime-appendix-title"endterm="datetime-appendix-title">
656+
See <xrefendterm="datetime-appendix-title"linkend="datetime-appendix-title">
630657
for the exact parsing rules of date/time input and for the recognized time zones.
631658
</para>
632659

@@ -639,6 +666,7 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
639666
<title>date</title>
640667
<para>
641668
The following are possible inputs for the <type>date</type> type.
669+
642670
<table tocentry="1">
643671
<title><productname>PostgreSQL</productname> Date Input</title>
644672
<titleabbrev>Date Inputs</titleabbrev>
@@ -813,13 +841,84 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
813841
</sect3>
814842

815843
<sect3>
816-
<title>time</title>
817-
<para>
818-
The following are valid <type>time</type> inputs.
819-
<table tocentry="1">
820-
<title><productname>PostgreSQL</productname> Time Input</title>
821-
<titleabbrev>Time Inputs</titleabbrev>
822-
<tgroup cols="2">
844+
<title>time</title>
845+
<para>
846+
The following are valid <type>time</type> inputs.
847+
848+
<table tocentry="1">
849+
<title><productname>PostgreSQL</productname> Time Input</title>
850+
<titleabbrev>Time Inputs</titleabbrev>
851+
<tgroup cols="2">
852+
<thead>
853+
<row>
854+
<entry>Example</entry>
855+
<entry>Description</entry>
856+
</row>
857+
</thead>
858+
<tbody>
859+
<row>
860+
<entry>04:05:06.789</entry>
861+
<entry>ISO-8601</entry>
862+
</row>
863+
<row>
864+
<entry>04:05:06</entry>
865+
<entry>ISO-8601</entry>
866+
</row>
867+
<row>
868+
<entry>04:05</entry>
869+
<entry>ISO-8601</entry>
870+
</row>
871+
<row>
872+
<entry>040506</entry>
873+
<entry>ISO-8601</entry>
874+
</row>
875+
<row>
876+
<entry>04:05 AM</entry>
877+
<entry>Same as 04:05; AM does not affect value</entry>
878+
</row>
879+
<row>
880+
<entry>04:05 PM</entry>
881+
<entry>Same as 16:05; input hour must be <= 12</entry>
882+
</row>
883+
<row>
884+
<entry>z</entry>
885+
<entry>Same as 00:00:00</entry>
886+
</row>
887+
<row>
888+
<entry>zulu</entry>
889+
<entry>Same as 00:00:00</entry>
890+
</row>
891+
<row>
892+
<entry>allballs</entry>
893+
<entry>Same as 00:00:00</entry>
894+
</row>
895+
</tbody>
896+
</tgroup>
897+
</table>
898+
</para>
899+
</sect3>
900+
901+
<sect3>
902+
<title>time with time zone</title>
903+
<para>
904+
This type is defined by SQL92, but the definition exhibits
905+
fundamental deficiencies which renders the type near useless. In
906+
most cases, a combination of <type>date</type>,
907+
<type>time</type>, and <type>timestamp with time zone</type>
908+
should provide a complete range of date/time functionality
909+
required by an application.
910+
</para>
911+
912+
<para>
913+
<type>time with time zone</type> accepts all input also legal
914+
for the <type>time</type> type, appended with a legal time zone,
915+
as follows:
916+
917+
<table tocentry="1">
918+
<title><productname>PostgreSQL</productname> Time With Time
919+
Zone Input</title>
920+
<titleabbrev>Time With Time Zone Inputs</titleabbrev>
921+
<tgroup cols="2">
823922
<thead>
824923
<row>
825924
<entry>Example</entry>
@@ -828,45 +927,30 @@ CREATE TABLE <replaceable class="parameter">tablename</replaceable> (<replaceabl
828927
</thead>
829928
<tbody>
830929
<row>
831-
<entry>04:05:06.789</entry>
930+
<entry>04:05:06.789-8</entry>
832931
<entry>ISO-8601</entry>
833932
</row>
834933
<row>
835-
<entry>04:05:06</entry>
934+
<entry>04:05:06-08:00</entry>
836935
<entry>ISO-8601</entry>
837936
</row>
838937
<row>
839-
<entry>04:05</entry>
938+
<entry>04:05-08:00</entry>
840939
<entry>ISO-8601</entry>
841940
</row>
842941
<row>
843-
<entry>040506</entry>
942+
<entry>040506-08/entry>
844943
<entry>ISO-8601</entry>
845944
</row>
846-
<row>
847-
<entry>04:05 AM</entry>
848-
<entry>Same as 04:05; AM does not affect value</entry>
849-
</row>
850-
<row>
851-
<entry>04:05 PM</entry>
852-
<entry>Same as 16:05; input hour must be <= 12</entry>
853-
</row>
854-
<row>
855-
<entry>z</entry>
856-
<entry>Same as 00:00:00</entry>
857-
</row>
858-
<row>
859-
<entry>zulu</entry>
860-
<entry>Same as 00:00:00</entry>
861-
</row>
862-
<row>
863-
<entry>allballs</entry>
864-
<entry>Same as 00:00:00</entry>
865-
</row>
866945
</tbody>
867946
</tgroup>
868947
</table>
869948
</para>
949+
950+
<para>
951+
Refer to <xref endterm="timezone-title" linkend="timezone"> for
952+
more examples of time zones.
953+
</para>
870954
</sect3>
871955

872956
<sect3>
@@ -888,8 +972,8 @@ January 8 04:05:06 1999 PST
888972
</para>
889973

890974
<para>
891-
<table tocentry="1">
892-
<title><productname>PostgreSQL</productname> Time Zone Input</title>
975+
<table tocentry="1" id="timezone">
976+
<title id="timezone-title"><productname>PostgreSQL</productname> Time Zone Input</title>
893977
<titleabbrev>Time Zone Inputs</titleabbrev>
894978
<tgroup cols="2">
895979
<thead>

0 commit comments

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