diff options
| author | Bruce Momjian <bruce@momjian.us> | 2000-02-06 05:09:42 +0000 |
|---|---|---|
| committer | Bruce Momjian <bruce@momjian.us> | 2000-02-06 05:09:42 +0000 |
| commit | a64d3bc4335ceabcf2122cb488c88edfa88b12ab (patch) | |
| tree | 4a81acf603563d180d5b08022346fded4ca14004 /src | |
| parent | 81fc1d5edb36773fe28f3b5dc5daf5eb7d468a61 (diff) | |
| download | postgresql-a64d3bc4335ceabcf2122cb488c88edfa88b12ab.tar.gz postgresql-a64d3bc4335ceabcf2122cb488c88edfa88b12ab.zip | |
Solaris has always had problems with 1947 in the
regression tests so I prepared a set of expected
files to make things look OK.
There's also a file to account for minor variations
in the geopmetry output and a resultmap patch to
pull them all together.
With these changes PostgreSQL, from CVS, builds and
regression tests (runcheck) cleanly.
Keith Parks.
Diffstat (limited to 'src')
21 files changed, 2965 insertions, 508 deletions
diff --git a/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-char.sql.out b/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-char.sql.out index 302a2ecc750..eb62e52c825 100644 --- a/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-char.sql.out +++ b/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-char.sql.out @@ -1,55 +1,54 @@ -abbrev|name_en |name_gr -------+--------------------+------------------------------------------------------------ -AK |Alaska |ÁëÜóêá -AL |Alabama |ÁëáìðÜìá -AZ |Arizona |Áñéæüíá -AR |Arkansas |Áñêáíóáò -ID |Idaho |Áúíôá÷ï -IA |Iowa |Áúüâá -VT |Vermont |Âåñìüíô -VA |Virginia |Âéñôæßíéá -NC |North Carolina |Âüñåéá Êáñïëßíá -ND |North Dakota |Âüñåéá Íôáêüôá -UT |Utah |Ãéïýôá -WY |Wyoming |Ãïõáúüìéíãê -WV |West Virginia |Ãïõåóô Âéñôæßíéá -WI |Wisconsin |Ãïõéóêüíóéí -WA |Washington |Ãïõüóéíãêôïí -DC |Washington DC |Ãïõüóéíãôïí Íôß Óé -IL |Illinois |Éëëéíüéò -IN |Indiana |ÉíôéÜíá -KA |Kansas |ÊÜíóáò -CA |California |Êáëéöüñíéá -KY |Kentucky |ÊåíôÜêé -CO |Colorado |ÊïëïñÜíôï -CT |Connecticut |ÊïííÝêôéêáô -LA |Louisiana |ËïõúæéÜíá -ME |Maine |ÌÝéí -MI |Michigan |Ìßôóéãêáí -MD |Maryland |Ìáßñõëáíô -MA |Massachusetts |Ìáóóá÷ïõóÝôç -MN |Minnesota |Ìéíåóóüôá -MS |Mississippi |Ìéóéóóßðé -MO |Missouri |Ìéóóïýñé -MT |Montana |ÌïíôÜíá -NJ |New Jersey |ÍÝá ÕåñóÝç -NY |New York |ÍÝá Õüñêç -NM |New Mexico |ÍÝï Ìåîéêü -NH |New Hampshire |ÍÝï ×Üìðóáúñ -NV |Nevada |ÍåâÜäá -NE |Nebraska |ÍåìðñÜóêá -DE |Delaware |ÍôÝëáãïõåñ -SC |South Carolina |Íüôéá Êáñïëßíá -SD |South Dakota |Íüôéá Íôáêüôá -OK |Oklahoma |Ïêëá÷üìá -OR |Oregon |Ïñåãêïí -OH |Ohio |Ï÷Üéï -PA |Pennsylvania |ÐåííóõëâÜíéá -RI |Rhode Island |Ñüïõíô Áúëáíô -TX |Texas |ÔÝîáò -TN |Tennessee |Ôåííåóß -GA |Georgia |Ôæüñôæéá -FL |Florida |Öëüñéíôá + abbrev | name_en | name_gr +--------+----------------------+-------------------------------------------------------------- + ID | Idaho | Áúíôá÷ï + IA | Iowa | Áúüâá + AL | Alabama | ÁëáìðÜìá + AK | Alaska | ÁëÜóêá + AZ | Arizona | Áñéæüíá + AR | Arkansas | Áñêáíóáò + VT | Vermont | Âåñìüíô + VA | Virginia | Âéñôæßíéá + NC | North Carolina | Âüñåéá Êáñïëßíá + ND | North Dakota | Âüñåéá Íôáêüôá + FL | Florida | Öëüñéíôá + IL | Illinois | Éëëéíüéò + IN | Indiana | ÉíôéÜíá + CA | California | Êáëéöüñíéá + KY | Kentucky | ÊåíôÜêé + CO | Colorado | ÊïëïñÜíôï + CT | Connecticut | ÊïííÝêôéêáô + KA | Kansas | ÊÜíóáò + LA | Louisiana | ËïõúæéÜíá + MA | Massachusetts | Ìáóóá÷ïõóÝôç + MD | Maryland | Ìáßñõëáíô + MN | Minnesota | Ìéíåóóüôá + MS | Mississippi | Ìéóéóóßðé + MO | Missouri | Ìéóóïýñé + MT | Montana | ÌïíôÜíá + ME | Maine | ÌÝéí + MI | Michigan | Ìßôóéãêáí + NV | Nevada | ÍåâÜäá + NE | Nebraska | ÍåìðñÜóêá + DE | Delaware | ÍôÝëáãïõåñ + NJ | New Jersey | ÍÝá ÕåñóÝç + NY | New York | ÍÝá Õüñêç + NH | New Hampshire | ÍÝï ×Üìðóáúñ + NM | New Mexico | ÍÝï Ìåîéêü + SC | South Carolina | Íüôéá Êáñïëßíá + SD | South Dakota | Íüôéá Íôáêüôá + OH | Ohio | Ï÷Üéï + OK | Oklahoma | Ïêëá÷üìá + OR | Oregon | Ïñåãêïí + PA | Pennsylvania | ÐåííóõëâÜíéá + TN | Tennessee | Ôåííåóß + GA | Georgia | Ôæüñôæéá + TX | Texas | ÔÝîáò + UT | Utah | Ãéïýôá + WY | Wyoming | Ãïõáúüìéíãê + WV | West Virginia | Ãïõåóô Âéñôæßíéá + WI | Wisconsin | Ãïõéóêüíóéí + WA | Washington | Ãïõüóéíãêôïí + DC | Washington DC | Ãïõüóéíãôïí Íôß Óé + RI | Rhode Island | Ñüïõíô Áúëáíô (50 rows) -EOF diff --git a/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-select.sql.out b/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-select.sql.out index d77585fdd7d..abfce69f280 100644 --- a/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-select.sql.out +++ b/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-select.sql.out @@ -1,11 +1,10 @@ -abbrev|name_en |name_gr -------+--------------------+------------------ -UT |Utah |Ãéïýôá -WY |Wyoming |Ãïõáúüìéíãê -WV |West Virginia |Ãïõåóô Âéñôæßíéá -WI |Wisconsin |Ãïõéóêüíóéí -WA |Washington |Ãïõüóéíãêôïí -DC |Washington DC |Ãïõüóéíãôïí Íôß Óé + abbrev | name_en | name_gr +--------+----------------------+-------------------- + UT | Utah | Ãéïýôá + WY | Wyoming | Ãïõáúüìéíãê + WV | West Virginia | Ãïõåóô Âéñôæßíéá + WI | Wisconsin | Ãïõéóêüíóéí + WA | Washington | Ãïõüóéíãêôïí + DC | Washington DC | Ãïõüóéíãôïí Íôß Óé (6 rows) -EOF diff --git a/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-text.sql.out b/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-text.sql.out index ce18789da01..0a128a79131 100644 --- a/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-text.sql.out +++ b/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-text.sql.out @@ -1,55 +1,54 @@ -abbrev|name_en |name_gr -------+--------------------+------------------ -AK |Alaska |ÁëÜóêá -AL |Alabama |ÁëáìðÜìá -AZ |Arizona |Áñéæüíá -AR |Arkansas |Áñêáíóáò -ID |Idaho |Áúíôá÷ï -IA |Iowa |Áúüâá -VT |Vermont |Âåñìüíô -VA |Virginia |Âéñôæßíéá -NC |North Carolina |Âüñåéá Êáñïëßíá -ND |North Dakota |Âüñåéá Íôáêüôá -UT |Utah |Ãéïýôá -WY |Wyoming |Ãïõáúüìéíãê -WV |West Virginia |Ãïõåóô Âéñôæßíéá -WI |Wisconsin |Ãïõéóêüíóéí -WA |Washington |Ãïõüóéíãêôïí -DC |Washington DC |Ãïõüóéíãôïí Íôß Óé -IL |Illinois |Éëëéíüéò -IN |Indiana |ÉíôéÜíá -KA |Kansas |ÊÜíóáò -CA |California |Êáëéöüñíéá -KY |Kentucky |ÊåíôÜêé -CO |Colorado |ÊïëïñÜíôï -CT |Connecticut |ÊïííÝêôéêáô -LA |Louisiana |ËïõúæéÜíá -ME |Maine |ÌÝéí -MI |Michigan |Ìßôóéãêáí -MD |Maryland |Ìáßñõëáíô -MA |Massachusetts |Ìáóóá÷ïõóÝôç -MN |Minnesota |Ìéíåóóüôá -MS |Mississippi |Ìéóéóóßðé -MO |Missouri |Ìéóóïýñé -MT |Montana |ÌïíôÜíá -NJ |New Jersey |ÍÝá ÕåñóÝç -NY |New York |ÍÝá Õüñêç -NM |New Mexico |ÍÝï Ìåîéêü -NH |New Hampshire |ÍÝï ×Üìðóáúñ -NV |Nevada |ÍåâÜäá -NE |Nebraska |ÍåìðñÜóêá -DE |Delaware |ÍôÝëáãïõåñ -SC |South Carolina |Íüôéá Êáñïëßíá -SD |South Dakota |Íüôéá Íôáêüôá -OK |Oklahoma |Ïêëá÷üìá -OR |Oregon |Ïñåãêïí -OH |Ohio |Ï÷Üéï -PA |Pennsylvania |ÐåííóõëâÜíéá -RI |Rhode Island |Ñüïõíô Áúëáíô -TX |Texas |ÔÝîáò -TN |Tennessee |Ôåííåóß -GA |Georgia |Ôæüñôæéá -FL |Florida |Öëüñéíôá + abbrev | name_en | name_gr +--------+----------------------+-------------------- + ID | Idaho | Áúíôá÷ï + IA | Iowa | Áúüâá + AL | Alabama | ÁëáìðÜìá + AK | Alaska | ÁëÜóêá + AZ | Arizona | Áñéæüíá + AR | Arkansas | Áñêáíóáò + VT | Vermont | Âåñìüíô + VA | Virginia | Âéñôæßíéá + NC | North Carolina | Âüñåéá Êáñïëßíá + ND | North Dakota | Âüñåéá Íôáêüôá + FL | Florida | Öëüñéíôá + IL | Illinois | Éëëéíüéò + IN | Indiana | ÉíôéÜíá + CA | California | Êáëéöüñíéá + KY | Kentucky | ÊåíôÜêé + CO | Colorado | ÊïëïñÜíôï + CT | Connecticut | ÊïííÝêôéêáô + KA | Kansas | ÊÜíóáò + LA | Louisiana | ËïõúæéÜíá + MA | Massachusetts | Ìáóóá÷ïõóÝôç + MD | Maryland | Ìáßñõëáíô + MN | Minnesota | Ìéíåóóüôá + MS | Mississippi | Ìéóéóóßðé + MO | Missouri | Ìéóóïýñé + MT | Montana | ÌïíôÜíá + ME | Maine | ÌÝéí + MI | Michigan | Ìßôóéãêáí + NV | Nevada | ÍåâÜäá + NE | Nebraska | ÍåìðñÜóêá + DE | Delaware | ÍôÝëáãïõåñ + NJ | New Jersey | ÍÝá ÕåñóÝç + NY | New York | ÍÝá Õüñêç + NH | New Hampshire | ÍÝï ×Üìðóáúñ + NM | New Mexico | ÍÝï Ìåîéêü + SC | South Carolina | Íüôéá Êáñïëßíá + SD | South Dakota | Íüôéá Íôáêüôá + OH | Ohio | Ï÷Üéï + OK | Oklahoma | Ïêëá÷üìá + OR | Oregon | Ïñåãêïí + PA | Pennsylvania | ÐåííóõëâÜíéá + TN | Tennessee | Ôåííåóß + GA | Georgia | Ôæüñôæéá + TX | Texas | ÔÝîáò + UT | Utah | Ãéïýôá + WY | Wyoming | Ãïõáúüìéíãê + WV | West Virginia | Ãïõåóô Âéñôæßíéá + WI | Wisconsin | Ãïõéóêüíóéí + WA | Washington | Ãïõüóéíãêôïí + DC | Washington DC | Ãïõüóéíãôïí Íôß Óé + RI | Rhode Island | Ñüïõíô Áúëáíô (50 rows) -EOF diff --git a/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-varchar.sql.out b/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-varchar.sql.out index ce18789da01..0a128a79131 100644 --- a/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-varchar.sql.out +++ b/src/test/locale/gr_GR.ISO8859-7/expected/test-gr-varchar.sql.out @@ -1,55 +1,54 @@ -abbrev|name_en |name_gr -------+--------------------+------------------ -AK |Alaska |ÁëÜóêá -AL |Alabama |ÁëáìðÜìá -AZ |Arizona |Áñéæüíá -AR |Arkansas |Áñêáíóáò -ID |Idaho |Áúíôá÷ï -IA |Iowa |Áúüâá -VT |Vermont |Âåñìüíô -VA |Virginia |Âéñôæßíéá -NC |North Carolina |Âüñåéá Êáñïëßíá -ND |North Dakota |Âüñåéá Íôáêüôá -UT |Utah |Ãéïýôá -WY |Wyoming |Ãïõáúüìéíãê -WV |West Virginia |Ãïõåóô Âéñôæßíéá -WI |Wisconsin |Ãïõéóêüíóéí -WA |Washington |Ãïõüóéíãêôïí -DC |Washington DC |Ãïõüóéíãôïí Íôß Óé -IL |Illinois |Éëëéíüéò -IN |Indiana |ÉíôéÜíá -KA |Kansas |ÊÜíóáò -CA |California |Êáëéöüñíéá -KY |Kentucky |ÊåíôÜêé -CO |Colorado |ÊïëïñÜíôï -CT |Connecticut |ÊïííÝêôéêáô -LA |Louisiana |ËïõúæéÜíá -ME |Maine |ÌÝéí -MI |Michigan |Ìßôóéãêáí -MD |Maryland |Ìáßñõëáíô -MA |Massachusetts |Ìáóóá÷ïõóÝôç -MN |Minnesota |Ìéíåóóüôá -MS |Mississippi |Ìéóéóóßðé -MO |Missouri |Ìéóóïýñé -MT |Montana |ÌïíôÜíá -NJ |New Jersey |ÍÝá ÕåñóÝç -NY |New York |ÍÝá Õüñêç -NM |New Mexico |ÍÝï Ìåîéêü -NH |New Hampshire |ÍÝï ×Üìðóáúñ -NV |Nevada |ÍåâÜäá -NE |Nebraska |ÍåìðñÜóêá -DE |Delaware |ÍôÝëáãïõåñ -SC |South Carolina |Íüôéá Êáñïëßíá -SD |South Dakota |Íüôéá Íôáêüôá -OK |Oklahoma |Ïêëá÷üìá -OR |Oregon |Ïñåãêïí -OH |Ohio |Ï÷Üéï -PA |Pennsylvania |ÐåííóõëâÜíéá -RI |Rhode Island |Ñüïõíô Áúëáíô -TX |Texas |ÔÝîáò -TN |Tennessee |Ôåííåóß -GA |Georgia |Ôæüñôæéá -FL |Florida |Öëüñéíôá + abbrev | name_en | name_gr +--------+----------------------+-------------------- + ID | Idaho | Áúíôá÷ï + IA | Iowa | Áúüâá + AL | Alabama | ÁëáìðÜìá + AK | Alaska | ÁëÜóêá + AZ | Arizona | Áñéæüíá + AR | Arkansas | Áñêáíóáò + VT | Vermont | Âåñìüíô + VA | Virginia | Âéñôæßíéá + NC | North Carolina | Âüñåéá Êáñïëßíá + ND | North Dakota | Âüñåéá Íôáêüôá + FL | Florida | Öëüñéíôá + IL | Illinois | Éëëéíüéò + IN | Indiana | ÉíôéÜíá + CA | California | Êáëéöüñíéá + KY | Kentucky | ÊåíôÜêé + CO | Colorado | ÊïëïñÜíôï + CT | Connecticut | ÊïííÝêôéêáô + KA | Kansas | ÊÜíóáò + LA | Louisiana | ËïõúæéÜíá + MA | Massachusetts | Ìáóóá÷ïõóÝôç + MD | Maryland | Ìáßñõëáíô + MN | Minnesota | Ìéíåóóüôá + MS | Mississippi | Ìéóéóóßðé + MO | Missouri | Ìéóóïýñé + MT | Montana | ÌïíôÜíá + ME | Maine | ÌÝéí + MI | Michigan | Ìßôóéãêáí + NV | Nevada | ÍåâÜäá + NE | Nebraska | ÍåìðñÜóêá + DE | Delaware | ÍôÝëáãïõåñ + NJ | New Jersey | ÍÝá ÕåñóÝç + NY | New York | ÍÝá Õüñêç + NH | New Hampshire | ÍÝï ×Üìðóáúñ + NM | New Mexico | ÍÝï Ìåîéêü + SC | South Carolina | Íüôéá Êáñïëßíá + SD | South Dakota | Íüôéá Íôáêüôá + OH | Ohio | Ï÷Üéï + OK | Oklahoma | Ïêëá÷üìá + OR | Oregon | Ïñåãêïí + PA | Pennsylvania | ÐåííóõëâÜíéá + TN | Tennessee | Ôåííåóß + GA | Georgia | Ôæüñôæéá + TX | Texas | ÔÝîáò + UT | Utah | Ãéïýôá + WY | Wyoming | Ãïõáúüìéíãê + WV | West Virginia | Ãïõåóô Âéñôæßíéá + WI | Wisconsin | Ãïõéóêüíóéí + WA | Washington | Ãïõüóéíãêôïí + DC | Washington DC | Ãïõüóéíãôïí Íôß Óé + RI | Rhode Island | Ñüïõíô Áúëáíô (50 rows) -EOF diff --git a/src/test/locale/gr_GR.ISO8859-7/runall b/src/test/locale/gr_GR.ISO8859-7/runall index 94bbd0c8c8d..da7650bb7fe 100755 --- a/src/test/locale/gr_GR.ISO8859-7/runall +++ b/src/test/locale/gr_GR.ISO8859-7/runall @@ -39,7 +39,7 @@ for f in char varchar text; do fi echo "Testing PgSQL: sort on $ftype type..." - destroydb testlocale >/dev/null 2>&1 + dropdb testlocale >/dev/null 2>&1 createdb testlocale || abort "createdb failed" psql -d testlocale -c "CREATE TABLE usastates (abbrev char(2), name_en char(20), name_gr $ftype);" >/dev/null 2>&1 || abort "createtable failed" psql testlocale < test-gr.sql.in > test-gr-$f.sql.out 2>/dev/null || abort "test query failed" @@ -48,5 +48,5 @@ done echo "Testing PgSQL: select on regexp..." psql testlocale < test-gr-select.sql.in > test-gr-select.sql.out 2>/dev/null || abort "select query failed" diff expected/test-gr-select.sql.out test-gr-select.sql.out -destroydb testlocale || abort "destroydb failed" +dropdb testlocale || abort "dropdb failed" echo "Finished." diff --git a/src/test/locale/koi8-r/expected/test-koi8-char.sql.out b/src/test/locale/koi8-r/expected/test-koi8-char.sql.out index c574ff06776..95f52d8af80 100644 --- a/src/test/locale/koi8-r/expected/test-koi8-char.sql.out +++ b/src/test/locale/koi8-r/expected/test-koi8-char.sql.out @@ -1,55 +1,54 @@ -abbrev|name_en |name_ru -------+--------------------+------------------------------------------------------------ -ID |Idaho |áÊÄÁÈÏ -IA |Iowa |áÊÏ×Á -AL |Alabama |áÌÁÂÁÍÁ -AK |Alaska |áÌÑÓËÁ -AZ |Arizona |áÒÉÚÏÎÁ -AR |Arkansas |áÒËÁÎÚÁÓ -WY |Wyoming |÷ÁÊÏÍÉÎÇ -WA |Washington |÷ÁÛÉÎÇÔÏÎ -VT |Vermont |÷ÅÒÍÏÎÔ -VA |Virginia |÷ÉÒÄÖÉÎÉÑ -WI |Wisconsin |÷ÉÓËÏÎÓÉÎ -DE |Delaware |äÅÌÁ×ÜÒ -GA |Georgia |äÖÏÒÄÖÉÑ -WV |West Virginia |úÁÐÁÄÎÁÑ ÷ÉÒÄÖÉÎÉÑ -IL |Illinois |éÌÌÉÎÏÊÓ -IN |Indiana |éÎÄÉÁÎÁ -CA |California |ëÁÌÉÆÏÒÎÉÑ -KA |Kansas |ëÁÎÚÁÓ -KY |Kentucky |ëÅÎÔÕËËÉ -CO |Colorado |ëÏÌÏÒÁÄÏ -CT |Connecticut |ëÏÎÎÅËÔÉËÕÔ -LA |Louisiana |ìÕÉÚÉÁÎÁ -MA |Massachusetts |íÁÓÓÁÞÕÓÅÔÓ -MN |Minnesota |íÉÎÎÅÓÏÔÁ -MS |Mississippi |íÉÓÓÉÓÉÐÉ -MO |Missouri |íÉÓÓÕÒÉ -MI |Michigan |íÉÞÉÇÁÎ -MT |Montana |íÏÎÔÁÎÁ -ME |Maine |íÜÎ -MD |Maryland |íÜÒÉÌÅÎÄ -NE |Nebraska |îÅÂÒÁÓËÁ -NV |Nevada |îÅ×ÁÄÁ -NH |New Hampshire |îØÀ-çÜÍÐÛÉÒ -NJ |New Jersey |îØÀ-äÖÅÒÓÉ -NY |New York |îØÀ-êÏÒË -NM |New Mexico |îØÀ-íÅËÓÉËÏ -OH |Ohio |ïÇÁÊÏ -OK |Oklahoma |ïËÌÁÈÏÍÁ -DC |Washington DC |ÏËÒÕÇ ëÏÌÕÍÂÉÑ (ÓÏÚÄÁÎ ÓÐÅÃÉÁÌØÎÏ ÐÏÄ ÓÔÏÌÉÃÕ) -OR |Oregon |ïÒÅÇÏÎ -PA |Pennsylvania |ðÅÎÓÉÌØ×ÁÎÉÑ -RI |Rhode Island |òÏÄ-áÊÌÅÎÄ -ND |North Dakota |óÅ×ÅÒÎÁÑ äÁËÏÔÁ -NC |North Carolina |óÅ×ÅÒÎÁÑ ëÁÒÏÌÉÎÁ -TN |Tennessee |ôÅÎÎÅÓÓÉ -TX |Texas |ôÅÈÁÓ -FL |Florida |æÌÏÒÉÄÁ -SD |South Dakota |àÖÎÁÑ äÁËÏÔÁ -SC |South Carolina |àÖÎÁÑ ëÁÒÏÌÉÎÁ -UT |Utah |àÔÁ + abbrev | name_en | name_ru +--------+----------------------+-------------------------------------------------------------- + ID | Idaho | áÊÄÁÈÏ + IA | Iowa | áÊÏ×Á + AL | Alabama | áÌÁÂÁÍÁ + AK | Alaska | áÌÑÓËÁ + AZ | Arizona | áÒÉÚÏÎÁ + AR | Arkansas | áÒËÁÎÚÁÓ + WY | Wyoming | ÷ÁÊÏÍÉÎÇ + WA | Washington | ÷ÁÛÉÎÇÔÏÎ + VT | Vermont | ÷ÅÒÍÏÎÔ + VA | Virginia | ÷ÉÒÄÖÉÎÉÑ + WI | Wisconsin | ÷ÉÓËÏÎÓÉÎ + DE | Delaware | äÅÌÁ×ÜÒ + GA | Georgia | äÖÏÒÄÖÉÑ + WV | West Virginia | úÁÐÁÄÎÁÑ ÷ÉÒÄÖÉÎÉÑ + IL | Illinois | éÌÌÉÎÏÊÓ + IN | Indiana | éÎÄÉÁÎÁ + CA | California | ëÁÌÉÆÏÒÎÉÑ + KA | Kansas | ëÁÎÚÁÓ + KY | Kentucky | ëÅÎÔÕËËÉ + CO | Colorado | ëÏÌÏÒÁÄÏ + CT | Connecticut | ëÏÎÎÅËÔÉËÕÔ + LA | Louisiana | ìÕÉÚÉÁÎÁ + MA | Massachusetts | íÁÓÓÁÞÕÓÅÔÓ + MN | Minnesota | íÉÎÎÅÓÏÔÁ + MS | Mississippi | íÉÓÓÉÓÉÐÉ + MO | Missouri | íÉÓÓÕÒÉ + MI | Michigan | íÉÞÉÇÁÎ + MT | Montana | íÏÎÔÁÎÁ + ME | Maine | íÜÎ + MD | Maryland | íÜÒÉÌÅÎÄ + NE | Nebraska | îÅÂÒÁÓËÁ + NV | Nevada | îÅ×ÁÄÁ + NH | New Hampshire | îØÀ-çÜÍÐÛÉÒ + NJ | New Jersey | îØÀ-äÖÅÒÓÉ + NY | New York | îØÀ-êÏÒË + NM | New Mexico | îØÀ-íÅËÓÉËÏ + OH | Ohio | ïÇÁÊÏ + OK | Oklahoma | ïËÌÁÈÏÍÁ + DC | Washington DC | ÏËÒÕÇ ëÏÌÕÍÂÉÑ (ÓÏÚÄÁÎ ÓÐÅÃÉÁÌØÎÏ ÐÏÄ ÓÔÏÌÉÃÕ) + OR | Oregon | ïÒÅÇÏÎ + PA | Pennsylvania | ðÅÎÓÉÌØ×ÁÎÉÑ + RI | Rhode Island | òÏÄ-áÊÌÅÎÄ + ND | North Dakota | óÅ×ÅÒÎÁÑ äÁËÏÔÁ + NC | North Carolina | óÅ×ÅÒÎÁÑ ëÁÒÏÌÉÎÁ + TN | Tennessee | ôÅÎÎÅÓÓÉ + TX | Texas | ôÅÈÁÓ + FL | Florida | æÌÏÒÉÄÁ + SD | South Dakota | àÖÎÁÑ äÁËÏÔÁ + SC | South Carolina | àÖÎÁÑ ëÁÒÏÌÉÎÁ + UT | Utah | àÔÁ (50 rows) -EOF diff --git a/src/test/locale/koi8-r/expected/test-koi8-select.sql.out b/src/test/locale/koi8-r/expected/test-koi8-select.sql.out index 1e9f97de3c6..f5be657f162 100644 --- a/src/test/locale/koi8-r/expected/test-koi8-select.sql.out +++ b/src/test/locale/koi8-r/expected/test-koi8-select.sql.out @@ -1,9 +1,8 @@ -abbrev|name_en |name_ru -------+--------------------+---------------------------------------------- -OH |Ohio |ïÇÁÊÏ -OK |Oklahoma |ïËÌÁÈÏÍÁ -DC |Washington DC |ÏËÒÕÇ ëÏÌÕÍÂÉÑ (ÓÏÚÄÁÎ ÓÐÅÃÉÁÌØÎÏ ÐÏÄ ÓÔÏÌÉÃÕ) -OR |Oregon |ïÒÅÇÏÎ + abbrev | name_en | name_ru +--------+----------------------+------------------------------------------------ + OH | Ohio | ïÇÁÊÏ + OK | Oklahoma | ïËÌÁÈÏÍÁ + DC | Washington DC | ÏËÒÕÇ ëÏÌÕÍÂÉÑ (ÓÏÚÄÁÎ ÓÐÅÃÉÁÌØÎÏ ÐÏÄ ÓÔÏÌÉÃÕ) + OR | Oregon | ïÒÅÇÏÎ (4 rows) -EOF diff --git a/src/test/locale/koi8-r/expected/test-koi8-text.sql.out b/src/test/locale/koi8-r/expected/test-koi8-text.sql.out index 210a8e2b683..3dc3acf4b19 100644 --- a/src/test/locale/koi8-r/expected/test-koi8-text.sql.out +++ b/src/test/locale/koi8-r/expected/test-koi8-text.sql.out @@ -1,55 +1,54 @@ -abbrev|name_en |name_ru -------+--------------------+---------------------------------------------- -ID |Idaho |áÊÄÁÈÏ -IA |Iowa |áÊÏ×Á -AL |Alabama |áÌÁÂÁÍÁ -AK |Alaska |áÌÑÓËÁ -AZ |Arizona |áÒÉÚÏÎÁ -AR |Arkansas |áÒËÁÎÚÁÓ -WY |Wyoming |÷ÁÊÏÍÉÎÇ -WA |Washington |÷ÁÛÉÎÇÔÏÎ -VT |Vermont |÷ÅÒÍÏÎÔ -VA |Virginia |÷ÉÒÄÖÉÎÉÑ -WI |Wisconsin |÷ÉÓËÏÎÓÉÎ -DE |Delaware |äÅÌÁ×ÜÒ -GA |Georgia |äÖÏÒÄÖÉÑ -WV |West Virginia |úÁÐÁÄÎÁÑ ÷ÉÒÄÖÉÎÉÑ -IL |Illinois |éÌÌÉÎÏÊÓ -IN |Indiana |éÎÄÉÁÎÁ -CA |California |ëÁÌÉÆÏÒÎÉÑ -KA |Kansas |ëÁÎÚÁÓ -KY |Kentucky |ëÅÎÔÕËËÉ -CO |Colorado |ëÏÌÏÒÁÄÏ -CT |Connecticut |ëÏÎÎÅËÔÉËÕÔ -LA |Louisiana |ìÕÉÚÉÁÎÁ -MA |Massachusetts |íÁÓÓÁÞÕÓÅÔÓ -MN |Minnesota |íÉÎÎÅÓÏÔÁ -MS |Mississippi |íÉÓÓÉÓÉÐÉ -MO |Missouri |íÉÓÓÕÒÉ -MI |Michigan |íÉÞÉÇÁÎ -MT |Montana |íÏÎÔÁÎÁ -ME |Maine |íÜÎ -MD |Maryland |íÜÒÉÌÅÎÄ -NE |Nebraska |îÅÂÒÁÓËÁ -NV |Nevada |îÅ×ÁÄÁ -NH |New Hampshire |îØÀ-çÜÍÐÛÉÒ -NJ |New Jersey |îØÀ-äÖÅÒÓÉ -NY |New York |îØÀ-êÏÒË -NM |New Mexico |îØÀ-íÅËÓÉËÏ -OH |Ohio |ïÇÁÊÏ -OK |Oklahoma |ïËÌÁÈÏÍÁ -DC |Washington DC |ÏËÒÕÇ ëÏÌÕÍÂÉÑ (ÓÏÚÄÁÎ ÓÐÅÃÉÁÌØÎÏ ÐÏÄ ÓÔÏÌÉÃÕ) -OR |Oregon |ïÒÅÇÏÎ -PA |Pennsylvania |ðÅÎÓÉÌØ×ÁÎÉÑ -RI |Rhode Island |òÏÄ-áÊÌÅÎÄ -ND |North Dakota |óÅ×ÅÒÎÁÑ äÁËÏÔÁ -NC |North Carolina |óÅ×ÅÒÎÁÑ ëÁÒÏÌÉÎÁ -TN |Tennessee |ôÅÎÎÅÓÓÉ -TX |Texas |ôÅÈÁÓ -FL |Florida |æÌÏÒÉÄÁ -SD |South Dakota |àÖÎÁÑ äÁËÏÔÁ -SC |South Carolina |àÖÎÁÑ ëÁÒÏÌÉÎÁ -UT |Utah |àÔÁ + abbrev | name_en | name_ru +--------+----------------------+------------------------------------------------ + ID | Idaho | áÊÄÁÈÏ + IA | Iowa | áÊÏ×Á + AL | Alabama | áÌÁÂÁÍÁ + AK | Alaska | áÌÑÓËÁ + AZ | Arizona | áÒÉÚÏÎÁ + AR | Arkansas | áÒËÁÎÚÁÓ + WY | Wyoming | ÷ÁÊÏÍÉÎÇ + WA | Washington | ÷ÁÛÉÎÇÔÏÎ + VT | Vermont | ÷ÅÒÍÏÎÔ + VA | Virginia | ÷ÉÒÄÖÉÎÉÑ + WI | Wisconsin | ÷ÉÓËÏÎÓÉÎ + DE | Delaware | äÅÌÁ×ÜÒ + GA | Georgia | äÖÏÒÄÖÉÑ + WV | West Virginia | úÁÐÁÄÎÁÑ ÷ÉÒÄÖÉÎÉÑ + IL | Illinois | éÌÌÉÎÏÊÓ + IN | Indiana | éÎÄÉÁÎÁ + CA | California | ëÁÌÉÆÏÒÎÉÑ + KA | Kansas | ëÁÎÚÁÓ + KY | Kentucky | ëÅÎÔÕËËÉ + CO | Colorado | ëÏÌÏÒÁÄÏ + CT | Connecticut | ëÏÎÎÅËÔÉËÕÔ + LA | Louisiana | ìÕÉÚÉÁÎÁ + MA | Massachusetts | íÁÓÓÁÞÕÓÅÔÓ + MN | Minnesota | íÉÎÎÅÓÏÔÁ + MS | Mississippi | íÉÓÓÉÓÉÐÉ + MO | Missouri | íÉÓÓÕÒÉ + MI | Michigan | íÉÞÉÇÁÎ + MT | Montana | íÏÎÔÁÎÁ + ME | Maine | íÜÎ + MD | Maryland | íÜÒÉÌÅÎÄ + NE | Nebraska | îÅÂÒÁÓËÁ + NV | Nevada | îÅ×ÁÄÁ + NH | New Hampshire | îØÀ-çÜÍÐÛÉÒ + NJ | New Jersey | îØÀ-äÖÅÒÓÉ + NY | New York | îØÀ-êÏÒË + NM | New Mexico | îØÀ-íÅËÓÉËÏ + OH | Ohio | ïÇÁÊÏ + OK | Oklahoma | ïËÌÁÈÏÍÁ + DC | Washington DC | ÏËÒÕÇ ëÏÌÕÍÂÉÑ (ÓÏÚÄÁÎ ÓÐÅÃÉÁÌØÎÏ ÐÏÄ ÓÔÏÌÉÃÕ) + OR | Oregon | ïÒÅÇÏÎ + PA | Pennsylvania | ðÅÎÓÉÌØ×ÁÎÉÑ + RI | Rhode Island | òÏÄ-áÊÌÅÎÄ + ND | North Dakota | óÅ×ÅÒÎÁÑ äÁËÏÔÁ + NC | North Carolina | óÅ×ÅÒÎÁÑ ëÁÒÏÌÉÎÁ + TN | Tennessee | ôÅÎÎÅÓÓÉ + TX | Texas | ôÅÈÁÓ + FL | Florida | æÌÏÒÉÄÁ + SD | South Dakota | àÖÎÁÑ äÁËÏÔÁ + SC | South Carolina | àÖÎÁÑ ëÁÒÏÌÉÎÁ + UT | Utah | àÔÁ (50 rows) -EOF diff --git a/src/test/locale/koi8-r/expected/test-koi8-varchar.sql.out b/src/test/locale/koi8-r/expected/test-koi8-varchar.sql.out index 210a8e2b683..3dc3acf4b19 100644 --- a/src/test/locale/koi8-r/expected/test-koi8-varchar.sql.out +++ b/src/test/locale/koi8-r/expected/test-koi8-varchar.sql.out @@ -1,55 +1,54 @@ -abbrev|name_en |name_ru -------+--------------------+---------------------------------------------- -ID |Idaho |áÊÄÁÈÏ -IA |Iowa |áÊÏ×Á -AL |Alabama |áÌÁÂÁÍÁ -AK |Alaska |áÌÑÓËÁ -AZ |Arizona |áÒÉÚÏÎÁ -AR |Arkansas |áÒËÁÎÚÁÓ -WY |Wyoming |÷ÁÊÏÍÉÎÇ -WA |Washington |÷ÁÛÉÎÇÔÏÎ -VT |Vermont |÷ÅÒÍÏÎÔ -VA |Virginia |÷ÉÒÄÖÉÎÉÑ -WI |Wisconsin |÷ÉÓËÏÎÓÉÎ -DE |Delaware |äÅÌÁ×ÜÒ -GA |Georgia |äÖÏÒÄÖÉÑ -WV |West Virginia |úÁÐÁÄÎÁÑ ÷ÉÒÄÖÉÎÉÑ -IL |Illinois |éÌÌÉÎÏÊÓ -IN |Indiana |éÎÄÉÁÎÁ -CA |California |ëÁÌÉÆÏÒÎÉÑ -KA |Kansas |ëÁÎÚÁÓ -KY |Kentucky |ëÅÎÔÕËËÉ -CO |Colorado |ëÏÌÏÒÁÄÏ -CT |Connecticut |ëÏÎÎÅËÔÉËÕÔ -LA |Louisiana |ìÕÉÚÉÁÎÁ -MA |Massachusetts |íÁÓÓÁÞÕÓÅÔÓ -MN |Minnesota |íÉÎÎÅÓÏÔÁ -MS |Mississippi |íÉÓÓÉÓÉÐÉ -MO |Missouri |íÉÓÓÕÒÉ -MI |Michigan |íÉÞÉÇÁÎ -MT |Montana |íÏÎÔÁÎÁ -ME |Maine |íÜÎ -MD |Maryland |íÜÒÉÌÅÎÄ -NE |Nebraska |îÅÂÒÁÓËÁ -NV |Nevada |îÅ×ÁÄÁ -NH |New Hampshire |îØÀ-çÜÍÐÛÉÒ -NJ |New Jersey |îØÀ-äÖÅÒÓÉ -NY |New York |îØÀ-êÏÒË -NM |New Mexico |îØÀ-íÅËÓÉËÏ -OH |Ohio |ïÇÁÊÏ -OK |Oklahoma |ïËÌÁÈÏÍÁ -DC |Washington DC |ÏËÒÕÇ ëÏÌÕÍÂÉÑ (ÓÏÚÄÁÎ ÓÐÅÃÉÁÌØÎÏ ÐÏÄ ÓÔÏÌÉÃÕ) -OR |Oregon |ïÒÅÇÏÎ -PA |Pennsylvania |ðÅÎÓÉÌØ×ÁÎÉÑ -RI |Rhode Island |òÏÄ-áÊÌÅÎÄ -ND |North Dakota |óÅ×ÅÒÎÁÑ äÁËÏÔÁ -NC |North Carolina |óÅ×ÅÒÎÁÑ ëÁÒÏÌÉÎÁ -TN |Tennessee |ôÅÎÎÅÓÓÉ -TX |Texas |ôÅÈÁÓ -FL |Florida |æÌÏÒÉÄÁ -SD |South Dakota |àÖÎÁÑ äÁËÏÔÁ -SC |South Carolina |àÖÎÁÑ ëÁÒÏÌÉÎÁ -UT |Utah |àÔÁ + abbrev | name_en | name_ru +--------+----------------------+------------------------------------------------ + ID | Idaho | áÊÄÁÈÏ + IA | Iowa | áÊÏ×Á + AL | Alabama | áÌÁÂÁÍÁ + AK | Alaska | áÌÑÓËÁ + AZ | Arizona | áÒÉÚÏÎÁ + AR | Arkansas | áÒËÁÎÚÁÓ + WY | Wyoming | ÷ÁÊÏÍÉÎÇ + WA | Washington | ÷ÁÛÉÎÇÔÏÎ + VT | Vermont | ÷ÅÒÍÏÎÔ + VA | Virginia | ÷ÉÒÄÖÉÎÉÑ + WI | Wisconsin | ÷ÉÓËÏÎÓÉÎ + DE | Delaware | äÅÌÁ×ÜÒ + GA | Georgia | äÖÏÒÄÖÉÑ + WV | West Virginia | úÁÐÁÄÎÁÑ ÷ÉÒÄÖÉÎÉÑ + IL | Illinois | éÌÌÉÎÏÊÓ + IN | Indiana | éÎÄÉÁÎÁ + CA | California | ëÁÌÉÆÏÒÎÉÑ + KA | Kansas | ëÁÎÚÁÓ + KY | Kentucky | ëÅÎÔÕËËÉ + CO | Colorado | ëÏÌÏÒÁÄÏ + CT | Connecticut | ëÏÎÎÅËÔÉËÕÔ + LA | Louisiana | ìÕÉÚÉÁÎÁ + MA | Massachusetts | íÁÓÓÁÞÕÓÅÔÓ + MN | Minnesota | íÉÎÎÅÓÏÔÁ + MS | Mississippi | íÉÓÓÉÓÉÐÉ + MO | Missouri | íÉÓÓÕÒÉ + MI | Michigan | íÉÞÉÇÁÎ + MT | Montana | íÏÎÔÁÎÁ + ME | Maine | íÜÎ + MD | Maryland | íÜÒÉÌÅÎÄ + NE | Nebraska | îÅÂÒÁÓËÁ + NV | Nevada | îÅ×ÁÄÁ + NH | New Hampshire | îØÀ-çÜÍÐÛÉÒ + NJ | New Jersey | îØÀ-äÖÅÒÓÉ + NY | New York | îØÀ-êÏÒË + NM | New Mexico | îØÀ-íÅËÓÉËÏ + OH | Ohio | ïÇÁÊÏ + OK | Oklahoma | ïËÌÁÈÏÍÁ + DC | Washington DC | ÏËÒÕÇ ëÏÌÕÍÂÉÑ (ÓÏÚÄÁÎ ÓÐÅÃÉÁÌØÎÏ ÐÏÄ ÓÔÏÌÉÃÕ) + OR | Oregon | ïÒÅÇÏÎ + PA | Pennsylvania | ðÅÎÓÉÌØ×ÁÎÉÑ + RI | Rhode Island | òÏÄ-áÊÌÅÎÄ + ND | North Dakota | óÅ×ÅÒÎÁÑ äÁËÏÔÁ + NC | North Carolina | óÅ×ÅÒÎÁÑ ëÁÒÏÌÉÎÁ + TN | Tennessee | ôÅÎÎÅÓÓÉ + TX | Texas | ôÅÈÁÓ + FL | Florida | æÌÏÒÉÄÁ + SD | South Dakota | àÖÎÁÑ äÁËÏÔÁ + SC | South Carolina | àÖÎÁÑ ëÁÒÏÌÉÎÁ + UT | Utah | àÔÁ (50 rows) -EOF diff --git a/src/test/locale/koi8-r/runall b/src/test/locale/koi8-r/runall index a6b81baa823..2fb6a5de41b 100755 --- a/src/test/locale/koi8-r/runall +++ b/src/test/locale/koi8-r/runall @@ -39,7 +39,7 @@ for f in char varchar text; do fi echo "Testing PgSQL: sort on $ftype type..." - destroydb testlocale >/dev/null 2>&1 + dropdb testlocale >/dev/null 2>&1 createdb testlocale || abort "createdb failed" psql -d testlocale -c "CREATE TABLE usastates (abbrev char(2), name_en char(20), name_ru $ftype);" >/dev/null 2>&1 || abort "createtable failed" psql testlocale < test-koi8.sql.in > test-koi8-$f.sql.out 2>/dev/null || abort "test query failed" @@ -48,5 +48,5 @@ done echo "Testing PgSQL: select on regexp..." psql testlocale < test-koi8-select.sql.in > test-koi8-select.sql.out 2>/dev/null || abort "select query failed" diff expected/test-koi8-select.sql.out test-koi8-select.sql.out -destroydb testlocale || abort "destroydb failed" +dropdb testlocale || abort "dropdb failed" echo "Finished." diff --git a/src/test/locale/koi8-to-win1251/expected/test-koi8-char.sql.out b/src/test/locale/koi8-to-win1251/expected/test-koi8-char.sql.out index 391af87c1dd..52d8b334a95 100644 --- a/src/test/locale/koi8-to-win1251/expected/test-koi8-char.sql.out +++ b/src/test/locale/koi8-to-win1251/expected/test-koi8-char.sql.out @@ -1,55 +1,54 @@ -abbrev|name_en |name_ru -------+--------------------+------------------------------------------------------------ -ID |Idaho |Àéäàõî -IA |Iowa |Àéîâà -AL |Alabama |Àëàáàìà -AK |Alaska |Àëÿñêà -AZ |Arizona |Àðèçîíà -AR |Arkansas |Àðêàíçàñ -WY |Wyoming |Âàéîìèíã -WA |Washington |Âàøèíãòîí -VT |Vermont |Âåðìîíò -VA |Virginia |Âèðäæèíèÿ -WI |Wisconsin |Âèñêîíñèí -DE |Delaware |Äåëàâýð -GA |Georgia |Äæîðäæèÿ -WV |West Virginia |Çàïàäíàÿ Âèðäæèíèÿ -IL |Illinois |Èëëèíîéñ -IN |Indiana |Èíäèàíà -CA |California |Êàëèôîðíèÿ -KA |Kansas |Êàíçàñ -KY |Kentucky |Êåíòóêêè -CO |Colorado |Êîëîðàäî -CT |Connecticut |Êîííåêòèêóò -LA |Louisiana |Ëóèçèàíà -MA |Massachusetts |Ìàññà÷óñåòñ -MN |Minnesota |Ìèííåñîòà -MS |Mississippi |Ìèññèñèïè -MO |Missouri |Ìèññóðè -MI |Michigan |Ìè÷èãàí -MT |Montana |Ìîíòàíà -ME |Maine |Ìýí -MD |Maryland |Ìýðèëåíä -NE |Nebraska |Íåáðàñêà -NV |Nevada |Íåâàäà -NH |New Hampshire |Íüþ-Ãýìïøèð -NJ |New Jersey |Íüþ-Äæåðñè -NY |New York |Íüþ-Éîðê -NM |New Mexico |Íüþ-Ìåêñèêî -OH |Ohio |Îãàéî -OK |Oklahoma |Îêëàõîìà -DC |Washington DC |îêðóã Êîëóìáèÿ (ñîçäàí ñïåöèàëüíî ïîä ñòîëèöó) -OR |Oregon |Îðåãîí -PA |Pennsylvania |Ïåíñèëüâàíèÿ -RI |Rhode Island |Ðîä-Àéëåíä -ND |North Dakota |Ñåâåðíàÿ Äàêîòà -NC |North Carolina |Ñåâåðíàÿ Êàðîëèíà -TN |Tennessee |Òåííåññè -TX |Texas |Òåõàñ -FL |Florida |Ôëîðèäà -SD |South Dakota |Þæíàÿ Äàêîòà -SC |South Carolina |Þæíàÿ Êàðîëèíà -UT |Utah |Þòà + abbrev | name_en | name_ru +--------+----------------------+-------------------------------------------------------------- + ID | Idaho | Àéäàõî + IA | Iowa | Àéîâà + AL | Alabama | Àëàáàìà + AK | Alaska | Àëÿñêà + AZ | Arizona | Àðèçîíà + AR | Arkansas | Àðêàíçàñ + WY | Wyoming | Âàéîìèíã + WA | Washington | Âàøèíãòîí + VT | Vermont | Âåðìîíò + VA | Virginia | Âèðäæèíèÿ + WI | Wisconsin | Âèñêîíñèí + DE | Delaware | Äåëàâýð + GA | Georgia | Äæîðäæèÿ + WV | West Virginia | Çàïàäíàÿ Âèðäæèíèÿ + IL | Illinois | Èëëèíîéñ + IN | Indiana | Èíäèàíà + CA | California | Êàëèôîðíèÿ + KA | Kansas | Êàíçàñ + KY | Kentucky | Êåíòóêêè + CO | Colorado | Êîëîðàäî + CT | Connecticut | Êîííåêòèêóò + LA | Louisiana | Ëóèçèàíà + MA | Massachusetts | Ìàññà÷óñåòñ + MN | Minnesota | Ìèííåñîòà + MS | Mississippi | Ìèññèñèïè + MO | Missouri | Ìèññóðè + MI | Michigan | Ìè÷èãàí + MT | Montana | Ìîíòàíà + ME | Maine | Ìýí + MD | Maryland | Ìýðèëåíä + NE | Nebraska | Íåáðàñêà + NV | Nevada | Íåâàäà + NH | New Hampshire | Íüþ-Ãýìïøèð + NJ | New Jersey | Íüþ-Äæåðñè + NY | New York | Íüþ-Éîðê + NM | New Mexico | Íüþ-Ìåêñèêî + OH | Ohio | Îãàéî + OK | Oklahoma | Îêëàõîìà + DC | Washington DC | îêðóã Êîëóìáèÿ (ñîçäàí ñïåöèàëüíî ïîä ñòîëèöó) + OR | Oregon | Îðåãîí + PA | Pennsylvania | Ïåíñèëüâàíèÿ + RI | Rhode Island | Ðîä-Àéëåíä + ND | North Dakota | Ñåâåðíàÿ Äàêîòà + NC | North Carolina | Ñåâåðíàÿ Êàðîëèíà + TN | Tennessee | Òåííåññè + TX | Texas | Òåõàñ + FL | Florida | Ôëîðèäà + SD | South Dakota | Þæíàÿ Äàêîòà + SC | South Carolina | Þæíàÿ Êàðîëèíà + UT | Utah | Þòà (50 rows) -EOF diff --git a/src/test/locale/koi8-to-win1251/expected/test-koi8-select.sql.out b/src/test/locale/koi8-to-win1251/expected/test-koi8-select.sql.out index 7d73f0e47ff..0feffa2548e 100644 --- a/src/test/locale/koi8-to-win1251/expected/test-koi8-select.sql.out +++ b/src/test/locale/koi8-to-win1251/expected/test-koi8-select.sql.out @@ -1,9 +1,8 @@ -abbrev|name_en |name_ru -------+--------------------+---------------------------------------------- -OH |Ohio |Îãàéî -OK |Oklahoma |Îêëàõîìà -DC |Washington DC |îêðóã Êîëóìáèÿ (ñîçäàí ñïåöèàëüíî ïîä ñòîëèöó) -OR |Oregon |Îðåãîí + abbrev | name_en | name_ru +--------+----------------------+------------------------------------------------ + OH | Ohio | Îãàéî + OK | Oklahoma | Îêëàõîìà + DC | Washington DC | îêðóã Êîëóìáèÿ (ñîçäàí ñïåöèàëüíî ïîä ñòîëèöó) + OR | Oregon | Îðåãîí (4 rows) -EOF diff --git a/src/test/locale/koi8-to-win1251/expected/test-koi8-sort.out b/src/test/locale/koi8-to-win1251/expected/test-koi8-sort.out index 9b35ecdeaab..72242ccbfa2 100644 --- a/src/test/locale/koi8-to-win1251/expected/test-koi8-sort.out +++ b/src/test/locale/koi8-to-win1251/expected/test-koi8-sort.out @@ -2,8 +2,8 @@ Bording hoarding Vesta vesta +Öàïëÿ +Þã àëüÿíñ Àôðèêà Ñåâåð -Öàïëÿ -Þã diff --git a/src/test/locale/koi8-to-win1251/expected/test-koi8-text.sql.out b/src/test/locale/koi8-to-win1251/expected/test-koi8-text.sql.out index 7a1dad22ff3..eccc1cbfc06 100644 --- a/src/test/locale/koi8-to-win1251/expected/test-koi8-text.sql.out +++ b/src/test/locale/koi8-to-win1251/expected/test-koi8-text.sql.out @@ -1,55 +1,54 @@ -abbrev|name_en |name_ru -------+--------------------+---------------------------------------------- -ID |Idaho |Àéäàõî -IA |Iowa |Àéîâà -AL |Alabama |Àëàáàìà -AK |Alaska |Àëÿñêà -AZ |Arizona |Àðèçîíà -AR |Arkansas |Àðêàíçàñ -WY |Wyoming |Âàéîìèíã -WA |Washington |Âàøèíãòîí -VT |Vermont |Âåðìîíò -VA |Virginia |Âèðäæèíèÿ -WI |Wisconsin |Âèñêîíñèí -DE |Delaware |Äåëàâýð -GA |Georgia |Äæîðäæèÿ -WV |West Virginia |Çàïàäíàÿ Âèðäæèíèÿ -IL |Illinois |Èëëèíîéñ -IN |Indiana |Èíäèàíà -CA |California |Êàëèôîðíèÿ -KA |Kansas |Êàíçàñ -KY |Kentucky |Êåíòóêêè -CO |Colorado |Êîëîðàäî -CT |Connecticut |Êîííåêòèêóò -LA |Louisiana |Ëóèçèàíà -MA |Massachusetts |Ìàññà÷óñåòñ -MN |Minnesota |Ìèííåñîòà -MS |Mississippi |Ìèññèñèïè -MO |Missouri |Ìèññóðè -MI |Michigan |Ìè÷èãàí -MT |Montana |Ìîíòàíà -ME |Maine |Ìýí -MD |Maryland |Ìýðèëåíä -NE |Nebraska |Íåáðàñêà -NV |Nevada |Íåâàäà -NH |New Hampshire |Íüþ-Ãýìïøèð -NJ |New Jersey |Íüþ-Äæåðñè -NY |New York |Íüþ-Éîðê -NM |New Mexico |Íüþ-Ìåêñèêî -OH |Ohio |Îãàéî -OK |Oklahoma |Îêëàõîìà -DC |Washington DC |îêðóã Êîëóìáèÿ (ñîçäàí ñïåöèàëüíî ïîä ñòîëèöó) -OR |Oregon |Îðåãîí -PA |Pennsylvania |Ïåíñèëüâàíèÿ -RI |Rhode Island |Ðîä-Àéëåíä -ND |North Dakota |Ñåâåðíàÿ Äàêîòà -NC |North Carolina |Ñåâåðíàÿ Êàðîëèíà -TN |Tennessee |Òåííåññè -TX |Texas |Òåõàñ -FL |Florida |Ôëîðèäà -SD |South Dakota |Þæíàÿ Äàêîòà -SC |South Carolina |Þæíàÿ Êàðîëèíà -UT |Utah |Þòà + abbrev | name_en | name_ru +--------+----------------------+------------------------------------------------ + ID | Idaho | Àéäàõî + IA | Iowa | Àéîâà + AL | Alabama | Àëàáàìà + AK | Alaska | Àëÿñêà + AZ | Arizona | Àðèçîíà + AR | Arkansas | Àðêàíçàñ + WY | Wyoming | Âàéîìèíã + WA | Washington | Âàøèíãòîí + VT | Vermont | Âåðìîíò + VA | Virginia | Âèðäæèíèÿ + WI | Wisconsin | Âèñêîíñèí + DE | Delaware | Äåëàâýð + GA | Georgia | Äæîðäæèÿ + WV | West Virginia | Çàïàäíàÿ Âèðäæèíèÿ + IL | Illinois | Èëëèíîéñ + IN | Indiana | Èíäèàíà + CA | California | Êàëèôîðíèÿ + KA | Kansas | Êàíçàñ + KY | Kentucky | Êåíòóêêè + CO | Colorado | Êîëîðàäî + CT | Connecticut | Êîííåêòèêóò + LA | Louisiana | Ëóèçèàíà + MA | Massachusetts | Ìàññà÷óñåòñ + MN | Minnesota | Ìèííåñîòà + MS | Mississippi | Ìèññèñèïè + MO | Missouri | Ìèññóðè + MI | Michigan | Ìè÷èãàí + MT | Montana | Ìîíòàíà + ME | Maine | Ìýí + MD | Maryland | Ìýðèëåíä + NE | Nebraska | Íåáðàñêà + NV | Nevada | Íåâàäà + NH | New Hampshire | Íüþ-Ãýìïøèð + NJ | New Jersey | Íüþ-Äæåðñè + NY | New York | Íüþ-Éîðê + NM | New Mexico | Íüþ-Ìåêñèêî + OH | Ohio | Îãàéî + OK | Oklahoma | Îêëàõîìà + DC | Washington DC | îêðóã Êîëóìáèÿ (ñîçäàí ñïåöèàëüíî ïîä ñòîëèöó) + OR | Oregon | Îðåãîí + PA | Pennsylvania | Ïåíñèëüâàíèÿ + RI | Rhode Island | Ðîä-Àéëåíä + ND | North Dakota | Ñåâåðíàÿ Äàêîòà + NC | North Carolina | Ñåâåðíàÿ Êàðîëèíà + TN | Tennessee | Òåííåññè + TX | Texas | Òåõàñ + FL | Florida | Ôëîðèäà + SD | South Dakota | Þæíàÿ Äàêîòà + SC | South Carolina | Þæíàÿ Êàðîëèíà + UT | Utah | Þòà (50 rows) -EOF diff --git a/src/test/locale/koi8-to-win1251/expected/test-koi8-varchar.sql.out b/src/test/locale/koi8-to-win1251/expected/test-koi8-varchar.sql.out index 7a1dad22ff3..eccc1cbfc06 100644 --- a/src/test/locale/koi8-to-win1251/expected/test-koi8-varchar.sql.out +++ b/src/test/locale/koi8-to-win1251/expected/test-koi8-varchar.sql.out @@ -1,55 +1,54 @@ -abbrev|name_en |name_ru -------+--------------------+---------------------------------------------- -ID |Idaho |Àéäàõî -IA |Iowa |Àéîâà -AL |Alabama |Àëàáàìà -AK |Alaska |Àëÿñêà -AZ |Arizona |Àðèçîíà -AR |Arkansas |Àðêàíçàñ -WY |Wyoming |Âàéîìèíã -WA |Washington |Âàøèíãòîí -VT |Vermont |Âåðìîíò -VA |Virginia |Âèðäæèíèÿ -WI |Wisconsin |Âèñêîíñèí -DE |Delaware |Äåëàâýð -GA |Georgia |Äæîðäæèÿ -WV |West Virginia |Çàïàäíàÿ Âèðäæèíèÿ -IL |Illinois |Èëëèíîéñ -IN |Indiana |Èíäèàíà -CA |California |Êàëèôîðíèÿ -KA |Kansas |Êàíçàñ -KY |Kentucky |Êåíòóêêè -CO |Colorado |Êîëîðàäî -CT |Connecticut |Êîííåêòèêóò -LA |Louisiana |Ëóèçèàíà -MA |Massachusetts |Ìàññà÷óñåòñ -MN |Minnesota |Ìèííåñîòà -MS |Mississippi |Ìèññèñèïè -MO |Missouri |Ìèññóðè -MI |Michigan |Ìè÷èãàí -MT |Montana |Ìîíòàíà -ME |Maine |Ìýí -MD |Maryland |Ìýðèëåíä -NE |Nebraska |Íåáðàñêà -NV |Nevada |Íåâàäà -NH |New Hampshire |Íüþ-Ãýìïøèð -NJ |New Jersey |Íüþ-Äæåðñè -NY |New York |Íüþ-Éîðê -NM |New Mexico |Íüþ-Ìåêñèêî -OH |Ohio |Îãàéî -OK |Oklahoma |Îêëàõîìà -DC |Washington DC |îêðóã Êîëóìáèÿ (ñîçäàí ñïåöèàëüíî ïîä ñòîëèöó) -OR |Oregon |Îðåãîí -PA |Pennsylvania |Ïåíñèëüâàíèÿ -RI |Rhode Island |Ðîä-Àéëåíä -ND |North Dakota |Ñåâåðíàÿ Äàêîòà -NC |North Carolina |Ñåâåðíàÿ Êàðîëèíà -TN |Tennessee |Òåííåññè -TX |Texas |Òåõàñ -FL |Florida |Ôëîðèäà -SD |South Dakota |Þæíàÿ Äàêîòà -SC |South Carolina |Þæíàÿ Êàðîëèíà -UT |Utah |Þòà + abbrev | name_en | name_ru +--------+----------------------+------------------------------------------------ + ID | Idaho | Àéäàõî + IA | Iowa | Àéîâà + AL | Alabama | Àëàáàìà + AK | Alaska | Àëÿñêà + AZ | Arizona | Àðèçîíà + AR | Arkansas | Àðêàíçàñ + WY | Wyoming | Âàéîìèíã + WA | Washington | Âàøèíãòîí + VT | Vermont | Âåðìîíò + VA | Virginia | Âèðäæèíèÿ + WI | Wisconsin | Âèñêîíñèí + DE | Delaware | Äåëàâýð + GA | Georgia | Äæîðäæèÿ + WV | West Virginia | Çàïàäíàÿ Âèðäæèíèÿ + IL | Illinois | Èëëèíîéñ + IN | Indiana | Èíäèàíà + CA | California | Êàëèôîðíèÿ + KA | Kansas | Êàíçàñ + KY | Kentucky | Êåíòóêêè + CO | Colorado | Êîëîðàäî + CT | Connecticut | Êîííåêòèêóò + LA | Louisiana | Ëóèçèàíà + MA | Massachusetts | Ìàññà÷óñåòñ + MN | Minnesota | Ìèííåñîòà + MS | Mississippi | Ìèññèñèïè + MO | Missouri | Ìèññóðè + MI | Michigan | Ìè÷èãàí + MT | Montana | Ìîíòàíà + ME | Maine | Ìýí + MD | Maryland | Ìýðèëåíä + NE | Nebraska | Íåáðàñêà + NV | Nevada | Íåâàäà + NH | New Hampshire | Íüþ-Ãýìïøèð + NJ | New Jersey | Íüþ-Äæåðñè + NY | New York | Íüþ-Éîðê + NM | New Mexico | Íüþ-Ìåêñèêî + OH | Ohio | Îãàéî + OK | Oklahoma | Îêëàõîìà + DC | Washington DC | îêðóã Êîëóìáèÿ (ñîçäàí ñïåöèàëüíî ïîä ñòîëèöó) + OR | Oregon | Îðåãîí + PA | Pennsylvania | Ïåíñèëüâàíèÿ + RI | Rhode Island | Ðîä-Àéëåíä + ND | North Dakota | Ñåâåðíàÿ Äàêîòà + NC | North Carolina | Ñåâåðíàÿ Êàðîëèíà + TN | Tennessee | Òåííåññè + TX | Texas | Òåõàñ + FL | Florida | Ôëîðèäà + SD | South Dakota | Þæíàÿ Äàêîòà + SC | South Carolina | Þæíàÿ Êàðîëèíà + UT | Utah | Þòà (50 rows) -EOF diff --git a/src/test/locale/koi8-to-win1251/runall b/src/test/locale/koi8-to-win1251/runall index e0fef2f0b94..a9aeadfa47b 100755 --- a/src/test/locale/koi8-to-win1251/runall +++ b/src/test/locale/koi8-to-win1251/runall @@ -36,7 +36,7 @@ for f in char varchar text; do fi echo "Testing PgSQL: sort on $ftype type..." - destroydb testlocale >/dev/null 2>&1 + dropdb testlocale >/dev/null 2>&1 createdb testlocale || abort "createdb failed" psql -d testlocale -c "CREATE TABLE usastates (abbrev char(2), name_en char(20), name_ru $ftype);" >/dev/null 2>&1 || abort "createtable failed" psql testlocale < test-koi8.sql.in > test-koi8-$f.sql.out 2>/dev/null || abort "test query failed" @@ -45,5 +45,5 @@ done echo "Testing PgSQL: select on regexp..." psql testlocale < test-koi8-select.sql.in > test-koi8-select.sql.out 2>/dev/null || abort "select query failed" diff expected/test-koi8-select.sql.out test-koi8-select.sql.out -destroydb testlocale || abort "destroydb failed" +dropdb testlocale || abort "dropdb failed" echo "Finished." diff --git a/src/test/regress/expected/abstime-solaris-1947.out b/src/test/regress/expected/abstime-solaris-1947.out new file mode 100644 index 00000000000..cca2519e91e --- /dev/null +++ b/src/test/regress/expected/abstime-solaris-1947.out @@ -0,0 +1,175 @@ +-- +-- ABSTIME +-- testing built-in time type abstime +-- uses reltime and tinterval +-- +-- +-- timezones may vary based not only on location but the operating +-- system. the main correctness issue is that the OS may not get +-- daylight savings time right for times prior to Unix epoch (jan 1 1970). +-- +CREATE TABLE ABSTIME_TBL (f1 abstime); +INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21'); +-- was INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'now'): +INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'Mon May 1 00:30:30 1995'); +INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'epoch'); +INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'current'); +INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'infinity'); +INSERT INTO ABSTIME_TBL (f1) VALUES (abstime '-infinity'); +INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'May 10, 1947 23:59:12'); +-- what happens if we specify slightly misformatted abstime? +INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00'); +ERROR: Bad abstime external representation 'Feb 35, 1946 10:00:00' +INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10'); +ERROR: Bad abstime external representation 'Feb 28, 1984 25:08:10' +-- badly formatted abstimes: these should result in invalid abstimes +INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format'); +ERROR: Bad abstime external representation 'bad date format' +INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843'); +-- test abstime operators +SELECT '' AS eight, ABSTIME_TBL.*; + eight | f1 +-------+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | Mon May 01 00:30:30 1995 PDT + | epoch + | current + | infinity + | -infinity + | Sat May 10 23:59:12 1947 PDT + | invalid +(8 rows) + +SELECT '' AS six, ABSTIME_TBL.* + WHERE ABSTIME_TBL.f1 < abstime 'Jun 30, 2001'; + six | f1 +-----+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | Mon May 01 00:30:30 1995 PDT + | epoch + | current + | -infinity + | Sat May 10 23:59:12 1947 PDT +(6 rows) + +SELECT '' AS six, ABSTIME_TBL.* + WHERE ABSTIME_TBL.f1 > abstime '-infinity'; + six | f1 +-----+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | Mon May 01 00:30:30 1995 PDT + | epoch + | current + | infinity + | Sat May 10 23:59:12 1947 PDT +(6 rows) + +SELECT '' AS six, ABSTIME_TBL.* + WHERE abstime 'May 10, 1947 23:59:12' <> ABSTIME_TBL.f1; + six | f1 +-----+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | Mon May 01 00:30:30 1995 PDT + | epoch + | current + | infinity + | -infinity +(6 rows) + +SELECT '' AS one, ABSTIME_TBL.* + WHERE abstime 'current' = ABSTIME_TBL.f1; + one | f1 +-----+--------- + | current +(1 row) + +SELECT '' AS three, ABSTIME_TBL.* + WHERE abstime 'epoch' >= ABSTIME_TBL.f1; + three | f1 +-------+------------------------------ + | epoch + | -infinity + | Sat May 10 23:59:12 1947 PDT +(3 rows) + +SELECT '' AS four, ABSTIME_TBL.* + WHERE ABSTIME_TBL.f1 <= abstime 'Jan 14, 1973 03:14:21'; + four | f1 +------+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | epoch + | -infinity + | Sat May 10 23:59:12 1947 PDT +(4 rows) + +SELECT '' AS four, ABSTIME_TBL.* + WHERE ABSTIME_TBL.f1 <?> + tinterval '["Apr 1 1950 00:00:00" "Dec 30 1999 23:00:00"]'; + four | f1 +------+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | Mon May 01 00:30:30 1995 PDT + | epoch +(3 rows) + +-- these four queries should return the same answer +-- the "infinity" and "-infinity" tuples in ABSTIME_TBL cannot be added and +-- therefore, should not show up in the results. +SELECT '' AS three, ABSTIME_TBL.* + WHERE (ABSTIME_TBL.f1 + reltime '@ 3 year') -- +3 years + < abstime 'Jan 14 14:00:00 1977'; + three | f1 +-------+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | epoch + | Sat May 10 23:59:12 1947 PDT +(3 rows) + +SELECT '' AS three, ABSTIME_TBL.* + WHERE (ABSTIME_TBL.f1 + reltime '@ 3 year ago') -- -3 years + < abstime 'Jan 14 14:00:00 1971'; + three | f1 +-------+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | epoch + | Sat May 10 23:59:12 1947 PDT +(3 rows) + +SELECT '' AS three, ABSTIME_TBL.* + WHERE (ABSTIME_TBL.f1 - reltime '@ 3 year') -- -(+3) years + < abstime 'Jan 14 14:00:00 1971'; + three | f1 +-------+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | epoch + | Sat May 10 23:59:12 1947 PDT +(3 rows) + +SELECT '' AS three, ABSTIME_TBL.* + WHERE (ABSTIME_TBL.f1 - reltime '@ 3 year ago') -- -(-3) years + < abstime 'Jan 14 14:00:00 1977'; + three | f1 +-------+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | epoch + | Sat May 10 23:59:12 1947 PDT +(3 rows) + +SELECT '' AS ten, ABSTIME_TBL.f1 AS abstime, RELTIME_TBL.f1 AS reltime + WHERE (ABSTIME_TBL.f1 + RELTIME_TBL.f1) + < abstime 'Jan 14 14:00:00 1971' + ORDER BY abstime, reltime; + ten | abstime | reltime +-----+------------------------------+--------------- + | Sat May 10 23:59:12 1947 PDT | @ 14 secs ago + | Sat May 10 23:59:12 1947 PDT | @ 1 min + | Sat May 10 23:59:12 1947 PDT | @ 5 hours + | Sat May 10 23:59:12 1947 PDT | @ 10 days + | Sat May 10 23:59:12 1947 PDT | @ 3 mons + | epoch | @ 14 secs ago + | epoch | @ 1 min + | epoch | @ 5 hours + | epoch | @ 10 days + | epoch | @ 3 mons +(10 rows) + diff --git a/src/test/regress/expected/geometry-solaris-precision.out b/src/test/regress/expected/geometry-solaris-precision.out new file mode 100644 index 00000000000..a389299d045 --- /dev/null +++ b/src/test/regress/expected/geometry-solaris-precision.out @@ -0,0 +1,532 @@ +-- +-- GEOMETRY +-- +-- +-- Points +-- +SELECT '' AS four, center(f1) AS center + FROM BOX_TBL; + four | center +------+--------- + | (1,1) + | (2,2) + | (2.5,3) + | (3,3) +(4 rows) + +SELECT '' AS four, (@@ f1) AS center + FROM BOX_TBL; + four | center +------+--------- + | (1,1) + | (2,2) + | (2.5,3) + | (3,3) +(4 rows) + +SELECT '' AS six, point(f1) AS center + FROM CIRCLE_TBL; + six | center +-----+----------- + | (0,0) + | (1,2) + | (1,3) + | (1,2) + | (100,200) + | (100,0) +(6 rows) + +SELECT '' AS six, (@@ f1) AS center + FROM CIRCLE_TBL; + six | center +-----+----------- + | (0,0) + | (1,2) + | (1,3) + | (1,2) + | (100,200) + | (100,0) +(6 rows) + +SELECT '' AS two, (@@ f1) AS center + FROM POLYGON_TBL + WHERE (# f1) > 2; + two | center +-----+------------------------------------- + | (1.33333333333333,1.33333333333333) + | (2.33333333333333,1.33333333333333) +(2 rows) + +-- "is horizontal" function +SELECT '' AS two, p1.f1 + FROM POINT_TBL p1 + WHERE ishorizontal(p1.f1, point '(0,0)'); + two | f1 +-----+--------- + | (0,0) + | (-10,0) +(2 rows) + +-- "is horizontal" operator +SELECT '' AS two, p1.f1 + FROM POINT_TBL p1 + WHERE p1.f1 ?- point '(0,0)'; + two | f1 +-----+--------- + | (0,0) + | (-10,0) +(2 rows) + +-- "is vertical" function +SELECT '' AS one, p1.f1 + FROM POINT_TBL p1 + WHERE isvertical(p1.f1, point '(5.1,34.5)'); + one | f1 +-----+------------ + | (5.1,34.5) +(1 row) + +-- "is vertical" operator +SELECT '' AS one, p1.f1 + FROM POINT_TBL p1 + WHERE p1.f1 ?| point '(5.1,34.5)'; + one | f1 +-----+------------ + | (5.1,34.5) +(1 row) + +-- +-- Line segments +-- +-- intersection +SELECT '' AS count, p.f1, l.s, l.s # p.f1 AS intersection + FROM LSEG_TBL l, POINT_TBL p; +ERROR: Unable to identify an operator '#' for types 'lseg' and 'point' + You will have to retype this query using an explicit cast +-- closest point +SELECT '' AS thirty, p.f1, l.s, p.f1 ## l.s AS closest + FROM LSEG_TBL l, POINT_TBL p; + thirty | f1 | s | closest +--------+------------+-------------------------------+--------------------------------------- + | (0,0) | [(1,2),(3,4)] | (1,2) + | (-10,0) | [(1,2),(3,4)] | (1,2) + | (-3,4) | [(1,2),(3,4)] | (1,2) + | (5.1,34.5) | [(1,2),(3,4)] | (3,4) + | (-5,-12) | [(1,2),(3,4)] | (1,2) + | (10,10) | [(1,2),(3,4)] | (3,4) + | (0,0) | [(0,0),(6,6)] | (-0,0) + | (-10,0) | [(0,0),(6,6)] | (0,0) + | (-3,4) | [(0,0),(6,6)] | (0.5,0.5) + | (5.1,34.5) | [(0,0),(6,6)] | (6,6) + | (-5,-12) | [(0,0),(6,6)] | (0,0) + | (10,10) | [(0,0),(6,6)] | (6,6) + | (0,0) | [(10,-10),(-3,-4)] | (-2.04878048780488,-4.4390243902439) + | (-10,0) | [(10,-10),(-3,-4)] | (-3,-4) + | (-3,4) | [(10,-10),(-3,-4)] | (-3,-4) + | (5.1,34.5) | [(10,-10),(-3,-4)] | (-3,-4) + | (-5,-12) | [(10,-10),(-3,-4)] | (-1.60487804878049,-4.64390243902439) + | (10,10) | [(10,-10),(-3,-4)] | (2.39024390243902,-6.48780487804878) + | (0,0) | [(-1000000,200),(300000,-40)] | (0.0028402365895872,15.384614860264) + | (-10,0) | [(-1000000,200),(300000,-40)] | (-9.99715942258202,15.3864610140472) + | (-3,4) | [(-1000000,200),(300000,-40)] | (-2.99789812267519,15.3851688427303) + | (5.1,34.5) | [(-1000000,200),(300000,-40)] | (5.09647083221496,15.3836744976925) + | (-5,-12) | [(-1000000,200),(300000,-40)] | (-4.99494420845634,15.3855375281616) + | (10,10) | [(-1000000,200),(300000,-40)] | (10.000993741978,15.3827690473092) + | (0,0) | [(11,22),(33,44)] | (11,22) + | (-10,0) | [(11,22),(33,44)] | (11,22) + | (-3,4) | [(11,22),(33,44)] | (11,22) + | (5.1,34.5) | [(11,22),(33,44)] | (14.3,25.3) + | (-5,-12) | [(11,22),(33,44)] | (11,22) + | (10,10) | [(11,22),(33,44)] | (11,22) +(30 rows) + +-- +-- Lines +-- +-- +-- Boxes +-- +SELECT '' as six, box(f1) AS box FROM CIRCLE_TBL; + six | box +-----+---------------------------------------------------------------------------- + | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) + | (71.7106781186547,72.7106781186547),(-69.7106781186547,-68.7106781186547) + | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932737) + | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559642) + | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) + | (170.710678118655,70.7106781186547),(29.2893218813453,-70.7106781186547) +(6 rows) + +-- translation +SELECT '' AS twentyfour, b.f1 + p.f1 AS translation + FROM BOX_TBL b, POINT_TBL p; + twentyfour | translation +------------+------------------------- + | (2,2),(0,0) + | (3,3),(1,1) + | (2.5,3.5),(2.5,2.5) + | (3,3),(3,3) + | (-8,2),(-10,0) + | (-7,3),(-9,1) + | (-7.5,3.5),(-7.5,2.5) + | (-7,3),(-7,3) + | (-1,6),(-3,4) + | (0,7),(-2,5) + | (-0.5,7.5),(-0.5,6.5) + | (0,7),(0,7) + | (7.1,36.5),(5.1,34.5) + | (8.1,37.5),(6.1,35.5) + | (7.6,38),(7.6,37) + | (8.1,37.5),(8.1,37.5) + | (-3,-10),(-5,-12) + | (-2,-9),(-4,-11) + | (-2.5,-8.5),(-2.5,-9.5) + | (-2,-9),(-2,-9) + | (12,12),(10,10) + | (13,13),(11,11) + | (12.5,13.5),(12.5,12.5) + | (13,13),(13,13) +(24 rows) + +SELECT '' AS twentyfour, b.f1 - p.f1 AS translation + FROM BOX_TBL b, POINT_TBL p; + twentyfour | translation +------------+--------------------------- + | (2,2),(0,0) + | (3,3),(1,1) + | (2.5,3.5),(2.5,2.5) + | (3,3),(3,3) + | (12,2),(10,0) + | (13,3),(11,1) + | (12.5,3.5),(12.5,2.5) + | (13,3),(13,3) + | (5,-2),(3,-4) + | (6,-1),(4,-3) + | (5.5,-0.5),(5.5,-1.5) + | (6,-1),(6,-1) + | (-3.1,-32.5),(-5.1,-34.5) + | (-2.1,-31.5),(-4.1,-33.5) + | (-2.6,-31),(-2.6,-32) + | (-2.1,-31.5),(-2.1,-31.5) + | (7,14),(5,12) + | (8,15),(6,13) + | (7.5,15.5),(7.5,14.5) + | (8,15),(8,15) + | (-8,-8),(-10,-10) + | (-7,-7),(-9,-9) + | (-7.5,-6.5),(-7.5,-7.5) + | (-7,-7),(-7,-7) +(24 rows) + +-- scaling and rotation +SELECT '' AS twentyfour, b.f1 * p.f1 AS rotation + FROM BOX_TBL b, POINT_TBL p; + twentyfour | rotation +------------+----------------------------- + | (0,0),(0,0) + | (0,0),(0,0) + | (0,0),(0,0) + | (0,0),(0,0) + | (-0,0),(-20,-20) + | (-10,-10),(-30,-30) + | (-25,-25),(-25,-35) + | (-30,-30),(-30,-30) + | (-0,2),(-14,0) + | (-7,3),(-21,1) + | (-17.5,2.5),(-21.5,-0.5) + | (-21,3),(-21,3) + | (0,79.2),(-58.8,0) + | (-29.4,118.8),(-88.2,39.6) + | (-73.5,104.1),(-108,99) + | (-88.2,118.8),(-88.2,118.8) + | (14,-0),(0,-34) + | (21,-17),(7,-51) + | (29.5,-42.5),(17.5,-47.5) + | (21,-51),(21,-51) + | (0,40),(0,0) + | (0,60),(0,20) + | (0,60),(-10,50) + | (0,60),(0,60) +(24 rows) + +SELECT '' AS twenty, b.f1 / p.f1 AS rotation + FROM BOX_TBL b, POINT_TBL p + WHERE (p.f1 <-> point '(0,0)') >= 1; + twenty | rotation +--------+----------------------------------------------------------------------------------- + | (0,-0),(-0.2,-0.2) + | (-0.1,-0.1),(-0.3,-0.3) + | (-0.25,-0.25),(-0.25,-0.35) + | (-0.3,-0.3),(-0.3,-0.3) + | (0.08,-0),(0,-0.56) + | (0.12,-0.28),(0.04,-0.84) + | (0.26,-0.7),(0.1,-0.82) + | (0.12,-0.84),(0.12,-0.84) + | (0.0651176557643925,0),(0,-0.0483449262493217) + | (0.0976764836465887,-0.0241724631246608),(0.0325588278821962,-0.0725173893739825) + | (0.109762715208919,-0.0562379754328844),(0.0813970697054906,-0.0604311578116521) + | (0.0976764836465887,-0.0725173893739825),(0.0976764836465887,-0.0725173893739825) + | (-0,0.0828402366863905),(-0.201183431952663,0) + | (-0.100591715976331,0.124260355029586),(-0.301775147928994,0.0414201183431953) + | (-0.251479289940828,0.103550295857988),(-0.322485207100592,0.0739644970414201) + | (-0.301775147928994,0.124260355029586),(-0.301775147928994,0.124260355029586) + | (0.2,0),(0,0) + | (0.3,0),(0.1,0) + | (0.3,0.05),(0.25,0) + | (0.3,0),(0.3,0) +(20 rows) + +-- +-- Paths +-- +SET geqo TO 'off'; +SELECT '' AS eight, points(f1) AS npoints, f1 AS path FROM PATH_TBL; + eight | npoints | path +-------+---------+--------------------------- + | 2 | [(1,2),(3,4)] + | 2 | ((1,2),(3,4)) + | 4 | [(0,0),(3,0),(4,5),(1,6)] + | 2 | ((1,2),(3,4)) + | 2 | ((1,2),(3,4)) + | 2 | [(1,2),(3,4)] + | 2 | [(11,12),(13,14)] + | 2 | ((11,12),(13,14)) +(8 rows) + +SELECT '' AS four, path(f1) FROM POLYGON_TBL; + four | path +------+--------------------- + | ((2,0),(2,4),(0,0)) + | ((3,1),(3,3),(1,0)) + | ((0,0)) + | ((0,1),(0,1)) +(4 rows) + +-- translation +SELECT '' AS eight, p1.f1 + point '(10,10)' AS dist_add + FROM PATH_TBL p1; + eight | dist_add +-------+----------------------------------- + | [(11,12),(13,14)] + | ((11,12),(13,14)) + | [(10,10),(13,10),(14,15),(11,16)] + | ((11,12),(13,14)) + | ((11,12),(13,14)) + | [(11,12),(13,14)] + | [(21,22),(23,24)] + | ((21,22),(23,24)) +(8 rows) + +-- scaling and rotation +SELECT '' AS eight, p1.f1 * point '(2,-1)' AS dist_mul + FROM PATH_TBL p1; + eight | dist_mul +-------+------------------------------ + | [(4,3),(10,5)] + | ((4,3),(10,5)) + | [(0,0),(6,-3),(13,6),(8,11)] + | ((4,3),(10,5)) + | ((4,3),(10,5)) + | [(4,3),(10,5)] + | [(34,13),(40,15)] + | ((34,13),(40,15)) +(8 rows) + +RESET geqo; +-- +-- Polygons +-- +-- containment +SELECT '' AS twentyfour, p.f1, poly.f1, poly.f1 ~ p.f1 AS contains + FROM POLYGON_TBL poly, POINT_TBL p; + twentyfour | f1 | f1 | contains +------------+------------+---------------------+---------- + | (0,0) | ((2,0),(2,4),(0,0)) | t + | (-10,0) | ((2,0),(2,4),(0,0)) | f + | (-3,4) | ((2,0),(2,4),(0,0)) | f + | (5.1,34.5) | ((2,0),(2,4),(0,0)) | f + | (-5,-12) | ((2,0),(2,4),(0,0)) | f + | (10,10) | ((2,0),(2,4),(0,0)) | f + | (0,0) | ((3,1),(3,3),(1,0)) | f + | (-10,0) | ((3,1),(3,3),(1,0)) | f + | (-3,4) | ((3,1),(3,3),(1,0)) | f + | (5.1,34.5) | ((3,1),(3,3),(1,0)) | f + | (-5,-12) | ((3,1),(3,3),(1,0)) | f + | (10,10) | ((3,1),(3,3),(1,0)) | f + | (0,0) | ((0,0)) | t + | (-10,0) | ((0,0)) | f + | (-3,4) | ((0,0)) | f + | (5.1,34.5) | ((0,0)) | f + | (-5,-12) | ((0,0)) | f + | (10,10) | ((0,0)) | f + | (0,0) | ((0,1),(0,1)) | f + | (-10,0) | ((0,1),(0,1)) | f + | (-3,4) | ((0,1),(0,1)) | f + | (5.1,34.5) | ((0,1),(0,1)) | f + | (-5,-12) | ((0,1),(0,1)) | f + | (10,10) | ((0,1),(0,1)) | f +(24 rows) + +SELECT '' AS twentyfour, p.f1, poly.f1, p.f1 @ poly.f1 AS contained + FROM POLYGON_TBL poly, POINT_TBL p; + twentyfour | f1 | f1 | contained +------------+------------+---------------------+----------- + | (0,0) | ((2,0),(2,4),(0,0)) | t + | (-10,0) | ((2,0),(2,4),(0,0)) | f + | (-3,4) | ((2,0),(2,4),(0,0)) | f + | (5.1,34.5) | ((2,0),(2,4),(0,0)) | f + | (-5,-12) | ((2,0),(2,4),(0,0)) | f + | (10,10) | ((2,0),(2,4),(0,0)) | f + | (0,0) | ((3,1),(3,3),(1,0)) | f + | (-10,0) | ((3,1),(3,3),(1,0)) | f + | (-3,4) | ((3,1),(3,3),(1,0)) | f + | (5.1,34.5) | ((3,1),(3,3),(1,0)) | f + | (-5,-12) | ((3,1),(3,3),(1,0)) | f + | (10,10) | ((3,1),(3,3),(1,0)) | f + | (0,0) | ((0,0)) | t + | (-10,0) | ((0,0)) | f + | (-3,4) | ((0,0)) | f + | (5.1,34.5) | ((0,0)) | f + | (-5,-12) | ((0,0)) | f + | (10,10) | ((0,0)) | f + | (0,0) | ((0,1),(0,1)) | f + | (-10,0) | ((0,1),(0,1)) | f + | (-3,4) | ((0,1),(0,1)) | f + | (5.1,34.5) | ((0,1),(0,1)) | f + | (-5,-12) | ((0,1),(0,1)) | f + | (10,10) | ((0,1),(0,1)) | f +(24 rows) + +SELECT '' AS four, points(f1) AS npoints, f1 AS polygon + FROM POLYGON_TBL; + four | npoints | polygon +------+---------+--------------------- + | 3 | ((2,0),(2,4),(0,0)) + | 3 | ((3,1),(3,3),(1,0)) + | 1 | ((0,0)) + | 2 | ((0,1),(0,1)) +(4 rows) + +SELECT '' AS four, polygon(f1) + FROM BOX_TBL; + four | polygon +------+------------------------------------------- + | ((0,0),(0,2),(2,2),(2,0)) + | ((1,1),(1,3),(3,3),(3,1)) + | ((2.5,2.5),(2.5,3.5),(2.5,3.5),(2.5,2.5)) + | ((3,3),(3,3),(3,3),(3,3)) +(4 rows) + +SELECT '' AS four, polygon(f1) + FROM PATH_TBL WHERE isclosed(f1); + four | polygon +------+------------------- + | ((1,2),(3,4)) + | ((1,2),(3,4)) + | ((1,2),(3,4)) + | ((11,12),(13,14)) +(4 rows) + +SELECT '' AS four, f1 AS open_path, polygon( pclose(f1)) AS polygon + FROM PATH_TBL + WHERE isopen(f1); + four | open_path | polygon +------+---------------------------+--------------------------- + | [(1,2),(3,4)] | ((1,2),(3,4)) + | [(0,0),(3,0),(4,5),(1,6)] | ((0,0),(3,0),(4,5),(1,6)) + | [(1,2),(3,4)] | ((1,2),(3,4)) + | [(11,12),(13,14)] | ((11,12),(13,14)) +(4 rows) + +-- convert circles to polygons using the default number of points +SELECT '' AS six, polygon(f1) + FROM CIRCLE_TBL; + six | polygon +-----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + | ((-3,0),(-2.59807621135076,1.50000000000442),(-1.49999999999116,2.59807621135842),(1.53102359017709e-11,3),(1.50000000001768,2.59807621134311),(2.59807621136607,1.4999999999779),(3,-3.06204718035418e-11),(2.59807621133545,-1.50000000003094),(1.49999999996464,-2.59807621137373),(-4.59307077053127e-11,-3),(-1.5000000000442,-2.5980762113278),(-2.59807621138138,-1.49999999995138)) + | ((-99,2),(-85.6025403783588,52.0000000001473),(-48.9999999997054,88.602540378614),(1.00000000051034,102),(51.0000000005893,88.6025403781036),(87.6025403788692,51.9999999992634),(101,1.99999999897932),(87.6025403778485,-48.0000000010313),(50.9999999988214,-84.6025403791243),(0.999999998468976,-98),(-49.0000000014732,-84.6025403775933),(-85.6025403793795,-47.9999999983795)) + | ((-4,3),(-3.33012701891794,5.50000000000737),(-1.49999999998527,7.3301270189307),(1.00000000002552,8),(3.50000000002946,7.33012701890518),(5.33012701894346,5.49999999996317),(6,2.99999999994897),(5.33012701889242,0.499999999948437),(3.49999999994107,-1.33012701895622),(0.999999999923449,-2),(-1.50000000007366,-1.33012701887967),(-3.33012701896897,0.500000000081028)) + | ((-2,2),(-1.59807621135076,3.50000000000442),(-0.499999999991161,4.59807621135842),(1.00000000001531,5),(2.50000000001768,4.59807621134311),(3.59807621136607,3.4999999999779),(4,1.99999999996938),(3.59807621133545,0.499999999969062),(2.49999999996464,-0.598076211373729),(0.999999999954069,-1),(-0.500000000044197,-0.598076211327799),(-1.59807621138138,0.500000000048616)) + | ((90,200),(91.3397459621641,205.000000000015),(95.0000000000295,208.660254037861),(100.000000000051,210),(105.000000000059,208.66025403781),(108.660254037887,204.999999999926),(110,199.999999999898),(108.660254037785,194.999999999897),(104.999999999882,191.339745962088),(99.9999999998469,190),(94.9999999998527,191.339745962241),(91.3397459620621,195.000000000162)) + | ((0,0),(13.3974596216412,50.0000000001473),(50.0000000002946,86.602540378614),(100.00000000051,100),(150.000000000589,86.6025403781036),(186.602540378869,49.9999999992634),(200,-1.02068239345139e-09),(186.602540377848,-50.0000000010313),(149.999999998821,-86.6025403791243),(99.999999998469,-100),(49.9999999985268,-86.6025403775933),(13.3974596206205,-49.9999999983795)) +(6 rows) + +-- convert the circle to an 8-point polygon +SELECT '' AS six, polygon(8, f1) + FROM CIRCLE_TBL; + six | polygon +-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + | ((-3,0),(-2.12132034355423,2.12132034356506),(1.53102359017709e-11,3),(2.12132034357588,2.1213203435434),(3,-3.06204718035418e-11),(2.12132034353258,-2.12132034358671),(-4.59307077053127e-11,-3),(-2.12132034359753,-2.12132034352175)) + | ((-99,2),(-69.7106781184743,72.7106781188352),(1.00000000051034,102),(71.710678119196,72.7106781181135),(101,1.99999999897932),(71.7106781177526,-68.7106781195569),(0.999999998468976,-98),(-69.7106781199178,-68.7106781173917)) + | ((-4,3),(-2.53553390592372,6.53553390594176),(1.00000000002552,8),(4.5355339059598,6.53553390590567),(6,2.99999999994897),(4.53553390588763,-0.535533905977846),(0.999999999923449,-2),(-2.53553390599589,-0.535533905869586)) + | ((-2,2),(-1.12132034355423,4.12132034356506),(1.00000000001531,5),(3.12132034357588,4.1213203435434),(4,1.99999999996938),(3.12132034353258,-0.121320343586707),(0.999999999954069,-1),(-1.12132034359753,-0.121320343521752)) + | ((90,200),(92.9289321881526,207.071067811884),(100.000000000051,210),(107.07106781192,207.071067811811),(110,199.999999999898),(107.071067811775,192.928932188044),(99.9999999998469,190),(92.9289321880082,192.928932188261)) + | ((0,0),(29.2893218815257,70.7106781188352),(100.00000000051,100),(170.710678119196,70.7106781181135),(200,-1.02068239345139e-09),(170.710678117753,-70.7106781195569),(99.999999998469,-100),(29.2893218800822,-70.7106781173917)) +(6 rows) + +-- +-- Circles +-- +SELECT '' AS six, circle(f1, 50.0) + FROM POINT_TBL; + six | circle +-----+----------------- + | <(0,0),50> + | <(-10,0),50> + | <(-3,4),50> + | <(5.1,34.5),50> + | <(-5,-12),50> + | <(10,10),50> +(6 rows) + +SELECT '' AS four, circle(f1) + FROM BOX_TBL; + four | circle +------+------------------------- + | <(1,1),1.4142135623731> + | <(2,2),1.4142135623731> + | <(2.5,3),0.5> + | <(3,3),0> +(4 rows) + +SELECT '' AS two, circle(f1) + FROM POLYGON_TBL + WHERE (# f1) >= 3; + two | circle +-----+-------------------------------------------------------- + | <(1.33333333333333,1.33333333333333),2.04168905063636> + | <(2.33333333333333,1.33333333333333),1.47534300379185> +(2 rows) + +SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance + FROM CIRCLE_TBL c1, POINT_TBL p1 + WHERE (p1.f1 <-> c1.f1) > 0 + ORDER BY distance, circle, point using <<; + twentyfour | circle | point | distance +------------+----------------+------------+------------------ + | <(100,0),100> | (5.1,34.5) | 0.97653192697797 + | <(1,2),3> | (-3,4) | 1.47213595499958 + | <(0,0),3> | (-3,4) | 2 + | <(100,0),100> | (-3,4) | 3.07764064044152 + | <(100,0),100> | (-5,-12) | 5.68348972285122 + | <(1,3),5> | (-10,0) | 6.40175425099138 + | <(1,3),5> | (10,10) | 6.40175425099138 + | <(0,0),3> | (-10,0) | 7 + | <(1,2),3> | (-10,0) | 8.18033988749895 + | <(1,2),3> | (10,10) | 9.0415945787923 + | <(0,0),3> | (-5,-12) | 10 + | <(100,0),100> | (-10,0) | 10 + | <(0,0),3> | (10,10) | 11.142135623731 + | <(1,3),5> | (-5,-12) | 11.1554944214035 + | <(1,2),3> | (-5,-12) | 12.2315462117278 + | <(1,3),5> | (5.1,34.5) | 26.7657047773223 + | <(1,2),3> | (5.1,34.5) | 29.757594539282 + | <(0,0),3> | (5.1,34.5) | 31.8749193547455 + | <(100,200),10> | (5.1,34.5) | 180.778038568384 + | <(100,200),10> | (10,10) | 200.237960416286 + | <(100,200),10> | (-3,4) | 211.415898254845 + | <(100,200),10> | (0,0) | 213.606797749979 + | <(100,200),10> | (-10,0) | 218.254244210267 + | <(100,200),10> | (-5,-12) | 226.577682802077 +(24 rows) + diff --git a/src/test/regress/expected/horology-solaris-1947.out b/src/test/regress/expected/horology-solaris-1947.out new file mode 100644 index 00000000000..8dd6611b734 --- /dev/null +++ b/src/test/regress/expected/horology-solaris-1947.out @@ -0,0 +1,1588 @@ +-- +-- HOROLOGY +-- +-- +-- datetime, timespan arithmetic +-- +SELECT datetime '1996-03-01' - timespan '1 second' AS "Feb 29"; + Feb 29 +------------------------------ + Thu Feb 29 23:59:59 1996 PST +(1 row) + +SELECT datetime '1999-03-01' - timespan '1 second' AS "Feb 28"; + Feb 28 +------------------------------ + Sun Feb 28 23:59:59 1999 PST +(1 row) + +SELECT datetime '2000-03-01' - timespan '1 second' AS "Feb 29"; + Feb 29 +------------------------------ + Tue Feb 29 23:59:59 2000 PST +(1 row) + +SELECT datetime '1999-12-01' + timespan '1 month - 1 second' AS "Dec 31"; + Dec 31 +------------------------------ + Fri Dec 31 23:59:59 1999 PST +(1 row) + +CREATE TABLE TEMP_DATETIME (f1 datetime); +-- get some candidate input values +INSERT INTO TEMP_DATETIME (f1) + SELECT d1 FROM DATETIME_TBL + WHERE d1 BETWEEN '13-jun-1957' AND '1-jan-1997' + OR d1 BETWEEN '1-jan-1999' AND '1-jan-2010'; +SELECT '' AS ten, f1 AS datetime + FROM TEMP_DATETIME + ORDER BY datetime; + ten | datetime +-----+------------------------------ + | epoch + | Wed Feb 28 17:32:01 1996 PST + | Thu Feb 29 17:32:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST + | Mon Dec 30 17:32:01 1996 PST + | Tue Dec 31 17:32:01 1996 PST + | Fri Dec 31 17:32:01 1999 PST + | Sat Jan 01 17:32:01 2000 PST + | Sun Dec 31 17:32:01 2000 PST + | Mon Jan 01 17:32:01 2001 PST +(10 rows) + +SELECT '' AS hundred, d.f1 AS datetime, t.f1 AS timespan, d.f1 + t.f1 AS plus + FROM TEMP_DATETIME d, TIMESPAN_TBL t + ORDER BY plus, datetime, timespan; + hundred | datetime | timespan | plus +---------+------------------------------+-------------------------------+------------------------------ + | epoch | @ 14 secs ago | Wed Dec 31 15:59:46 1969 PST + | epoch | @ 1 min | Wed Dec 31 16:01:00 1969 PST + | epoch | @ 5 hours | Wed Dec 31 21:00:00 1969 PST + | epoch | @ 1 day 2 hours 3 mins 4 secs | Thu Jan 01 18:03:04 1970 PST + | epoch | @ 10 days | Sat Jan 10 16:00:00 1970 PST + | epoch | @ 3 mons | Tue Mar 31 16:00:00 1970 PST + | epoch | @ 5 mons | Sun May 31 17:00:00 1970 PDT + | epoch | @ 5 mons 12 hours | Mon Jun 01 05:00:00 1970 PDT + | epoch | @ 6 years | Wed Dec 31 16:00:00 1975 PST + | Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:31:47 1996 PST + | Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:33:01 1996 PST + | Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 22:32:01 1996 PST + | Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:31:47 1996 PST + | Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:33:01 1996 PST + | Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 19:35:05 1996 PST + | Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 22:32:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:31:47 1996 PST + | Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:33:01 1996 PST + | Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Mar 01 19:35:05 1996 PST + | Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 22:32:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Mar 02 19:35:05 1996 PST + | Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sat Mar 09 17:32:01 1996 PST + | Thu Feb 29 17:32:01 1996 PST | @ 10 days | Sun Mar 10 17:32:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST | @ 10 days | Mon Mar 11 17:32:01 1996 PST + | Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue May 28 18:32:01 1996 PDT + | Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed May 29 18:32:01 1996 PDT + | Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Sat Jun 01 18:32:01 1996 PDT + | Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Sun Jul 28 18:32:01 1996 PDT + | Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Mon Jul 29 06:32:01 1996 PDT + | Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Mon Jul 29 18:32:01 1996 PDT + | Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 06:32:01 1996 PDT + | Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Thu Aug 01 18:32:01 1996 PDT + | Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Aug 02 06:32:01 1996 PDT + | Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:31:47 1996 PST + | Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:33:01 1996 PST + | Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 22:32:01 1996 PST + | Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:31:47 1996 PST + | Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:33:01 1996 PST + | Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 31 19:35:05 1996 PST + | Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 22:32:01 1996 PST + | Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Jan 01 19:35:05 1997 PST + | Mon Dec 30 17:32:01 1996 PST | @ 10 days | Thu Jan 09 17:32:01 1997 PST + | Tue Dec 31 17:32:01 1996 PST | @ 10 days | Fri Jan 10 17:32:01 1997 PST + | Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Sun Mar 30 17:32:01 1997 PST + | Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Mar 31 17:32:01 1997 PST + | Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Fri May 30 18:32:01 1997 PDT + | Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Sat May 31 06:32:01 1997 PDT + | Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Sat May 31 18:32:01 1997 PDT + | Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Jun 01 06:32:01 1997 PDT + | Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:31:47 1999 PST + | Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:33:01 1999 PST + | Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 22:32:01 1999 PST + | Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:31:47 2000 PST + | Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:33:01 2000 PST + | Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Jan 01 19:35:05 2000 PST + | Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 22:32:01 2000 PST + | Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Jan 02 19:35:05 2000 PST + | Fri Dec 31 17:32:01 1999 PST | @ 10 days | Mon Jan 10 17:32:01 2000 PST + | Sat Jan 01 17:32:01 2000 PST | @ 10 days | Tue Jan 11 17:32:01 2000 PST + | Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Fri Mar 31 17:32:01 2000 PST + | Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Sat Apr 01 17:32:01 2000 PST + | Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Wed May 31 18:32:01 2000 PDT + | Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Thu Jun 01 06:32:01 2000 PDT + | Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Thu Jun 01 18:32:01 2000 PDT + | Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 02 06:32:01 2000 PDT + | Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:31:47 2000 PST + | Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:33:01 2000 PST + | Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 22:32:01 2000 PST + | Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:31:47 2001 PST + | Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:33:01 2001 PST + | Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Jan 01 19:35:05 2001 PST + | Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 22:32:01 2001 PST + | Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Jan 02 19:35:05 2001 PST + | Sun Dec 31 17:32:01 2000 PST | @ 10 days | Wed Jan 10 17:32:01 2001 PST + | Mon Jan 01 17:32:01 2001 PST | @ 10 days | Thu Jan 11 17:32:01 2001 PST + | Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Mar 31 17:32:01 2001 PST + | Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Apr 01 18:32:01 2001 PDT + | Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Thu May 31 18:32:01 2001 PDT + | Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Fri Jun 01 06:32:01 2001 PDT + | Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Fri Jun 01 18:32:01 2001 PDT + | Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Sat Jun 02 06:32:01 2001 PDT + | Wed Feb 28 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST + | Thu Feb 29 17:32:01 1996 PST | @ 6 years | Thu Feb 28 17:32:01 2002 PST + | Fri Mar 01 17:32:01 1996 PST | @ 6 years | Fri Mar 01 17:32:01 2002 PST + | Mon Dec 30 17:32:01 1996 PST | @ 6 years | Mon Dec 30 17:32:01 2002 PST + | Tue Dec 31 17:32:01 1996 PST | @ 6 years | Tue Dec 31 17:32:01 2002 PST + | epoch | @ 34 years | Wed Dec 31 16:00:00 2003 PST + | Fri Dec 31 17:32:01 1999 PST | @ 6 years | Sat Dec 31 17:32:01 2005 PST + | Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sun Jan 01 17:32:01 2006 PST + | Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sun Dec 31 17:32:01 2006 PST + | Mon Jan 01 17:32:01 2001 PST | @ 6 years | Mon Jan 01 17:32:01 2007 PST + | Wed Feb 28 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST + | Thu Feb 29 17:32:01 1996 PST | @ 34 years | Thu Feb 28 17:32:01 2030 PST + | Fri Mar 01 17:32:01 1996 PST | @ 34 years | Fri Mar 01 17:32:01 2030 PST + | Mon Dec 30 17:32:01 1996 PST | @ 34 years | Mon Dec 30 17:32:01 2030 PST + | Tue Dec 31 17:32:01 1996 PST | @ 34 years | Tue Dec 31 17:32:01 2030 PST + | Fri Dec 31 17:32:01 1999 PST | @ 34 years | Sat Dec 31 17:32:01 2033 PST + | Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sun Jan 01 17:32:01 2034 PST + | Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sun Dec 31 17:32:01 2034 PST + | Mon Jan 01 17:32:01 2001 PST | @ 34 years | Mon Jan 01 17:32:01 2035 PST +(100 rows) + +SELECT '' AS hundred, d.f1 AS datetime, t.f1 AS timespan, d.f1 - t.f1 AS minus + FROM TEMP_DATETIME d, TIMESPAN_TBL t + WHERE isfinite(d.f1) + ORDER BY minus, datetime, timespan; + hundred | datetime | timespan | minus +---------+------------------------------+-------------------------------+------------------------------ + | epoch | @ 34 years | Tue Dec 31 16:00:00 1935 PST + | Wed Feb 28 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST + | Thu Feb 29 17:32:01 1996 PST | @ 34 years | Wed Feb 28 17:32:01 1962 PST + | Fri Mar 01 17:32:01 1996 PST | @ 34 years | Thu Mar 01 17:32:01 1962 PST + | Mon Dec 30 17:32:01 1996 PST | @ 34 years | Sun Dec 30 17:32:01 1962 PST + | Tue Dec 31 17:32:01 1996 PST | @ 34 years | Mon Dec 31 17:32:01 1962 PST + | epoch | @ 6 years | Tue Dec 31 16:00:00 1963 PST + | Fri Dec 31 17:32:01 1999 PST | @ 34 years | Fri Dec 31 17:32:01 1965 PST + | Sat Jan 01 17:32:01 2000 PST | @ 34 years | Sat Jan 01 17:32:01 1966 PST + | Sun Dec 31 17:32:01 2000 PST | @ 34 years | Sat Dec 31 17:32:01 1966 PST + | Mon Jan 01 17:32:01 2001 PST | @ 34 years | Sun Jan 01 17:32:01 1967 PST + | epoch | @ 5 mons 12 hours | Thu Jul 31 05:00:00 1969 PDT + | epoch | @ 5 mons | Thu Jul 31 17:00:00 1969 PDT + | epoch | @ 3 mons | Tue Sep 30 17:00:00 1969 PDT + | epoch | @ 10 days | Sun Dec 21 16:00:00 1969 PST + | epoch | @ 1 day 2 hours 3 mins 4 secs | Tue Dec 30 13:56:56 1969 PST + | epoch | @ 5 hours | Wed Dec 31 11:00:00 1969 PST + | epoch | @ 1 min | Wed Dec 31 15:59:00 1969 PST + | epoch | @ 14 secs ago | Wed Dec 31 16:00:14 1969 PST + | Wed Feb 28 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST + | Thu Feb 29 17:32:01 1996 PST | @ 6 years | Wed Feb 28 17:32:01 1990 PST + | Fri Mar 01 17:32:01 1996 PST | @ 6 years | Thu Mar 01 17:32:01 1990 PST + | Mon Dec 30 17:32:01 1996 PST | @ 6 years | Sun Dec 30 17:32:01 1990 PST + | Tue Dec 31 17:32:01 1996 PST | @ 6 years | Mon Dec 31 17:32:01 1990 PST + | Fri Dec 31 17:32:01 1999 PST | @ 6 years | Fri Dec 31 17:32:01 1993 PST + | Sat Jan 01 17:32:01 2000 PST | @ 6 years | Sat Jan 01 17:32:01 1994 PST + | Sun Dec 31 17:32:01 2000 PST | @ 6 years | Sat Dec 31 17:32:01 1994 PST + | Mon Jan 01 17:32:01 2001 PST | @ 6 years | Sun Jan 01 17:32:01 1995 PST + | Wed Feb 28 17:32:01 1996 PST | @ 5 mons 12 hours | Thu Sep 28 06:32:01 1995 PDT + | Wed Feb 28 17:32:01 1996 PST | @ 5 mons | Thu Sep 28 18:32:01 1995 PDT + | Thu Feb 29 17:32:01 1996 PST | @ 5 mons 12 hours | Fri Sep 29 06:32:01 1995 PDT + | Thu Feb 29 17:32:01 1996 PST | @ 5 mons | Fri Sep 29 18:32:01 1995 PDT + | Fri Mar 01 17:32:01 1996 PST | @ 5 mons 12 hours | Sun Oct 01 06:32:01 1995 PDT + | Fri Mar 01 17:32:01 1996 PST | @ 5 mons | Sun Oct 01 18:32:01 1995 PDT + | Wed Feb 28 17:32:01 1996 PST | @ 3 mons | Tue Nov 28 17:32:01 1995 PST + | Thu Feb 29 17:32:01 1996 PST | @ 3 mons | Wed Nov 29 17:32:01 1995 PST + | Fri Mar 01 17:32:01 1996 PST | @ 3 mons | Fri Dec 01 17:32:01 1995 PST + | Wed Feb 28 17:32:01 1996 PST | @ 10 days | Sun Feb 18 17:32:01 1996 PST + | Thu Feb 29 17:32:01 1996 PST | @ 10 days | Mon Feb 19 17:32:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST | @ 10 days | Tue Feb 20 17:32:01 1996 PST + | Wed Feb 28 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Tue Feb 27 15:28:57 1996 PST + | Wed Feb 28 17:32:01 1996 PST | @ 5 hours | Wed Feb 28 12:32:01 1996 PST + | Thu Feb 29 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Wed Feb 28 15:28:57 1996 PST + | Wed Feb 28 17:32:01 1996 PST | @ 1 min | Wed Feb 28 17:31:01 1996 PST + | Wed Feb 28 17:32:01 1996 PST | @ 14 secs ago | Wed Feb 28 17:32:15 1996 PST + | Thu Feb 29 17:32:01 1996 PST | @ 5 hours | Thu Feb 29 12:32:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Feb 29 15:28:57 1996 PST + | Thu Feb 29 17:32:01 1996 PST | @ 1 min | Thu Feb 29 17:31:01 1996 PST + | Thu Feb 29 17:32:01 1996 PST | @ 14 secs ago | Thu Feb 29 17:32:15 1996 PST + | Fri Mar 01 17:32:01 1996 PST | @ 5 hours | Fri Mar 01 12:32:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST | @ 1 min | Fri Mar 01 17:31:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST | @ 14 secs ago | Fri Mar 01 17:32:15 1996 PST + | Mon Dec 30 17:32:01 1996 PST | @ 5 mons 12 hours | Tue Jul 30 06:32:01 1996 PDT + | Mon Dec 30 17:32:01 1996 PST | @ 5 mons | Tue Jul 30 18:32:01 1996 PDT + | Tue Dec 31 17:32:01 1996 PST | @ 5 mons 12 hours | Wed Jul 31 06:32:01 1996 PDT + | Tue Dec 31 17:32:01 1996 PST | @ 5 mons | Wed Jul 31 18:32:01 1996 PDT + | Mon Dec 30 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 18:32:01 1996 PDT + | Tue Dec 31 17:32:01 1996 PST | @ 3 mons | Mon Sep 30 18:32:01 1996 PDT + | Mon Dec 30 17:32:01 1996 PST | @ 10 days | Fri Dec 20 17:32:01 1996 PST + | Tue Dec 31 17:32:01 1996 PST | @ 10 days | Sat Dec 21 17:32:01 1996 PST + | Mon Dec 30 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 29 15:28:57 1996 PST + | Mon Dec 30 17:32:01 1996 PST | @ 5 hours | Mon Dec 30 12:32:01 1996 PST + | Tue Dec 31 17:32:01 1996 PST | @ 1 day 2 hours 3 mins 4 secs | Mon Dec 30 15:28:57 1996 PST + | Mon Dec 30 17:32:01 1996 PST | @ 1 min | Mon Dec 30 17:31:01 1996 PST + | Mon Dec 30 17:32:01 1996 PST | @ 14 secs ago | Mon Dec 30 17:32:15 1996 PST + | Tue Dec 31 17:32:01 1996 PST | @ 5 hours | Tue Dec 31 12:32:01 1996 PST + | Tue Dec 31 17:32:01 1996 PST | @ 1 min | Tue Dec 31 17:31:01 1996 PST + | Tue Dec 31 17:32:01 1996 PST | @ 14 secs ago | Tue Dec 31 17:32:15 1996 PST + | Fri Dec 31 17:32:01 1999 PST | @ 5 mons 12 hours | Sat Jul 31 06:32:01 1999 PDT + | Fri Dec 31 17:32:01 1999 PST | @ 5 mons | Sat Jul 31 18:32:01 1999 PDT + | Sat Jan 01 17:32:01 2000 PST | @ 5 mons 12 hours | Sun Aug 01 06:32:01 1999 PDT + | Sat Jan 01 17:32:01 2000 PST | @ 5 mons | Sun Aug 01 18:32:01 1999 PDT + | Fri Dec 31 17:32:01 1999 PST | @ 3 mons | Thu Sep 30 18:32:01 1999 PDT + | Sat Jan 01 17:32:01 2000 PST | @ 3 mons | Fri Oct 01 18:32:01 1999 PDT + | Fri Dec 31 17:32:01 1999 PST | @ 10 days | Tue Dec 21 17:32:01 1999 PST + | Sat Jan 01 17:32:01 2000 PST | @ 10 days | Wed Dec 22 17:32:01 1999 PST + | Fri Dec 31 17:32:01 1999 PST | @ 1 day 2 hours 3 mins 4 secs | Thu Dec 30 15:28:57 1999 PST + | Fri Dec 31 17:32:01 1999 PST | @ 5 hours | Fri Dec 31 12:32:01 1999 PST + | Sat Jan 01 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Fri Dec 31 15:28:57 1999 PST + | Fri Dec 31 17:32:01 1999 PST | @ 1 min | Fri Dec 31 17:31:01 1999 PST + | Fri Dec 31 17:32:01 1999 PST | @ 14 secs ago | Fri Dec 31 17:32:15 1999 PST + | Sat Jan 01 17:32:01 2000 PST | @ 5 hours | Sat Jan 01 12:32:01 2000 PST + | Sat Jan 01 17:32:01 2000 PST | @ 1 min | Sat Jan 01 17:31:01 2000 PST + | Sat Jan 01 17:32:01 2000 PST | @ 14 secs ago | Sat Jan 01 17:32:15 2000 PST + | Sun Dec 31 17:32:01 2000 PST | @ 5 mons 12 hours | Mon Jul 31 06:32:01 2000 PDT + | Sun Dec 31 17:32:01 2000 PST | @ 5 mons | Mon Jul 31 18:32:01 2000 PDT + | Mon Jan 01 17:32:01 2001 PST | @ 5 mons 12 hours | Tue Aug 01 06:32:01 2000 PDT + | Mon Jan 01 17:32:01 2001 PST | @ 5 mons | Tue Aug 01 18:32:01 2000 PDT + | Sun Dec 31 17:32:01 2000 PST | @ 3 mons | Sat Sep 30 18:32:01 2000 PDT + | Mon Jan 01 17:32:01 2001 PST | @ 3 mons | Sun Oct 01 18:32:01 2000 PDT + | Sun Dec 31 17:32:01 2000 PST | @ 10 days | Thu Dec 21 17:32:01 2000 PST + | Mon Jan 01 17:32:01 2001 PST | @ 10 days | Fri Dec 22 17:32:01 2000 PST + | Sun Dec 31 17:32:01 2000 PST | @ 1 day 2 hours 3 mins 4 secs | Sat Dec 30 15:28:57 2000 PST + | Sun Dec 31 17:32:01 2000 PST | @ 5 hours | Sun Dec 31 12:32:01 2000 PST + | Mon Jan 01 17:32:01 2001 PST | @ 1 day 2 hours 3 mins 4 secs | Sun Dec 31 15:28:57 2000 PST + | Sun Dec 31 17:32:01 2000 PST | @ 1 min | Sun Dec 31 17:31:01 2000 PST + | Sun Dec 31 17:32:01 2000 PST | @ 14 secs ago | Sun Dec 31 17:32:15 2000 PST + | Mon Jan 01 17:32:01 2001 PST | @ 5 hours | Mon Jan 01 12:32:01 2001 PST + | Mon Jan 01 17:32:01 2001 PST | @ 1 min | Mon Jan 01 17:31:01 2001 PST + | Mon Jan 01 17:32:01 2001 PST | @ 14 secs ago | Mon Jan 01 17:32:15 2001 PST +(100 rows) + +SELECT '' AS ten, d.f1 AS datetime, datetime '1980-01-06 00:00 GMT' AS gpstime_zero, + d.f1 - datetime '1980-01-06 00:00 GMT' AS difference + FROM TEMP_DATETIME d + ORDER BY difference; + ten | datetime | gpstime_zero | difference +-----+------------------------------+------------------------------+---------------------------------- + | epoch | Sat Jan 05 16:00:00 1980 PST | @ 3657 days ago + | Wed Feb 28 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5898 days 1 hour 32 mins 1 sec + | Thu Feb 29 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5899 days 1 hour 32 mins 1 sec + | Fri Mar 01 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 5900 days 1 hour 32 mins 1 sec + | Mon Dec 30 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6204 days 1 hour 32 mins 1 sec + | Tue Dec 31 17:32:01 1996 PST | Sat Jan 05 16:00:00 1980 PST | @ 6205 days 1 hour 32 mins 1 sec + | Fri Dec 31 17:32:01 1999 PST | Sat Jan 05 16:00:00 1980 PST | @ 7300 days 1 hour 32 mins 1 sec + | Sat Jan 01 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7301 days 1 hour 32 mins 1 sec + | Sun Dec 31 17:32:01 2000 PST | Sat Jan 05 16:00:00 1980 PST | @ 7666 days 1 hour 32 mins 1 sec + | Mon Jan 01 17:32:01 2001 PST | Sat Jan 05 16:00:00 1980 PST | @ 7667 days 1 hour 32 mins 1 sec +(10 rows) + +SELECT '' AS hundred, d1.f1 AS datetime1, d2.f1 AS datetime2, d1.f1 - d2.f1 AS difference + FROM TEMP_DATETIME d1, TEMP_DATETIME d2 + ORDER BY datetime1, datetime2, difference; + hundred | datetime1 | datetime2 | difference +---------+------------------------------+------------------------------+--------------------------------------- + | epoch | epoch | @ 0 + | epoch | Wed Feb 28 17:32:01 1996 PST | @ 9555 days 1 hour 32 mins 1 sec ago + | epoch | Thu Feb 29 17:32:01 1996 PST | @ 9556 days 1 hour 32 mins 1 sec ago + | epoch | Fri Mar 01 17:32:01 1996 PST | @ 9557 days 1 hour 32 mins 1 sec ago + | epoch | Mon Dec 30 17:32:01 1996 PST | @ 9861 days 1 hour 32 mins 1 sec ago + | epoch | Tue Dec 31 17:32:01 1996 PST | @ 9862 days 1 hour 32 mins 1 sec ago + | epoch | Fri Dec 31 17:32:01 1999 PST | @ 10957 days 1 hour 32 mins 1 sec ago + | epoch | Sat Jan 01 17:32:01 2000 PST | @ 10958 days 1 hour 32 mins 1 sec ago + | epoch | Sun Dec 31 17:32:01 2000 PST | @ 11323 days 1 hour 32 mins 1 sec ago + | epoch | Mon Jan 01 17:32:01 2001 PST | @ 11324 days 1 hour 32 mins 1 sec ago + | Wed Feb 28 17:32:01 1996 PST | epoch | @ 9555 days 1 hour 32 mins 1 sec + | Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 0 + | Wed Feb 28 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day ago + | Wed Feb 28 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 2 days ago + | Wed Feb 28 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 306 days ago + | Wed Feb 28 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 307 days ago + | Wed Feb 28 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1402 days ago + | Wed Feb 28 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1403 days ago + | Wed Feb 28 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1768 days ago + | Wed Feb 28 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1769 days ago + | Thu Feb 29 17:32:01 1996 PST | epoch | @ 9556 days 1 hour 32 mins 1 sec + | Thu Feb 29 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 1 day + | Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 0 + | Thu Feb 29 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 1 day ago + | Thu Feb 29 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 305 days ago + | Thu Feb 29 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 306 days ago + | Thu Feb 29 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1401 days ago + | Thu Feb 29 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1402 days ago + | Thu Feb 29 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1767 days ago + | Thu Feb 29 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1768 days ago + | Fri Mar 01 17:32:01 1996 PST | epoch | @ 9557 days 1 hour 32 mins 1 sec + | Fri Mar 01 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 2 days + | Fri Mar 01 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 1 day + | Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 0 + | Fri Mar 01 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 304 days ago + | Fri Mar 01 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 305 days ago + | Fri Mar 01 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1400 days ago + | Fri Mar 01 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1401 days ago + | Fri Mar 01 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1766 days ago + | Fri Mar 01 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1767 days ago + | Mon Dec 30 17:32:01 1996 PST | epoch | @ 9861 days 1 hour 32 mins 1 sec + | Mon Dec 30 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 306 days + | Mon Dec 30 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 305 days + | Mon Dec 30 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 304 days + | Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 0 + | Mon Dec 30 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 1 day ago + | Mon Dec 30 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1096 days ago + | Mon Dec 30 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1097 days ago + | Mon Dec 30 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1462 days ago + | Mon Dec 30 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1463 days ago + | Tue Dec 31 17:32:01 1996 PST | epoch | @ 9862 days 1 hour 32 mins 1 sec + | Tue Dec 31 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST | @ 307 days + | Tue Dec 31 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST | @ 306 days + | Tue Dec 31 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST | @ 305 days + | Tue Dec 31 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST | @ 1 day + | Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST | @ 0 + | Tue Dec 31 17:32:01 1996 PST | Fri Dec 31 17:32:01 1999 PST | @ 1095 days ago + | Tue Dec 31 17:32:01 1996 PST | Sat Jan 01 17:32:01 2000 PST | @ 1096 days ago + | Tue Dec 31 17:32:01 1996 PST | Sun Dec 31 17:32:01 2000 PST | @ 1461 days ago + | Tue Dec 31 17:32:01 1996 PST | Mon Jan 01 17:32:01 2001 PST | @ 1462 days ago + | Fri Dec 31 17:32:01 1999 PST | epoch | @ 10957 days 1 hour 32 mins 1 sec + | Fri Dec 31 17:32:01 1999 PST | Wed Feb 28 17:32:01 1996 PST | @ 1402 days + | Fri Dec 31 17:32:01 1999 PST | Thu Feb 29 17:32:01 1996 PST | @ 1401 days + | Fri Dec 31 17:32:01 1999 PST | Fri Mar 01 17:32:01 1996 PST | @ 1400 days + | Fri Dec 31 17:32:01 1999 PST | Mon Dec 30 17:32:01 1996 PST | @ 1096 days + | Fri Dec 31 17:32:01 1999 PST | Tue Dec 31 17:32:01 1996 PST | @ 1095 days + | Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST | @ 0 + | Fri Dec 31 17:32:01 1999 PST | Sat Jan 01 17:32:01 2000 PST | @ 1 day ago + | Fri Dec 31 17:32:01 1999 PST | Sun Dec 31 17:32:01 2000 PST | @ 366 days ago + | Fri Dec 31 17:32:01 1999 PST | Mon Jan 01 17:32:01 2001 PST | @ 367 days ago + | Sat Jan 01 17:32:01 2000 PST | epoch | @ 10958 days 1 hour 32 mins 1 sec + | Sat Jan 01 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1403 days + | Sat Jan 01 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1402 days + | Sat Jan 01 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1401 days + | Sat Jan 01 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1097 days + | Sat Jan 01 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1096 days + | Sat Jan 01 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 1 day + | Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 0 + | Sat Jan 01 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 365 days ago + | Sat Jan 01 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 366 days ago + | Sun Dec 31 17:32:01 2000 PST | epoch | @ 11323 days 1 hour 32 mins 1 sec + | Sun Dec 31 17:32:01 2000 PST | Wed Feb 28 17:32:01 1996 PST | @ 1768 days + | Sun Dec 31 17:32:01 2000 PST | Thu Feb 29 17:32:01 1996 PST | @ 1767 days + | Sun Dec 31 17:32:01 2000 PST | Fri Mar 01 17:32:01 1996 PST | @ 1766 days + | Sun Dec 31 17:32:01 2000 PST | Mon Dec 30 17:32:01 1996 PST | @ 1462 days + | Sun Dec 31 17:32:01 2000 PST | Tue Dec 31 17:32:01 1996 PST | @ 1461 days + | Sun Dec 31 17:32:01 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 366 days + | Sun Dec 31 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 365 days + | Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 0 + | Sun Dec 31 17:32:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 1 day ago + | Mon Jan 01 17:32:01 2001 PST | epoch | @ 11324 days 1 hour 32 mins 1 sec + | Mon Jan 01 17:32:01 2001 PST | Wed Feb 28 17:32:01 1996 PST | @ 1769 days + | Mon Jan 01 17:32:01 2001 PST | Thu Feb 29 17:32:01 1996 PST | @ 1768 days + | Mon Jan 01 17:32:01 2001 PST | Fri Mar 01 17:32:01 1996 PST | @ 1767 days + | Mon Jan 01 17:32:01 2001 PST | Mon Dec 30 17:32:01 1996 PST | @ 1463 days + | Mon Jan 01 17:32:01 2001 PST | Tue Dec 31 17:32:01 1996 PST | @ 1462 days + | Mon Jan 01 17:32:01 2001 PST | Fri Dec 31 17:32:01 1999 PST | @ 367 days + | Mon Jan 01 17:32:01 2001 PST | Sat Jan 01 17:32:01 2000 PST | @ 366 days + | Mon Jan 01 17:32:01 2001 PST | Sun Dec 31 17:32:01 2000 PST | @ 1 day + | Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST | @ 0 +(100 rows) + +SELECT '' as fifty, d1 as datetime, + date_part('year', d1) AS year, date_part('month', d1) AS month, + date_part('day',d1) AS day, date_part('hour', d1) AS hour, + date_part('minute', d1) AS minute, date_part('second', d1) AS second + FROM DATETIME_TBL + WHERE isfinite(d1) and d1 >= '1-jan-1900 GMT' + ORDER BY datetime; + fifty | datetime | year | month | day | hour | minute | second +-------+---------------------------------+------+-------+-----+------+--------+---------- + | epoch | 1969 | 12 | 31 | 16 | 0 | 0 + | Wed Feb 28 17:32:01 1996 PST | 1996 | 2 | 28 | 17 | 32 | 1 + | Thu Feb 29 17:32:01 1996 PST | 1996 | 2 | 29 | 17 | 32 | 1 + | Fri Mar 01 17:32:01 1996 PST | 1996 | 3 | 1 | 17 | 32 | 1 + | Mon Dec 30 17:32:01 1996 PST | 1996 | 12 | 30 | 17 | 32 | 1 + | Tue Dec 31 17:32:01 1996 PST | 1996 | 12 | 31 | 17 | 32 | 1 + | Wed Jan 01 17:32:01 1997 PST | 1997 | 1 | 1 | 17 | 32 | 1 + | Thu Jan 02 00:00:00 1997 PST | 1997 | 1 | 2 | 0 | 0 | 0 + | Thu Jan 02 03:04:05 1997 PST | 1997 | 1 | 2 | 3 | 4 | 5 + | Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1 + | Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1 + | Mon Feb 10 09:32:01 1997 PST | 1997 | 2 | 10 | 9 | 32 | 1 + | Mon Feb 10 17:32:00 1997 PST | 1997 | 2 | 10 | 17 | 32 | 0 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1 + | Mon Feb 10 17:32:01.00 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.000001 + | Mon Feb 10 17:32:01.40 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.4 + | Mon Feb 10 17:32:01.50 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.5 + | Mon Feb 10 17:32:01.60 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.6 + | Mon Feb 10 17:32:02.00 1997 PST | 1997 | 2 | 10 | 17 | 32 | 1.999999 + | Tue Feb 11 17:32:01 1997 PST | 1997 | 2 | 11 | 17 | 32 | 1 + | Wed Feb 12 17:32:01 1997 PST | 1997 | 2 | 12 | 17 | 32 | 1 + | Thu Feb 13 17:32:01 1997 PST | 1997 | 2 | 13 | 17 | 32 | 1 + | Fri Feb 14 17:32:01 1997 PST | 1997 | 2 | 14 | 17 | 32 | 1 + | Sat Feb 15 17:32:01 1997 PST | 1997 | 2 | 15 | 17 | 32 | 1 + | Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1 + | Sun Feb 16 17:32:01 1997 PST | 1997 | 2 | 16 | 17 | 32 | 1 + | Fri Feb 28 17:32:01 1997 PST | 1997 | 2 | 28 | 17 | 32 | 1 + | Sat Mar 01 17:32:01 1997 PST | 1997 | 3 | 1 | 17 | 32 | 1 + | Tue Jun 10 17:32:01 1997 PDT | 1997 | 6 | 10 | 17 | 32 | 1 + | Tue Jun 10 18:32:01 1997 PDT | 1997 | 6 | 10 | 18 | 32 | 1 + | Tue Dec 30 17:32:01 1997 PST | 1997 | 12 | 30 | 17 | 32 | 1 + | Wed Dec 31 17:32:01 1997 PST | 1997 | 12 | 31 | 17 | 32 | 1 + | Fri Dec 31 17:32:01 1999 PST | 1999 | 12 | 31 | 17 | 32 | 1 + | Sat Jan 01 17:32:01 2000 PST | 2000 | 1 | 1 | 17 | 32 | 1 + | Sun Dec 31 17:32:01 2000 PST | 2000 | 12 | 31 | 17 | 32 | 1 + | Mon Jan 01 17:32:01 2001 PST | 2001 | 1 | 1 | 17 | 32 | 1 + | Sat Feb 16 17:32:01 2097 | 2097 | 2 | 16 | 17 | 32 | 1 +(49 rows) + +-- +-- abstime, reltime arithmetic +-- +SELECT '' AS four, f1 AS abstime, + date_part('year', f1) AS year, date_part('month', f1) AS month, + date_part('day',f1) AS day, date_part('hour', f1) AS hour, + date_part('minute', f1) AS minute, date_part('second', f1) AS second + FROM ABSTIME_TBL + WHERE isfinite(f1) and f1 <> abstime 'current' + ORDER BY abstime; + four | abstime | year | month | day | hour | minute | second +------+------------------------------+------+-------+-----+------+--------+-------- + | Sat May 10 23:59:12 1947 PDT | 1947 | 5 | 10 | 23 | 59 | 12 + | epoch | 1969 | 12 | 31 | 16 | 0 | 0 + | Sun Jan 14 03:14:21 1973 PST | 1973 | 1 | 14 | 3 | 14 | 21 + | Mon May 01 00:30:30 1995 PDT | 1995 | 5 | 1 | 0 | 30 | 30 +(4 rows) + +-- +-- conversions +-- +SELECT '' AS ten, f1 AS datetime, date( f1) AS date + FROM TEMP_DATETIME + WHERE f1 <> datetime 'current' + ORDER BY date; + ten | datetime | date +-----+------------------------------+------------ + | epoch | 01-01-1970 + | Wed Feb 28 17:32:01 1996 PST | 02-28-1996 + | Thu Feb 29 17:32:01 1996 PST | 02-29-1996 + | Fri Mar 01 17:32:01 1996 PST | 03-01-1996 + | Mon Dec 30 17:32:01 1996 PST | 12-30-1996 + | Tue Dec 31 17:32:01 1996 PST | 12-31-1996 + | Fri Dec 31 17:32:01 1999 PST | 12-31-1999 + | Sat Jan 01 17:32:01 2000 PST | 01-01-2000 + | Sun Dec 31 17:32:01 2000 PST | 12-31-2000 + | Mon Jan 01 17:32:01 2001 PST | 01-01-2001 +(10 rows) + +SELECT '' AS ten, f1 AS datetime, abstime( f1) AS abstime + FROM TEMP_DATETIME + ORDER BY abstime; + ten | datetime | abstime +-----+------------------------------+------------------------------ + | epoch | epoch + | Wed Feb 28 17:32:01 1996 PST | Wed Feb 28 17:32:01 1996 PST + | Thu Feb 29 17:32:01 1996 PST | Thu Feb 29 17:32:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST | Fri Mar 01 17:32:01 1996 PST + | Mon Dec 30 17:32:01 1996 PST | Mon Dec 30 17:32:01 1996 PST + | Tue Dec 31 17:32:01 1996 PST | Tue Dec 31 17:32:01 1996 PST + | Fri Dec 31 17:32:01 1999 PST | Fri Dec 31 17:32:01 1999 PST + | Sat Jan 01 17:32:01 2000 PST | Sat Jan 01 17:32:01 2000 PST + | Sun Dec 31 17:32:01 2000 PST | Sun Dec 31 17:32:01 2000 PST + | Mon Jan 01 17:32:01 2001 PST | Mon Jan 01 17:32:01 2001 PST +(10 rows) + +SELECT '' AS five, f1 AS abstime, date( f1) AS date + FROM ABSTIME_TBL + WHERE isfinite(f1) AND f1 <> abstime 'current' + ORDER BY date; + five | abstime | date +------+------------------------------+------------ + | Sat May 10 23:59:12 1947 PDT | 05-10-1947 + | epoch | 01-01-1970 + | Sun Jan 14 03:14:21 1973 PST | 01-14-1973 + | Mon May 01 00:30:30 1995 PDT | 05-01-1995 +(4 rows) + +SELECT '' AS five, d1 AS datetime, abstime(d1) AS abstime + FROM DATETIME_TBL WHERE NOT isfinite(d1); + five | datetime | abstime +------+-----------+----------- + | invalid | invalid + | -infinity | -infinity + | infinity | infinity + | invalid | invalid + | invalid | invalid +(5 rows) + +SELECT '' AS three, f1 as abstime, datetime(f1) AS datetime + FROM ABSTIME_TBL WHERE NOT isfinite(f1); + three | abstime | datetime +-------+-----------+----------- + | infinity | infinity + | -infinity | -infinity + | invalid | invalid +(3 rows) + +SELECT '' AS ten, f1 AS timespan, reltime( f1) AS reltime + FROM TIMESPAN_TBL; + ten | timespan | reltime +-----+-------------------------------+------------------------------- + | @ 1 min | @ 1 min + | @ 5 hours | @ 5 hours + | @ 10 days | @ 10 days + | @ 34 years | @ 34 years + | @ 3 mons | @ 3 mons + | @ 14 secs ago | @ 14 secs ago + | @ 1 day 2 hours 3 mins 4 secs | @ 1 day 2 hours 3 mins 4 secs + | @ 6 years | @ 6 years + | @ 5 mons | @ 5 mons + | @ 5 mons 12 hours | @ 5 mons 12 hours +(10 rows) + +SELECT '' AS six, f1 as reltime, timespan( f1) AS timespan + FROM RELTIME_TBL; + six | reltime | timespan +-----+---------------+--------------- + | @ 1 min | @ 1 min + | @ 5 hours | @ 5 hours + | @ 10 days | @ 10 days + | @ 34 years | @ 34 years + | @ 3 mons | @ 3 mons + | @ 14 secs ago | @ 14 secs ago +(6 rows) + +DROP TABLE TEMP_DATETIME; +-- +-- formats +-- +SET DateStyle TO 'US,Postgres'; +SHOW DateStyle; +NOTICE: DateStyle is Postgres with US (NonEuropean) conventions +SELECT '' AS sixty_two, d1 AS us_postgres FROM DATETIME_TBL; + sixty_two | us_postgres +-----------+--------------------------------- + | invalid + | -infinity + | infinity + | epoch + | Mon Feb 10 17:32:01 1997 PST + | invalid + | invalid + | Mon Feb 10 17:32:01.00 1997 PST + | Mon Feb 10 17:32:02.00 1997 PST + | Mon Feb 10 17:32:01.40 1997 PST + | Mon Feb 10 17:32:01.50 1997 PST + | Mon Feb 10 17:32:01.60 1997 PST + | Thu Jan 02 00:00:00 1997 PST + | Thu Jan 02 03:04:05 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Tue Jun 10 17:32:01 1997 PDT + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:00 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 09:32:01 1997 PST + | Mon Feb 10 09:32:01 1997 PST + | Mon Feb 10 09:32:01 1997 PST + | Tue Jun 10 18:32:01 1997 PDT + | Mon Feb 10 17:32:01 1997 PST + | Tue Feb 11 17:32:01 1997 PST + | Wed Feb 12 17:32:01 1997 PST + | Thu Feb 13 17:32:01 1997 PST + | Fri Feb 14 17:32:01 1997 PST + | Sat Feb 15 17:32:01 1997 PST + | Sun Feb 16 17:32:01 1997 PST + | Tue Feb 16 17:32 0097 BC + | Sat Feb 16 17:32:01 0097 + | Thu Feb 16 17:32:01 0597 + | Tue Feb 16 17:32:01 1097 + | Sat Feb 16 17:32:01 1697 + | Thu Feb 16 17:32:01 1797 + | Tue Feb 16 17:32:01 1897 + | Sun Feb 16 17:32:01 1997 PST + | Sat Feb 16 17:32:01 2097 + | Wed Feb 28 17:32:01 1996 PST + | Thu Feb 29 17:32:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST + | Mon Dec 30 17:32:01 1996 PST + | Tue Dec 31 17:32:01 1996 PST + | Wed Jan 01 17:32:01 1997 PST + | Fri Feb 28 17:32:01 1997 PST + | Sat Mar 01 17:32:01 1997 PST + | Tue Dec 30 17:32:01 1997 PST + | Wed Dec 31 17:32:01 1997 PST + | Fri Dec 31 17:32:01 1999 PST + | Sat Jan 01 17:32:01 2000 PST + | Sun Dec 31 17:32:01 2000 PST + | Mon Jan 01 17:32:01 2001 PST +(61 rows) + +SELECT '' AS eight, f1 AS us_postgres FROM ABSTIME_TBL; + eight | us_postgres +-------+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | Mon May 01 00:30:30 1995 PDT + | epoch + | current + | infinity + | -infinity + | Sat May 10 23:59:12 1947 PDT + | invalid +(8 rows) + +SET DateStyle TO 'US,ISO'; +SELECT '' AS sixty_two, d1 AS us_iso FROM DATETIME_TBL; + sixty_two | us_iso +-----------+--------------------------- + | invalid + | -infinity + | infinity + | epoch + | 1997-02-10 17:32:01-08 + | invalid + | invalid + | 1997-02-10 17:32:01.00-08 + | 1997-02-10 17:32:02.00-08 + | 1997-02-10 17:32:01.40-08 + | 1997-02-10 17:32:01.50-08 + | 1997-02-10 17:32:01.60-08 + | 1997-01-02 00:00:00-08 + | 1997-01-02 03:04:05-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-06-10 17:32:01-07 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:00-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-06-10 18:32:01-07 + | 1997-02-10 17:32:01-08 + | 1997-02-11 17:32:01-08 + | 1997-02-12 17:32:01-08 + | 1997-02-13 17:32:01-08 + | 1997-02-14 17:32:01-08 + | 1997-02-15 17:32:01-08 + | 1997-02-16 17:32:01-08 + | 0097-02-16 17:32 BC + | 0097-02-16 17:32:01 + | 0597-02-16 17:32:01 + | 1097-02-16 17:32:01 + | 1697-02-16 17:32:01 + | 1797-02-16 17:32:01 + | 1897-02-16 17:32:01 + | 1997-02-16 17:32:01-08 + | 2097-02-16 17:32:01 + | 1996-02-28 17:32:01-08 + | 1996-02-29 17:32:01-08 + | 1996-03-01 17:32:01-08 + | 1996-12-30 17:32:01-08 + | 1996-12-31 17:32:01-08 + | 1997-01-01 17:32:01-08 + | 1997-02-28 17:32:01-08 + | 1997-03-01 17:32:01-08 + | 1997-12-30 17:32:01-08 + | 1997-12-31 17:32:01-08 + | 1999-12-31 17:32:01-08 + | 2000-01-01 17:32:01-08 + | 2000-12-31 17:32:01-08 + | 2001-01-01 17:32:01-08 +(61 rows) + +SELECT '' AS eight, f1 AS us_iso FROM ABSTIME_TBL; + eight | us_iso +-------+------------------------ + | 1973-01-14 03:14:21-08 + | 1995-05-01 00:30:30-07 + | epoch + | current + | infinity + | -infinity + | 1947-05-10 23:59:12-07 + | invalid +(8 rows) + +SET DateStyle TO 'US,SQL'; +SHOW DateStyle; +NOTICE: DateStyle is SQL with US (NonEuropean) conventions +SELECT '' AS sixty_two, d1 AS us_sql FROM DATETIME_TBL; + sixty_two | us_sql +-----------+---------------------------- + | invalid + | -infinity + | infinity + | epoch + | 02/10/1997 17:32:01.00 PST + | invalid + | invalid + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:02.00 PST + | 02/10/1997 17:32:01.40 PST + | 02/10/1997 17:32:01.50 PST + | 02/10/1997 17:32:01.60 PST + | 01/02/1997 00:00:00.00 PST + | 01/02/1997 03:04:05.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 06/10/1997 17:32:01.00 PDT + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:00.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 09:32:01.00 PST + | 02/10/1997 09:32:01.00 PST + | 02/10/1997 09:32:01.00 PST + | 06/10/1997 18:32:01.00 PDT + | 02/10/1997 17:32:01.00 PST + | 02/11/1997 17:32:01.00 PST + | 02/12/1997 17:32:01.00 PST + | 02/13/1997 17:32:01.00 PST + | 02/14/1997 17:32:01.00 PST + | 02/15/1997 17:32:01.00 PST + | 02/16/1997 17:32:01.00 PST + | 02/16/0097 17:32 BC + | 02/16/0097 17:32:01.00 + | 02/16/0597 17:32:01.00 + | 02/16/1097 17:32:01.00 + | 02/16/1697 17:32:01.00 + | 02/16/1797 17:32:01.00 + | 02/16/1897 17:32:01.00 + | 02/16/1997 17:32:01.00 PST + | 02/16/2097 17:32:01.00 + | 02/28/1996 17:32:01.00 PST + | 02/29/1996 17:32:01.00 PST + | 03/01/1996 17:32:01.00 PST + | 12/30/1996 17:32:01.00 PST + | 12/31/1996 17:32:01.00 PST + | 01/01/1997 17:32:01.00 PST + | 02/28/1997 17:32:01.00 PST + | 03/01/1997 17:32:01.00 PST + | 12/30/1997 17:32:01.00 PST + | 12/31/1997 17:32:01.00 PST + | 12/31/1999 17:32:01.00 PST + | 01/01/2000 17:32:01.00 PST + | 12/31/2000 17:32:01.00 PST + | 01/01/2001 17:32:01.00 PST +(61 rows) + +SELECT '' AS eight, f1 AS us_sql FROM ABSTIME_TBL; + eight | us_sql +-------+---------------------------- + | 01/14/1973 03:14:21.00 PST + | 05/01/1995 00:30:30.00 PDT + | epoch + | current + | infinity + | -infinity + | 05/10/1947 23:59:12.00 PDT + | invalid +(8 rows) + +SET DateStyle TO 'European,Postgres'; +SHOW DateStyle; +NOTICE: DateStyle is Postgres with European conventions +INSERT INTO DATETIME_TBL VALUES('13/06/1957'); +SELECT count(*) as one FROM DATETIME_TBL WHERE d1 = 'Jun 13 1957'; + one +----- + 1 +(1 row) + +SELECT '' AS sixty_three, d1 AS european_postgres FROM DATETIME_TBL; + sixty_three | european_postgres +-------------+--------------------------------- + | invalid + | -infinity + | infinity + | epoch + | Mon 10 Feb 17:32:01 1997 PST + | invalid + | invalid + | Mon 10 Feb 17:32:01.00 1997 PST + | Mon 10 Feb 17:32:02.00 1997 PST + | Mon 10 Feb 17:32:01.40 1997 PST + | Mon 10 Feb 17:32:01.50 1997 PST + | Mon 10 Feb 17:32:01.60 1997 PST + | Thu 02 Jan 00:00:00 1997 PST + | Thu 02 Jan 03:04:05 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Tue 10 Jun 17:32:01 1997 PDT + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:00 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 09:32:01 1997 PST + | Mon 10 Feb 09:32:01 1997 PST + | Mon 10 Feb 09:32:01 1997 PST + | Tue 10 Jun 18:32:01 1997 PDT + | Mon 10 Feb 17:32:01 1997 PST + | Tue 11 Feb 17:32:01 1997 PST + | Wed 12 Feb 17:32:01 1997 PST + | Thu 13 Feb 17:32:01 1997 PST + | Fri 14 Feb 17:32:01 1997 PST + | Sat 15 Feb 17:32:01 1997 PST + | Sun 16 Feb 17:32:01 1997 PST + | Tue 16 Feb 17:32 0097 BC + | Sat 16 Feb 17:32:01 0097 + | Thu 16 Feb 17:32:01 0597 + | Tue 16 Feb 17:32:01 1097 + | Sat 16 Feb 17:32:01 1697 + | Thu 16 Feb 17:32:01 1797 + | Tue 16 Feb 17:32:01 1897 + | Sun 16 Feb 17:32:01 1997 PST + | Sat 16 Feb 17:32:01 2097 + | Wed 28 Feb 17:32:01 1996 PST + | Thu 29 Feb 17:32:01 1996 PST + | Fri 01 Mar 17:32:01 1996 PST + | Mon 30 Dec 17:32:01 1996 PST + | Tue 31 Dec 17:32:01 1996 PST + | Wed 01 Jan 17:32:01 1997 PST + | Fri 28 Feb 17:32:01 1997 PST + | Sat 01 Mar 17:32:01 1997 PST + | Tue 30 Dec 17:32:01 1997 PST + | Wed 31 Dec 17:32:01 1997 PST + | Fri 31 Dec 17:32:01 1999 PST + | Sat 01 Jan 17:32:01 2000 PST + | Sun 31 Dec 17:32:01 2000 PST + | Mon 01 Jan 17:32:01 2001 PST + | Thu 13 Jun 00:00:00 1957 PDT +(62 rows) + +SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL; + eight | european_postgres +-------+------------------------------ + | Sun 14 Jan 03:14:21 1973 PST + | Mon 01 May 00:30:30 1995 PDT + | epoch + | current + | infinity + | -infinity + | Sat 10 May 23:59:12 1947 PDT + | invalid +(8 rows) + +SET DateStyle TO 'European,ISO'; +SHOW DateStyle; +NOTICE: DateStyle is ISO with European conventions +SELECT '' AS sixty_three, d1 AS european_iso FROM DATETIME_TBL; + sixty_three | european_iso +-------------+--------------------------- + | invalid + | -infinity + | infinity + | epoch + | 1997-02-10 17:32:01-08 + | invalid + | invalid + | 1997-02-10 17:32:01.00-08 + | 1997-02-10 17:32:02.00-08 + | 1997-02-10 17:32:01.40-08 + | 1997-02-10 17:32:01.50-08 + | 1997-02-10 17:32:01.60-08 + | 1997-01-02 00:00:00-08 + | 1997-01-02 03:04:05-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-06-10 17:32:01-07 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:00-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-06-10 18:32:01-07 + | 1997-02-10 17:32:01-08 + | 1997-02-11 17:32:01-08 + | 1997-02-12 17:32:01-08 + | 1997-02-13 17:32:01-08 + | 1997-02-14 17:32:01-08 + | 1997-02-15 17:32:01-08 + | 1997-02-16 17:32:01-08 + | 0097-02-16 17:32 BC + | 0097-02-16 17:32:01 + | 0597-02-16 17:32:01 + | 1097-02-16 17:32:01 + | 1697-02-16 17:32:01 + | 1797-02-16 17:32:01 + | 1897-02-16 17:32:01 + | 1997-02-16 17:32:01-08 + | 2097-02-16 17:32:01 + | 1996-02-28 17:32:01-08 + | 1996-02-29 17:32:01-08 + | 1996-03-01 17:32:01-08 + | 1996-12-30 17:32:01-08 + | 1996-12-31 17:32:01-08 + | 1997-01-01 17:32:01-08 + | 1997-02-28 17:32:01-08 + | 1997-03-01 17:32:01-08 + | 1997-12-30 17:32:01-08 + | 1997-12-31 17:32:01-08 + | 1999-12-31 17:32:01-08 + | 2000-01-01 17:32:01-08 + | 2000-12-31 17:32:01-08 + | 2001-01-01 17:32:01-08 + | 1957-06-13 00:00:00-07 +(62 rows) + +SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL; + eight | european_iso +-------+------------------------ + | 1973-01-14 03:14:21-08 + | 1995-05-01 00:30:30-07 + | epoch + | current + | infinity + | -infinity + | 1947-05-10 23:59:12-07 + | invalid +(8 rows) + +SET DateStyle TO 'European,SQL'; +SHOW DateStyle; +NOTICE: DateStyle is SQL with European conventions +SELECT '' AS sixty_three, d1 AS european_sql FROM DATETIME_TBL; + sixty_three | european_sql +-------------+---------------------------- + | invalid + | -infinity + | infinity + | epoch + | 10/02/1997 17:32:01.00 PST + | invalid + | invalid + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:02.00 PST + | 10/02/1997 17:32:01.40 PST + | 10/02/1997 17:32:01.50 PST + | 10/02/1997 17:32:01.60 PST + | 02/01/1997 00:00:00.00 PST + | 02/01/1997 03:04:05.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/06/1997 17:32:01.00 PDT + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:00.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 09:32:01.00 PST + | 10/02/1997 09:32:01.00 PST + | 10/02/1997 09:32:01.00 PST + | 10/06/1997 18:32:01.00 PDT + | 10/02/1997 17:32:01.00 PST + | 11/02/1997 17:32:01.00 PST + | 12/02/1997 17:32:01.00 PST + | 13/02/1997 17:32:01.00 PST + | 14/02/1997 17:32:01.00 PST + | 15/02/1997 17:32:01.00 PST + | 16/02/1997 17:32:01.00 PST + | 16/02/0097 17:32 BC + | 16/02/0097 17:32:01.00 + | 16/02/0597 17:32:01.00 + | 16/02/1097 17:32:01.00 + | 16/02/1697 17:32:01.00 + | 16/02/1797 17:32:01.00 + | 16/02/1897 17:32:01.00 + | 16/02/1997 17:32:01.00 PST + | 16/02/2097 17:32:01.00 + | 28/02/1996 17:32:01.00 PST + | 29/02/1996 17:32:01.00 PST + | 01/03/1996 17:32:01.00 PST + | 30/12/1996 17:32:01.00 PST + | 31/12/1996 17:32:01.00 PST + | 01/01/1997 17:32:01.00 PST + | 28/02/1997 17:32:01.00 PST + | 01/03/1997 17:32:01.00 PST + | 30/12/1997 17:32:01.00 PST + | 31/12/1997 17:32:01.00 PST + | 31/12/1999 17:32:01.00 PST + | 01/01/2000 17:32:01.00 PST + | 31/12/2000 17:32:01.00 PST + | 01/01/2001 17:32:01.00 PST + | 13/06/1957 00:00:00.00 PDT +(62 rows) + +SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL; + eight | european_sql +-------+---------------------------- + | 14/01/1973 03:14:21.00 PST + | 01/05/1995 00:30:30.00 PDT + | epoch + | current + | infinity + | -infinity + | 10/05/1947 23:59:12.00 PDT + | invalid +(8 rows) + +RESET DateStyle; +SHOW DateStyle; +NOTICE: DateStyle is Postgres with US (NonEuropean) conventions +-- +-- formats +-- +SET DateStyle TO 'US,Postgres'; +SHOW DateStyle; +NOTICE: DateStyle is Postgres with US (NonEuropean) conventions +SELECT '' AS sixty_two, d1 AS us_postgres FROM DATETIME_TBL; + sixty_two | us_postgres +-----------+--------------------------------- + | invalid + | -infinity + | infinity + | epoch + | Mon Feb 10 17:32:01 1997 PST + | invalid + | invalid + | Mon Feb 10 17:32:01.00 1997 PST + | Mon Feb 10 17:32:02.00 1997 PST + | Mon Feb 10 17:32:01.40 1997 PST + | Mon Feb 10 17:32:01.50 1997 PST + | Mon Feb 10 17:32:01.60 1997 PST + | Thu Jan 02 00:00:00 1997 PST + | Thu Jan 02 03:04:05 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Tue Jun 10 17:32:01 1997 PDT + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:00 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 17:32:01 1997 PST + | Mon Feb 10 09:32:01 1997 PST + | Mon Feb 10 09:32:01 1997 PST + | Mon Feb 10 09:32:01 1997 PST + | Tue Jun 10 18:32:01 1997 PDT + | Mon Feb 10 17:32:01 1997 PST + | Tue Feb 11 17:32:01 1997 PST + | Wed Feb 12 17:32:01 1997 PST + | Thu Feb 13 17:32:01 1997 PST + | Fri Feb 14 17:32:01 1997 PST + | Sat Feb 15 17:32:01 1997 PST + | Sun Feb 16 17:32:01 1997 PST + | Tue Feb 16 17:32 0097 BC + | Sat Feb 16 17:32:01 0097 + | Thu Feb 16 17:32:01 0597 + | Tue Feb 16 17:32:01 1097 + | Sat Feb 16 17:32:01 1697 + | Thu Feb 16 17:32:01 1797 + | Tue Feb 16 17:32:01 1897 + | Sun Feb 16 17:32:01 1997 PST + | Sat Feb 16 17:32:01 2097 + | Wed Feb 28 17:32:01 1996 PST + | Thu Feb 29 17:32:01 1996 PST + | Fri Mar 01 17:32:01 1996 PST + | Mon Dec 30 17:32:01 1996 PST + | Tue Dec 31 17:32:01 1996 PST + | Wed Jan 01 17:32:01 1997 PST + | Fri Feb 28 17:32:01 1997 PST + | Sat Mar 01 17:32:01 1997 PST + | Tue Dec 30 17:32:01 1997 PST + | Wed Dec 31 17:32:01 1997 PST + | Fri Dec 31 17:32:01 1999 PST + | Sat Jan 01 17:32:01 2000 PST + | Sun Dec 31 17:32:01 2000 PST + | Mon Jan 01 17:32:01 2001 PST + | Thu Jun 13 00:00:00 1957 PDT +(62 rows) + +SELECT '' AS eight, f1 AS us_postgres FROM ABSTIME_TBL; + eight | us_postgres +-------+------------------------------ + | Sun Jan 14 03:14:21 1973 PST + | Mon May 01 00:30:30 1995 PDT + | epoch + | current + | infinity + | -infinity + | Sat May 10 23:59:12 1947 PDT + | invalid +(8 rows) + +SET DateStyle TO 'US,ISO'; +SELECT '' AS sixty_two, d1 AS us_iso FROM DATETIME_TBL; + sixty_two | us_iso +-----------+--------------------------- + | invalid + | -infinity + | infinity + | epoch + | 1997-02-10 17:32:01-08 + | invalid + | invalid + | 1997-02-10 17:32:01.00-08 + | 1997-02-10 17:32:02.00-08 + | 1997-02-10 17:32:01.40-08 + | 1997-02-10 17:32:01.50-08 + | 1997-02-10 17:32:01.60-08 + | 1997-01-02 00:00:00-08 + | 1997-01-02 03:04:05-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-06-10 17:32:01-07 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:00-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-06-10 18:32:01-07 + | 1997-02-10 17:32:01-08 + | 1997-02-11 17:32:01-08 + | 1997-02-12 17:32:01-08 + | 1997-02-13 17:32:01-08 + | 1997-02-14 17:32:01-08 + | 1997-02-15 17:32:01-08 + | 1997-02-16 17:32:01-08 + | 0097-02-16 17:32 BC + | 0097-02-16 17:32:01 + | 0597-02-16 17:32:01 + | 1097-02-16 17:32:01 + | 1697-02-16 17:32:01 + | 1797-02-16 17:32:01 + | 1897-02-16 17:32:01 + | 1997-02-16 17:32:01-08 + | 2097-02-16 17:32:01 + | 1996-02-28 17:32:01-08 + | 1996-02-29 17:32:01-08 + | 1996-03-01 17:32:01-08 + | 1996-12-30 17:32:01-08 + | 1996-12-31 17:32:01-08 + | 1997-01-01 17:32:01-08 + | 1997-02-28 17:32:01-08 + | 1997-03-01 17:32:01-08 + | 1997-12-30 17:32:01-08 + | 1997-12-31 17:32:01-08 + | 1999-12-31 17:32:01-08 + | 2000-01-01 17:32:01-08 + | 2000-12-31 17:32:01-08 + | 2001-01-01 17:32:01-08 + | 1957-06-13 00:00:00-07 +(62 rows) + +SELECT '' AS eight, f1 AS us_iso FROM ABSTIME_TBL; + eight | us_iso +-------+------------------------ + | 1973-01-14 03:14:21-08 + | 1995-05-01 00:30:30-07 + | epoch + | current + | infinity + | -infinity + | 1947-05-10 23:59:12-07 + | invalid +(8 rows) + +SET DateStyle TO 'US,SQL'; +SHOW DateStyle; +NOTICE: DateStyle is SQL with US (NonEuropean) conventions +SELECT '' AS sixty_two, d1 AS us_sql FROM DATETIME_TBL; + sixty_two | us_sql +-----------+---------------------------- + | invalid + | -infinity + | infinity + | epoch + | 02/10/1997 17:32:01.00 PST + | invalid + | invalid + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:02.00 PST + | 02/10/1997 17:32:01.40 PST + | 02/10/1997 17:32:01.50 PST + | 02/10/1997 17:32:01.60 PST + | 01/02/1997 00:00:00.00 PST + | 01/02/1997 03:04:05.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 06/10/1997 17:32:01.00 PDT + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:00.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 17:32:01.00 PST + | 02/10/1997 09:32:01.00 PST + | 02/10/1997 09:32:01.00 PST + | 02/10/1997 09:32:01.00 PST + | 06/10/1997 18:32:01.00 PDT + | 02/10/1997 17:32:01.00 PST + | 02/11/1997 17:32:01.00 PST + | 02/12/1997 17:32:01.00 PST + | 02/13/1997 17:32:01.00 PST + | 02/14/1997 17:32:01.00 PST + | 02/15/1997 17:32:01.00 PST + | 02/16/1997 17:32:01.00 PST + | 02/16/0097 17:32 BC + | 02/16/0097 17:32:01.00 + | 02/16/0597 17:32:01.00 + | 02/16/1097 17:32:01.00 + | 02/16/1697 17:32:01.00 + | 02/16/1797 17:32:01.00 + | 02/16/1897 17:32:01.00 + | 02/16/1997 17:32:01.00 PST + | 02/16/2097 17:32:01.00 + | 02/28/1996 17:32:01.00 PST + | 02/29/1996 17:32:01.00 PST + | 03/01/1996 17:32:01.00 PST + | 12/30/1996 17:32:01.00 PST + | 12/31/1996 17:32:01.00 PST + | 01/01/1997 17:32:01.00 PST + | 02/28/1997 17:32:01.00 PST + | 03/01/1997 17:32:01.00 PST + | 12/30/1997 17:32:01.00 PST + | 12/31/1997 17:32:01.00 PST + | 12/31/1999 17:32:01.00 PST + | 01/01/2000 17:32:01.00 PST + | 12/31/2000 17:32:01.00 PST + | 01/01/2001 17:32:01.00 PST + | 06/13/1957 00:00:00.00 PDT +(62 rows) + +SELECT '' AS eight, f1 AS us_sql FROM ABSTIME_TBL; + eight | us_sql +-------+---------------------------- + | 01/14/1973 03:14:21.00 PST + | 05/01/1995 00:30:30.00 PDT + | epoch + | current + | infinity + | -infinity + | 05/10/1947 23:59:12.00 PDT + | invalid +(8 rows) + +SET DateStyle TO 'European,Postgres'; +SHOW DateStyle; +NOTICE: DateStyle is Postgres with European conventions +INSERT INTO DATETIME_TBL VALUES('13/06/1957'); +SELECT count(*) as one FROM DATETIME_TBL WHERE d1 = 'Jun 13 1957'; + one +----- + 2 +(1 row) + +SELECT '' AS sixty_three, d1 AS european_postgres FROM DATETIME_TBL; + sixty_three | european_postgres +-------------+--------------------------------- + | invalid + | -infinity + | infinity + | epoch + | Mon 10 Feb 17:32:01 1997 PST + | invalid + | invalid + | Mon 10 Feb 17:32:01.00 1997 PST + | Mon 10 Feb 17:32:02.00 1997 PST + | Mon 10 Feb 17:32:01.40 1997 PST + | Mon 10 Feb 17:32:01.50 1997 PST + | Mon 10 Feb 17:32:01.60 1997 PST + | Thu 02 Jan 00:00:00 1997 PST + | Thu 02 Jan 03:04:05 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Tue 10 Jun 17:32:01 1997 PDT + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:00 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 17:32:01 1997 PST + | Mon 10 Feb 09:32:01 1997 PST + | Mon 10 Feb 09:32:01 1997 PST + | Mon 10 Feb 09:32:01 1997 PST + | Tue 10 Jun 18:32:01 1997 PDT + | Mon 10 Feb 17:32:01 1997 PST + | Tue 11 Feb 17:32:01 1997 PST + | Wed 12 Feb 17:32:01 1997 PST + | Thu 13 Feb 17:32:01 1997 PST + | Fri 14 Feb 17:32:01 1997 PST + | Sat 15 Feb 17:32:01 1997 PST + | Sun 16 Feb 17:32:01 1997 PST + | Tue 16 Feb 17:32 0097 BC + | Sat 16 Feb 17:32:01 0097 + | Thu 16 Feb 17:32:01 0597 + | Tue 16 Feb 17:32:01 1097 + | Sat 16 Feb 17:32:01 1697 + | Thu 16 Feb 17:32:01 1797 + | Tue 16 Feb 17:32:01 1897 + | Sun 16 Feb 17:32:01 1997 PST + | Sat 16 Feb 17:32:01 2097 + | Wed 28 Feb 17:32:01 1996 PST + | Thu 29 Feb 17:32:01 1996 PST + | Fri 01 Mar 17:32:01 1996 PST + | Mon 30 Dec 17:32:01 1996 PST + | Tue 31 Dec 17:32:01 1996 PST + | Wed 01 Jan 17:32:01 1997 PST + | Fri 28 Feb 17:32:01 1997 PST + | Sat 01 Mar 17:32:01 1997 PST + | Tue 30 Dec 17:32:01 1997 PST + | Wed 31 Dec 17:32:01 1997 PST + | Fri 31 Dec 17:32:01 1999 PST + | Sat 01 Jan 17:32:01 2000 PST + | Sun 31 Dec 17:32:01 2000 PST + | Mon 01 Jan 17:32:01 2001 PST + | Thu 13 Jun 00:00:00 1957 PDT + | Thu 13 Jun 00:00:00 1957 PDT +(63 rows) + +SELECT '' AS eight, f1 AS european_postgres FROM ABSTIME_TBL; + eight | european_postgres +-------+------------------------------ + | Sun 14 Jan 03:14:21 1973 PST + | Mon 01 May 00:30:30 1995 PDT + | epoch + | current + | infinity + | -infinity + | Sat 10 May 23:59:12 1947 PDT + | invalid +(8 rows) + +SET DateStyle TO 'European,ISO'; +SHOW DateStyle; +NOTICE: DateStyle is ISO with European conventions +SELECT '' AS sixty_three, d1 AS european_iso FROM DATETIME_TBL; + sixty_three | european_iso +-------------+--------------------------- + | invalid + | -infinity + | infinity + | epoch + | 1997-02-10 17:32:01-08 + | invalid + | invalid + | 1997-02-10 17:32:01.00-08 + | 1997-02-10 17:32:02.00-08 + | 1997-02-10 17:32:01.40-08 + | 1997-02-10 17:32:01.50-08 + | 1997-02-10 17:32:01.60-08 + | 1997-01-02 00:00:00-08 + | 1997-01-02 03:04:05-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-06-10 17:32:01-07 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:00-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 17:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-02-10 09:32:01-08 + | 1997-06-10 18:32:01-07 + | 1997-02-10 17:32:01-08 + | 1997-02-11 17:32:01-08 + | 1997-02-12 17:32:01-08 + | 1997-02-13 17:32:01-08 + | 1997-02-14 17:32:01-08 + | 1997-02-15 17:32:01-08 + | 1997-02-16 17:32:01-08 + | 0097-02-16 17:32 BC + | 0097-02-16 17:32:01 + | 0597-02-16 17:32:01 + | 1097-02-16 17:32:01 + | 1697-02-16 17:32:01 + | 1797-02-16 17:32:01 + | 1897-02-16 17:32:01 + | 1997-02-16 17:32:01-08 + | 2097-02-16 17:32:01 + | 1996-02-28 17:32:01-08 + | 1996-02-29 17:32:01-08 + | 1996-03-01 17:32:01-08 + | 1996-12-30 17:32:01-08 + | 1996-12-31 17:32:01-08 + | 1997-01-01 17:32:01-08 + | 1997-02-28 17:32:01-08 + | 1997-03-01 17:32:01-08 + | 1997-12-30 17:32:01-08 + | 1997-12-31 17:32:01-08 + | 1999-12-31 17:32:01-08 + | 2000-01-01 17:32:01-08 + | 2000-12-31 17:32:01-08 + | 2001-01-01 17:32:01-08 + | 1957-06-13 00:00:00-07 + | 1957-06-13 00:00:00-07 +(63 rows) + +SELECT '' AS eight, f1 AS european_iso FROM ABSTIME_TBL; + eight | european_iso +-------+------------------------ + | 1973-01-14 03:14:21-08 + | 1995-05-01 00:30:30-07 + | epoch + | current + | infinity + | -infinity + | 1947-05-10 23:59:12-07 + | invalid +(8 rows) + +SET DateStyle TO 'European,SQL'; +SHOW DateStyle; +NOTICE: DateStyle is SQL with European conventions +SELECT '' AS sixty_three, d1 AS european_sql FROM DATETIME_TBL; + sixty_three | european_sql +-------------+---------------------------- + | invalid + | -infinity + | infinity + | epoch + | 10/02/1997 17:32:01.00 PST + | invalid + | invalid + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:02.00 PST + | 10/02/1997 17:32:01.40 PST + | 10/02/1997 17:32:01.50 PST + | 10/02/1997 17:32:01.60 PST + | 02/01/1997 00:00:00.00 PST + | 02/01/1997 03:04:05.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/06/1997 17:32:01.00 PDT + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:00.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 17:32:01.00 PST + | 10/02/1997 09:32:01.00 PST + | 10/02/1997 09:32:01.00 PST + | 10/02/1997 09:32:01.00 PST + | 10/06/1997 18:32:01.00 PDT + | 10/02/1997 17:32:01.00 PST + | 11/02/1997 17:32:01.00 PST + | 12/02/1997 17:32:01.00 PST + | 13/02/1997 17:32:01.00 PST + | 14/02/1997 17:32:01.00 PST + | 15/02/1997 17:32:01.00 PST + | 16/02/1997 17:32:01.00 PST + | 16/02/0097 17:32 BC + | 16/02/0097 17:32:01.00 + | 16/02/0597 17:32:01.00 + | 16/02/1097 17:32:01.00 + | 16/02/1697 17:32:01.00 + | 16/02/1797 17:32:01.00 + | 16/02/1897 17:32:01.00 + | 16/02/1997 17:32:01.00 PST + | 16/02/2097 17:32:01.00 + | 28/02/1996 17:32:01.00 PST + | 29/02/1996 17:32:01.00 PST + | 01/03/1996 17:32:01.00 PST + | 30/12/1996 17:32:01.00 PST + | 31/12/1996 17:32:01.00 PST + | 01/01/1997 17:32:01.00 PST + | 28/02/1997 17:32:01.00 PST + | 01/03/1997 17:32:01.00 PST + | 30/12/1997 17:32:01.00 PST + | 31/12/1997 17:32:01.00 PST + | 31/12/1999 17:32:01.00 PST + | 01/01/2000 17:32:01.00 PST + | 31/12/2000 17:32:01.00 PST + | 01/01/2001 17:32:01.00 PST + | 13/06/1957 00:00:00.00 PDT + | 13/06/1957 00:00:00.00 PDT +(63 rows) + +SELECT '' AS eight, f1 AS european_sql FROM ABSTIME_TBL; + eight | european_sql +-------+---------------------------- + | 14/01/1973 03:14:21.00 PST + | 01/05/1995 00:30:30.00 PDT + | epoch + | current + | infinity + | -infinity + | 10/05/1947 23:59:12.00 PDT + | invalid +(8 rows) + +RESET DateStyle; +SHOW DateStyle; +NOTICE: DateStyle is Postgres with US (NonEuropean) conventions diff --git a/src/test/regress/expected/tinterval-solaris-1947.out b/src/test/regress/expected/tinterval-solaris-1947.out new file mode 100644 index 00000000000..98e5e9ae66b --- /dev/null +++ b/src/test/regress/expected/tinterval-solaris-1947.out @@ -0,0 +1,168 @@ +-- +-- TINTERVAL +-- +CREATE TABLE TINTERVAL_TBL (f1 tinterval); +INSERT INTO TINTERVAL_TBL (f1) + VALUES ('["-infinity" "infinity"]'); +INSERT INTO TINTERVAL_TBL (f1) + VALUES ('["May 10, 1947 23:59:12" "Jan 14, 1973 03:14:21"]'); +INSERT INTO TINTERVAL_TBL (f1) + VALUES ('["Sep 4, 1983 23:59:12" "Oct 4, 1983 23:59:12"]'); +INSERT INTO TINTERVAL_TBL (f1) + VALUES ('["epoch" "Mon May 1 00:30:30 1995"]'); +INSERT INTO TINTERVAL_TBL (f1) + VALUES ('["Feb 15 1990 12:15:03" "current"]'); +-- badly formatted tintervals +INSERT INTO TINTERVAL_TBL (f1) + VALUES ('["bad time specifications" ""]'); +ERROR: Bad abstime external representation 'bad time specifications' +INSERT INTO TINTERVAL_TBL (f1) + VALUES ('["" "infinity"]'); +ERROR: Bad abstime external representation '' +-- test tinterval operators +SELECT '' AS five, TINTERVAL_TBL.*; + five | f1 +------+----------------------------------------------------------------- + | ["-infinity" "infinity"] + | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] + | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] + | ["Thu Feb 15 12:15:03 1990 PST" "current"] +(5 rows) + +-- length == +SELECT '' AS one, t.* + FROM TINTERVAL_TBL t + WHERE t.f1 #= '@ 1 months'; + one | f1 +-----+----------------------------------------------------------------- + | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] +(1 row) + +-- length <> +SELECT '' AS three, t.* + FROM TINTERVAL_TBL t + WHERE t.f1 #<> '@ 1 months'; + three | f1 +-------+----------------------------------------------------------------- + | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] + | ["Thu Feb 15 12:15:03 1990 PST" "current"] +(3 rows) + +-- length < +SELECT '' AS zero, t.* + FROM TINTERVAL_TBL t + WHERE t.f1 #< '@ 1 month'; + zero | f1 +------+---- +(0 rows) + +-- length <= +SELECT '' AS one, t.* + FROM TINTERVAL_TBL t + WHERE t.f1 #<= '@ 1 month'; + one | f1 +-----+----------------------------------------------------------------- + | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] +(1 row) + +-- length > +SELECT '' AS three, t.* + FROM TINTERVAL_TBL t + WHERE t.f1 #> '@ 1 year'; + three | f1 +-------+----------------------------------------------------------------- + | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] + | ["Thu Feb 15 12:15:03 1990 PST" "current"] +(3 rows) + +-- length >= +SELECT '' AS three, t.* + FROM TINTERVAL_TBL t + WHERE t.f1 #>= '@ 3 years'; + three | f1 +-------+----------------------------------------------------------------- + | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] + | ["Thu Feb 15 12:15:03 1990 PST" "current"] +(3 rows) + +-- overlaps +SELECT '' AS three, t1.* + FROM TINTERVAL_TBL t1 + WHERE t1.f1 && + tinterval '["Aug 15 14:23:19 1983" "Sep 16 14:23:19 1983"]'; + three | f1 +-------+----------------------------------------------------------------- + | ["-infinity" "infinity"] + | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] +(3 rows) + +SET geqo TO 'off'; +SELECT '' AS five, t1.f1, t2.f1 + FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2 + WHERE t1.f1 && t2.f1 and + t1.f1 = t2.f1 + ORDER BY t1.f1, t2.f1; + five | f1 | f1 +------+-----------------------------------------------------------------+----------------------------------------------------------------- + | ["-infinity" "infinity"] | ["-infinity" "infinity"] + | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] + | ["Thu Feb 15 12:15:03 1990 PST" "current"] | ["Thu Feb 15 12:15:03 1990 PST" "current"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] | ["epoch" "Mon May 01 00:30:30 1995 PDT"] + | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] +(5 rows) + +SELECT '' AS fourteen, t1.f1 AS interval1, t2.f1 AS interval2 + FROM TINTERVAL_TBL t1, TINTERVAL_TBL t2 + WHERE t1.f1 && t2.f1 and not t1.f1 = t2.f1 + ORDER BY interval1, interval2; + fourteen | interval1 | interval2 +----------+-----------------------------------------------------------------+----------------------------------------------------------------- + | ["-infinity" "infinity"] | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] + | ["-infinity" "infinity"] | ["Thu Feb 15 12:15:03 1990 PST" "current"] + | ["-infinity" "infinity"] | ["epoch" "Mon May 01 00:30:30 1995 PDT"] + | ["-infinity" "infinity"] | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] + | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] | ["-infinity" "infinity"] + | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] | ["epoch" "Mon May 01 00:30:30 1995 PDT"] + | ["Thu Feb 15 12:15:03 1990 PST" "current"] | ["-infinity" "infinity"] + | ["Thu Feb 15 12:15:03 1990 PST" "current"] | ["epoch" "Mon May 01 00:30:30 1995 PDT"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] | ["-infinity" "infinity"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] | ["Thu Feb 15 12:15:03 1990 PST" "current"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] + | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] | ["-infinity" "infinity"] + | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] | ["epoch" "Mon May 01 00:30:30 1995 PDT"] +(14 rows) + +-- contains +SELECT '' AS five, t1.f1 + FROM TINTERVAL_TBL t1 + WHERE not t1.f1 << + tinterval '["Aug 15 14:23:19 1980" "Sep 16 14:23:19 1990"]' + ORDER BY t1.f1; + five | f1 +------+----------------------------------------------------------------- + | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] + | ["Thu Feb 15 12:15:03 1990 PST" "current"] + | ["Sat May 10 23:59:12 1947 PDT" "Sun Jan 14 03:14:21 1973 PST"] +(3 rows) + +-- make time interval +SELECT '' AS three, t1.f1 + FROM TINTERVAL_TBL t1 + WHERE t1.f1 && + (abstime 'Aug 15 14:23:19 1983' <#> + abstime 'Sep 16 14:23:19 1983') + ORDER BY t1.f1; + three | f1 +-------+----------------------------------------------------------------- + | ["-infinity" "infinity"] + | ["Sun Sep 04 23:59:12 1983 PDT" "Tue Oct 04 23:59:12 1983 PDT"] + | ["epoch" "Mon May 01 00:30:30 1995 PDT"] +(3 rows) + +RESET geqo; diff --git a/src/test/regress/resultmap b/src/test/regress/resultmap index 8e7db2cdabb..94aea1471ca 100644 --- a/src/test/regress/resultmap +++ b/src/test/regress/resultmap @@ -4,7 +4,13 @@ int2/.*-netbsd=int2-too-large int4/.*-netbsd=int4-too-large int2/i.86-pc-linux-gnulibc=int2-not-representable int4/i.86-pc-linux-gnulibc=int4-not-representable +int2/sparc-sun-solaris=int2-too-large +int4/sparc-sun-solaris=int4-too-large geometry/hppa=geometry-positive-zeros geometry/.*-netbsd=geometry-positive-zeros geometry/i.86-.*-gnulibc=geometry-i86-gnulibc +geometry/sparc-sun-solaris=geometry-solaris-precision horology/hppa=horology-no-DST-before-1970 +horology/sparc-sun-solaris=horology-solaris-1947 +abstime/sparc-sun-solaris=abstime-solaris-1947 +tinterval/sparc-sun-solaris=tinterval-solaris-1947 |