summaryrefslogtreecommitdiff
path: root/src/test/regress/expected/timestamptz.out
Commit message (Collapse)AuthorAgeFilesLines
* Add functions to do timestamptz arithmetic in a non-default timezone.Tom Lane2023-03-181-0/+54
| | | | | | | | | | | | | | | | | | Add versions of timestamptz + interval, timestamptz - interval, and generate_series(timestamptz, ...) in which a timezone can be specified explicitly instead of defaulting to the TimeZone GUC setting. The new functions for the first two are named date_add and date_subtract. This might seem too generic, but we could use overloading to add additional variants if that seems useful. Along the way, improve the docs' pretty inadequate explanation of how timestamptz +- interval works. Przemysław Sztoch and Gurjeet Singh; cosmetic changes and most of the docs work by me Discussion: https://postgr.es/m/01a84551-48dd-1359-bf7e-f6b0203a6bd0@sztoch.pl
* Rework pg_input_error_message(), now renamed pg_input_error_info()Michael Paquier2023-02-281-8/+8
| | | | | | | | | | | | | | | | | | | pg_input_error_info() is now a SQL function able to return a row with more than just the error message generated for incorrect data type inputs when these are able to handle soft failures, returning more contents of ErrorData, as of: - The error message (same as before). - The error detail, if set. - The error hint, if set. - SQL error code. All the regression tests that relied on pg_input_error_message() are updated to reflect the effects of the rename. Per discussion with Tom Lane and Andrew Dunstan. Author: Nathan Bossart Discussion: https://postgr.es/m/139a68e1-bd1f-a9a7-b5fe-0be9845c6311@dunslane.net
* Detect overflow in timestamp[tz] subtraction.Tom Lane2023-02-201-0/+9
| | | | | | | | | | It's possible to overflow the int64 microseconds field of the output interval when subtracting two timestamps. Detect that instead of silently returning a bogus result. Nick Babadzhanian Discussion: https://postgr.es/m/CABw73Uq2oJ3E+kYvvDuY04EkhhkChim2e-PaghBDjOmgUAMWGw@mail.gmail.com
* Accept "+infinity" in date and timestamp[tz] input.Tom Lane2023-01-011-0/+6
| | | | | | | | | The float and numeric types accept this variant spelling of "infinity", so it seems like the datetime types should too. Vik Fearing, some cosmetic mods by me Discussion: https://postgr.es/m/d0bef637-2dbd-0a5d-e539-48243b6f6c5e@postgresfriends.org
* Convert datetime input functions to use "soft" error reporting.Tom Lane2022-12-091-0/+31
| | | | | | | | | This patch converts the input functions for date, time, timetz, timestamp, timestamptz, and interval to the new soft-error style. There's some related stuff in formatting.c that remains to be cleaned up, but that seems like a separable project. Discussion: https://postgr.es/m/3bbbb0df-7382-bf87-9737-340ba096e034@postgrespro.ru
* Revert "Disallow infinite endpoints in generate_series() for timestamps."Tom Lane2022-05-091-8/+19
| | | | | | | | | | | | | | | | | | | This reverts commit eafdf9de06e9b60168f5e47cedcfceecdc6d4b5f and its back-branch counterparts. Corey Huinker pointed out that we'd discussed this exact change back in 2016 and rejected it, on the grounds that there's at least one usage pattern with LIMIT where an infinite endpoint can usefully be used. Perhaps that argument needs to be re-litigated, but there's no time left before our back-branch releases. To keep our options open, restore the status quo ante; if we do end up deciding to change things, waiting one more quarter won't hurt anything. Rather than just doing a straight revert, I added a new test case demonstrating the usage with LIMIT. That'll at least remind us of the issue if we forget again. Discussion: https://postgr.es/m/3603504.1652068977@sss.pgh.pa.us Discussion: https://postgr.es/m/CADkLM=dzw0Pvdqp5yWKxMd+VmNkAMhG=4ku7GnCZxebWnzmz3Q@mail.gmail.com
* Disallow infinite endpoints in generate_series() for timestamps.Tom Lane2022-04-201-0/+49
| | | | | | | | | | Such cases will lead to infinite loops, so they're of no practical value. The numeric variant of generate_series() already threw error for this, so borrow its message wording. Per report from Richard Wesley. Back-patch to all supported branches. Discussion: https://postgr.es/m/91B44E7B-68D5-448F-95C8-B4B3B0F5DEAF@duckdblabs.com
* Support "of", "tzh", and "tzm" format codes.Robert Haas2022-03-141-0/+65
| | | | | | | | | | | | The upper case versions "OF", "TZH", and "TZM" are already supported, and all other format codes that are supported in upper case are also supported in lower case, so we should support these as well for consistency. Nitin Jadhav, with a tiny cosmetic change by me. Reviewed by Suraj Kharage and David Zhang. Discussion: http://postgr.es/m/CAMm1aWZ-oZyKd75+8D=VJ0sAoSwtdXWLP-MAWD4D8R1Dgandzw@mail.gmail.com
* Disallow negative strides in date_bin()John Naylor2021-07-281-1/+4
| | | | | | | | | | | It's not clear what the semantics of negative strides would be, so throw an error instead. Per report from Bauyrzhan Sakhariyev Reviewed-by: Tom Lane, Michael Paquier Discussion: https://www.postgresql.org/message-id/CAKpL73vZmLuFVuwF26FJ%2BNk11PVHhAnQRoREFcA03x7znRoFvA%40mail.gmail.com Backpatch to v14
* Fix division by zero error in date_binJohn Naylor2021-07-221-0/+3
| | | | | | Bauyrzhan Sakhariyev, via Github Backpatch to v14
* Change return type of EXTRACT to numericPeter Eisentraut2021-04-061-0/+92
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The previous implementation of EXTRACT mapped internally to date_part(), which returned type double precision (since it was implemented long before the numeric type existed). This can lead to imprecise output in some cases, so returning numeric would be preferrable. Changing the return type of an existing function is a bit risky, so instead we do the following: We implement a new set of functions, which are now called "extract", in parallel to the existing date_part functions. They work the same way internally but use numeric instead of float8. The EXTRACT construct is now mapped by the parser to these new extract functions. That way, dumps of views etc. from old versions (which would use date_part) continue to work unchanged, but new uses will map to the new extract functions. Additionally, the reverse compilation of EXTRACT now reproduces the original syntax, using the new mechanism introduced in 40c24bfef92530bd846e111c1742c2a54441c62c. The following minor changes of behavior result from the new implementation: - The column name from an isolated EXTRACT call is now "extract" instead of "date_part". - Extract from date now rejects inappropriate field names such as HOUR. It was previously mapped internally to extract from timestamp, so it would silently accept everything appropriate for timestamp. - Return values when extracting fields with possibly fractional values, such as second and epoch, now have the full scale that the value has internally (so, for example, '1.000000' instead of just '1'). Reported-by: Petr Fedorov <petr.fedorov@phystech.edu> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://www.postgresql.org/message-id/flat/42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu
* Add tests for date_part of epoch near upper bound of timestamp rangePeter Eisentraut2021-03-301-0/+7
| | | | | This exercises a special case in the implementations of date_part('epoch', timestamp[tz]) that was previously not tested.
* Add upper boundary tests for timestamp and timestamptz typesPeter Eisentraut2021-03-301-2/+21
| | | | | | | | The existing regression tests only tested the lower boundary of the range supported by the timestamp and timestamptz types because "The upper boundary differs between integer and float timestamps, so no check". Since this is obsolete, add similar tests for the upper boundary.
* Clean up date_part tests a bitPeter Eisentraut2021-03-291-69/+70
| | | | | | Some tests for timestamp and timestamptz were in the date.sql test file. Move them to their appropriate files, or drop tests cases that were already present there.
* Add date_bin functionPeter Eisentraut2021-03-241-0/+66
| | | | | | | | | | | | Similar to date_trunc, but allows binning by an arbitrary interval rather than just full units. Author: John Naylor <john.naylor@enterprisedb.com> Reviewed-by: David Fetter <david@fetter.org> Reviewed-by: Isaac Morland <isaac.morland@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Reviewed-by: Artur Zakirov <zaartur@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CACPNZCt4buQFRgy6DyjuZS-2aPDpccRkrJBmgUfwYc1KiaXYxg@mail.gmail.com
* Clean up ancient test stylePeter Eisentraut2020-12-151-1185/+1185
| | | | | | | | | | | | | | | | Many older tests where written in a style like SELECT '' AS two, i.* FROM INT2_TBL where the first column indicated the number of expected result rows. This has gotten increasingly out of date, as the test data fixtures have expanded, so a lot of these were wrong and misleading. Moreover, this style isn't really necessary, since the psql output already shows the number of result rows. To clean this up, remove all those extra columns. Discussion: https://www.postgresql.org/message-id/flat/1a25312b-2686-380d-3c67-7a69094a999f%40enterprisedb.com
* Improve our ability to regurgitate SQL-syntax function calls.Tom Lane2020-11-041-3/+3
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | The SQL spec calls out nonstandard syntax for certain function calls, for example substring() with numeric position info is supposed to be spelled "SUBSTRING(string FROM start FOR count)". We accept many of these things, but up to now would not print them in the same format, instead simplifying down to "substring"(string, start, count). That's long annoyed me because it creates an interoperability problem: we're gratuitously injecting Postgres-specific syntax into what might otherwise be a perfectly spec-compliant view definition. However, the real reason for addressing it right now is to support a planned change in the semantics of EXTRACT() a/k/a date_part(). When we switch that to returning numeric, we'll have the parser translate EXTRACT() to some new function name (might as well be "extract" if you ask me) and then teach ruleutils.c to reverse-list that per SQL spec. In this way existing calls to date_part() will continue to have the old semantics. To implement this, invent a new CoercionForm value COERCE_SQL_SYNTAX, and make the parser insert that rather than COERCE_EXPLICIT_CALL when the input has SQL-spec decoration. (But if the input has the form of a plain function call, continue to mark it COERCE_EXPLICIT_CALL, even if it's calling one of these functions.) Then ruleutils.c recognizes COERCE_SQL_SYNTAX as a cue to emit SQL call syntax. It can know which decoration to emit using hard-wired knowledge about the functions that could be called this way. (While this solution isn't extensible without manual additions, neither is the grammar, so this doesn't seem unmaintainable.) Notice that this solution will reverse-list a function call with SQL decoration only if it was entered that way; so dump-and-reload will not by itself produce any changes in the appearance of views. This requires adding a CoercionForm field to struct FuncCall. (I couldn't resist the temptation to rearrange that struct's field order a tad while I was at it.) FuncCall doesn't appear in stored rules, so that change isn't a reason for a catversion bump, but I did one anyway because the new enum value for CoercionForm fields could confuse old backend code. Possible future work: * Perhaps CoercionForm should now be renamed to DisplayForm, or something like that, to reflect its more general meaning. This'd require touching a couple hundred places, so it's not clear it's worth the code churn. * The SQLValueFunction node type, which was invented partly for the same goal of improving SQL-compatibility of view output, could perhaps be replaced with regular function calls marked with COERCE_SQL_SYNTAX. It's unclear if this would be a net code savings, however. Discussion: https://postgr.es/m/42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu
* Avoid low-probability regression test failures in timestamp[tz] tests.Tom Lane2019-12-221-16/+25
| | | | | | | | | | | | | | | | | | | If the first transaction block in these tests were entered exactly at midnight (California time), they'd report a bogus failure due to 'now' and 'midnight' having the same values. Commit 8c2ac75c5 had dismissed this as being of negligible probability, but we've now seen it happen in the buildfarm, so let's prevent it. We can get pretty much the same test coverage without an it's-not-midnight assumption by moving the does-'now'-work cases into their own test step. While here, apply commit 47169c255's s/DELETE/TRUNCATE/ change to timestamptz as well as timestamp (not sure why that didn't occur to me at the time; the risk of failure is the same). Back-patch to all supported branches, since the main point is to get rid of potential buildfarm failures. Discussion: https://postgr.es/m/14821.1577031117@sss.pgh.pa.us
* Fix EXTRACT(ISOYEAR FROM timestamp) for years BC.Tom Lane2019-12-121-1/+1
| | | | | | | | | The test cases added by commit 26ae3aa80 exposed an old oversight in timestamp[tz]_part: they didn't correct the result of date2isoyear() for BC years, so that we produced an off-by-one answer for such years. Fix that, and back-patch to all supported branches. Discussion: https://postgr.es/m/SG2PR06MB37762CAE45DB0F6CA7001EA9B6550@SG2PR06MB3776.apcprd06.prod.outlook.com
* Remove redundant function calls in timestamp[tz]_part().Tom Lane2019-12-121-184/+367
| | | | | | | | | | | | | | | | | | | | The DTK_DOW/DTK_ISODOW and DTK_DOY switch cases in timestamp_part() and timestamptz_part() contained calls of timestamp2tm() that were fully redundant with the ones done just above the switch. This evidently crept in during commit 258ee1b63, which relocated that code from another place where the calls were indeed needed. Just delete the redundant calls. I (tgl) noted that our test coverage of these functions left quite a bit to be desired, so extend timestamp.sql and timestamptz.sql to cover all the branches. Back-patch to all supported branches, as the previous commit was. There's no real issue here other than some wasted cycles in some not-too-heavily-used code paths, but the test coverage seems valuable. Report and patch by Li Japin; test case adjustments by me. Discussion: https://postgr.es/m/SG2PR06MB37762CAE45DB0F6CA7001EA9B6550@SG2PR06MB3776.apcprd06.prod.outlook.com
* Support for FF1-FF6 datetime format patternsAlexander Korotkov2019-09-161-0/+15
| | | | | | | | | | | | | | | | | | | | SQL Standard 2016 defines FF1-FF9 format patters for fractions of seconds in jsonpath .datetime() method and CAST (... FORMAT ...) SQL clause. Parsing engine of upcoming .datetime() method will be shared with to_date()/ to_timestamp(). This patch implements FF1-FF6 format patterns for upcoming jsonpath .datetime() method. to_date()/to_timestamp() functions will also get support of this format patterns as positive side effect. FF7-FF9 are not supported due to lack of precision in our internal timestamp representation. Extracted from original patch by Nikita Glukhov, Teodor Sigaev, Oleg Bartunov. Heavily revised by me. Discussion: https://postgr.es/m/fcc6fc6a-b497-f39a-923d-aa34d0c588e8%402ndQuadrant.com Discussion: https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.com Author: Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov Reviewed-by: Anastasia Lubennikova, Peter Eisentraut
* Remove explicit error handling for obsolete date/time valuesPeter Eisentraut2019-06-301-13/+0
| | | | | | | | | | The date/time values 'current', 'invalid', and 'undefined' were removed a long time ago, but the code still contains explicit error handling for the transition. To simplify the code and avoid having to handle these values everywhere, just remove the recognition of these tokens altogether now. Reviewed-by: Michael Paquier <michael@paquier.xyz>
* Add a timezone-specific variant of date_trunc().Tom Lane2018-11-141-0/+18
| | | | | | | | | | | | | | date_trunc(field, timestamptz, zone_name) performs truncation using the named time zone as reference, rather than working in the session time zone as is the default behavior. It's equivalent to date_trunc(field, timestamptz at time zone zone_name) at time zone zone_name but it's faster, easier to type, and arguably easier to understand. Vik Fearing and Tom Lane Discussion: https://postgr.es/m/6249ffc4-2b22-4c1b-4e7d-7af84fedd7c6@2ndquadrant.com
* Deduplicate "invalid input syntax" messages for various types.Andres Freund2018-07-221-1/+1
| | | | | | | | | | | | | Previously a lot of the error messages referenced the type in the error message itself. That requires that the message is translated separately for each type. Note that currently a few smallint cases continue to reference the integer, rather than smallint, type. A later patch will create a separate routine for 16bit input. Author: Andres Freund Discussion: https://postgr.es/m/20180707200158.wpqkd7rjr4jxq5g7@alap3.anarazel.de
* Implement TZH and TZM timestamp format patternsAndrew Dunstan2018-01-091-29/+43
| | | | | | | These are compatible with Oracle and required for the datetime template language for jsonpath in an upcoming patch. Nikita Glukhov and Andrew Dunstan, reviewed by Pavel Stehule.
* Fix timestamptz regression test to still work with latest IANA zone data.Tom Lane2017-03-091-217/+107
| | | | | | | | | | | | | | | | | | | | | | | | | | | The IANA timezone crew continues to chip away at their project of removing timezone abbreviations that have no real-world currency from their database. The tzdata2017a update removes all such abbreviations for South American zones, as well as much of the Pacific. This breaks some test cases in timestamptz.sql that were expecting America/Santiago and America/Caracas to have non-numeric abbreviations. The test cases involving America/Santiago seem to have selected that zone more or less at random, so just replace it with America/New_York, which is of similar longitude. The cases involving America/Caracas are harder since they were chosen to test a time-varying zone abbreviation around a point where it changed meaning in the backwards direction. Fortunately, Europe/Moscow has a similar case in 2014, and the MSK/MSD abbreviations are well enough attested that IANA seems unlikely to decide to remove them from the database in future. With these changes, this regression test should pass when using any IANA zone database from 2015 or later. One could wish that there were a few years more daylight on how out-of-date your zone database can be ... but really the --with-system-tzdata option is only meant for use on platforms where the zone database is kept up-to-date pretty faithfully, so I do not think this is a big objection. Discussion: https://postgr.es/m/6749.1489087470@sss.pgh.pa.us
* Add a regression test script dedicated to exercising system views.Tom Lane2017-01-301-35/+0
| | | | | | | | | | | | | | | | | | | | | | | Quite a few of our built-in system views were not exercised anywhere in the regression tests. This is perhaps not so exciting for the ones that are simple projections/joins of system catalogs, but for the ones that are wrappers for set-returning C functions, the omission translates directly to lack of test coverage for those functions. In many cases, the reason for the omission is that the view doesn't have much to do with any specific SQL feature, so there's no natural place to test it. To remedy that, invent a new script sysviews.sql that's dedicated to testing SRF-based views. Move a couple of tests that did fit this charter into the new script, and add simple "count(*)" based tests of other views within the charter. That's enough to ensure we at least exercise the main code path through the SRF, although it does little to prove that the output is sane. More could be done here, no doubt, and I hope someone will think about how we can test these views more thoroughly. But this is a starting point. Discussion: https://postgr.es/m/19359.1485723741@sss.pgh.pa.us
* Disable transforms that replaced AT TIME ZONE with RelabelType.Tom Lane2017-01-181-0/+19
| | | | | | | | | | | | | | | | | | These resulted in wrong answers if the relabeled argument could be matched to an index column, as shown in bug #14504 from Evgeniy Kozlov. We might be able to resurrect these optimizations by adjusting the planner's treatment of RelabelType, or by adjusting btree's rules for selecting comparison functions, but either solution will take careful analysis and does not sound like a fit candidate for backpatching. I left the catalog infrastructure in place and just reduced the transform functions to always-return-NULL. This would be necessary anyway in the back branches, and it doesn't seem important to be more invasive in HEAD. Bug introduced by commit b8a18ad48. Back-patch to 9.5 where that came in. Report: https://postgr.es/m/20170118144828.1432.52823@wrigleys.postgresql.org Discussion: https://postgr.es/m/18771.1484759439@sss.pgh.pa.us
* Add regression test coverage for non-default timezone abbreviation sets.Tom Lane2016-09-041-0/+15
| | | | | | | | | | After further reflection about the mess cleaned up in commit 39b691f25, I decided the main bit of test coverage that was still missing was to check that the non-default abbreviation-set files we supply are usable. Add that. Back-patch to supported branches, just because it seems like a good idea to keep this all in sync.
* Don't require dynamic timezone abbreviations to match underlying time zone.Tom Lane2016-09-021-0/+20
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Previously, we threw an error if a dynamic timezone abbreviation did not match any abbreviation recorded in the referenced IANA time zone entry. That seemed like a good consistency check at the time, but it turns out that a number of the abbreviations in the IANA database are things that Olson and crew made up out of whole cloth. Their current policy is to remove such names in favor of using simple numeric offsets. Perhaps unsurprisingly, a lot of these made-up abbreviations have varied in meaning over time, which meant that our commit b2cbced9e and later changes made them into dynamic abbreviations. So with newer IANA database versions that don't mention these abbreviations at all, we fail, as reported in bug #14307 from Neil Anderson. It's worse than just a few unused-in-the-wild abbreviations not working, because the pg_timezone_abbrevs view stops working altogether (since its underlying function tries to compute the whole view result in one call). We considered deleting these abbreviations from our abbreviations list, but the problem with that is that we can't stay ahead of possible future IANA changes. Instead, let's leave the abbreviations list alone, and treat any "orphaned" dynamic abbreviation as just meaning the referenced time zone. It will behave a bit differently than it used to, in that you can't any longer override the zone's standard vs. daylight rule by using the "wrong" abbreviation of a pair, but that's better than failing entirely. (Also, this solution can be interpreted as adding a small new feature, which is that any abbreviation a user wants can be defined as referencing a time zone name.) Back-patch to all supported branches, since this problem affects all of them when using tzdata 2016f or newer. Report: <20160902031551.15674.67337@wrigleys.postgresql.org> Discussion: <6189.1472820913@sss.pgh.pa.us>
* Remove just-added tests for to_timestamp(float8) with out-of-range inputs.Tom Lane2016-03-291-7/+1
| | | | | | | | | | Reporting the specific out-of-range input value produces platform-dependent results. We could skip reporting the value, but that's contrary to our message style guidelines and unhelpful to users. Or we could add a separate expected-output file for Windows, but that would be a substantial maintenance burden, and these test cases seem unlikely to be worth it. Per buildfarm.
* Allow to_timestamp(float8) to convert float infinity to timestamp infinity.Tom Lane2016-03-291-0/+47
| | | | | | | | | With the original SQL-function implementation, such cases failed because we don't support infinite intervals. Converting the function to C lets us bypass the interval representation, which should be a bit faster as well as more flexible. Vitaly Burovoy, reviewed by Anastasia Lubennikova
* Fix assorted breakage in to_char()'s OF format option.Tom Lane2016-03-171-0/+51
| | | | | | | | | | | | | | | | | | In HEAD, fix incorrect field width for hours part of OF when tm_gmtoff is negative. This was introduced by commit 2d87eedc1d4468d3 as a result of falsely applying a pattern that's correct when + signs are omitted, which is not the case for OF. In 9.4, fix missing abs() call that allowed a sign to be attached to the minutes part of OF. This was fixed in 9.5 by 9b43d73b3f9bef27, but for inscrutable reasons not back-patched. In all three versions, ensure that the sign of tm_gmtoff is correctly reported even when the GMT offset is less than 1 hour. Add regression tests, which evidently we desperately need here. Thomas Munro and Tom Lane, per report from David Fetter
* Be more careful about out-of-range dates and timestamps.Tom Lane2016-03-161-0/+24
| | | | | | | | | | | | | | | | | | | | | Tighten the semantics of boundary-case timestamptz so that we allow timestamps >= '4714-11-24 00:00+00 BC' and < 'ENDYEAR-01-01 00:00+00 AD' exactly, no more and no less, but it is allowed to enter timestamps within that range using non-GMT timezone offsets (which could make the nominal date 4714-11-23 BC or ENDYEAR-01-01 AD). This eliminates dump/reload failure conditions for timestamps near the endpoints. To do this, separate checking of the inputs for date2j() from the final range check, and allow the Julian date code to handle a range slightly wider than the nominal range of the datatypes. Also add a bunch of checks to detect out-of-range dates and timestamps that formerly could be returned by operations such as date-plus-integer. All C-level functions that return date, timestamp, or timestamptz should now be proof against returning a value that doesn't pass IS_VALID_DATE() or IS_VALID_TIMESTAMP(). Vitaly Burovoy, reviewed by Anastasia Lubennikova, and substantially whacked around by me
* to_char(): Do not count negative sign as a digit for time valuesBruce Momjian2015-10-051-3/+3
| | | | | | | | For time masks, like HH24, MI, SS, CC, MM, do not count the negative sign as part of the zero-padding length specified by the mask, e.g. have to_char('-4 years'::interval, 'YY') return '-04', not '-4'. Report by Craig Ringer
* Don't require bleeding-edge timezone data in timestamptz regression test.Tom Lane2014-11-181-111/+113
| | | | | | | | | | | | | | The regression test cases added in commits b2cbced9e et al depended in part on the Russian timezone offset changes of Oct 2014. While this is of no particular concern for a default Postgres build, it was possible for a build using --with-system-tzdata to fail the tests if the system tzdata database wasn't au courant. Bjorn Munch and Christoph Berg both complained about this while packaging 9.4rc1, so we probably shouldn't insist on the system tzdata being up-to-date. Instead, make an equivalent test using a zone change that occurred in Venezuela in 2007. With this patch, the regression tests should pass using any tzdata set from 2012 or later. (I can't muster much sympathy for somebody using --with-system-tzdata on a machine whose system tzdata is more than three years out-of-date.)
* Support timezone abbreviations that sometimes change.Tom Lane2014-10-161-0/+680
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Up to now, PG has assumed that any given timezone abbreviation (such as "EDT") represents a constant GMT offset in the usage of any particular region; we had a way to configure what that offset was, but not for it to be changeable over time. But, as with most things horological, this view of the world is too simplistic: there are numerous regions that have at one time or another switched to a different GMT offset but kept using the same timezone abbreviation. Almost the entire Russian Federation did that a few years ago, and later this month they're going to do it again. And there are similar examples all over the world. To cope with this, invent the notion of a "dynamic timezone abbreviation", which is one that is referenced to a particular underlying timezone (as defined in the IANA timezone database) and means whatever it currently means in that zone. For zones that use or have used daylight-savings time, the standard and DST abbreviations continue to have the property that you can specify standard or DST time and get that time offset whether or not DST was theoretically in effect at the time. However, the abbreviations mean what they meant at the time in question (or most recently before that time) rather than being absolutely fixed. The standard abbreviation-list files have been changed to use this behavior for abbreviations that have actually varied in meaning since 1970. The old simple-numeric definitions are kept for abbreviations that have not changed, since they are a bit faster to resolve. While this is clearly a new feature, it seems necessary to back-patch it into all active branches, because otherwise use of Russian zone abbreviations is going to become even more problematic than it already was. This change supersedes the changes in commit 513d06ded et al to modify the fixed meanings of the Russian abbreviations; since we've not shipped that yet, this will avoid an undesirably incompatible (not to mention incorrect) change in behavior for timestamps between 2011 and 2014. This patch makes some cosmetic changes in ecpglib to keep its usage of datetime lookup tables as similar as possible to the backend code, but doesn't do anything about the increasingly obsolete set of timezone abbreviation definitions that are hard-wired into ecpglib. Whatever we do about that will likely not be appropriate material for back-patching. Also, a potential free() of a garbage pointer after an out-of-memory failure in ecpglib has been fixed. This patch also fixes pre-existing bugs in DetermineTimeZoneOffset() that caused it to produce unexpected results near a timezone transition, if both the "before" and "after" states are marked as standard time. We'd only ever thought about or tested transitions between standard and DST time, but that's not what's happening when a zone simply redefines their base GMT offset. In passing, update the SGML documentation to refer to the Olson/zoneinfo/ zic timezone database as the "IANA" database, since it's now being maintained under the auspices of IANA.
* Be more wary in choice of timezone names to test make_timestamptz with.Tom Lane2014-05-121-11/+5
| | | | | | | | | | | | | | America/Metlakatla hasn't been in the IANA database all that long, so some installations might not have it. It does seem worthwhile to test with a fractional-minute GMT offset, but we can get that from almost any pre-1900 date; I chose Europe/Paris, whose LMT offset from Greenwich should be pretty darn well established. Also, assuming that Mars/Mons_Olympus will never be in the IANA database seems less than future-proof, so let's use a more fanciful location for the bad-zone-name check. Per complaint from Christoph Berg.
* Constructors for interval, timestamp, timestamptzAlvaro Herrera2014-03-041-0/+114
| | | | | | Author: Pavel Stěhule, editorialized somewhat by Álvaro Herrera Reviewed-by: Tomáš Vondra, Marko Tiikkaja With input from Fabrízio de Royes Mello, Jim Nasby
* Allow 5+ digit years for non-ISO timestamp/date strings, where appropriateBruce Momjian2013-10-161-0/+22
| | | | Report from Haribabu Kommi
* Fix to_char(), to_date(), and to_timestamp() to handle negative/BCBruce Momjian2012-08-071-2/+2
| | | | | | | | century specifications just like positive/AD centuries. Previously the behavior was either wrong or inconsistent with positive/AD handling. Centuries without years now always assume the first year of the century, which is now documented.
* Remove useless whitespace at end of linesPeter Eisentraut2010-11-231-17/+12
|
* Fix to_char YYY, YY, Y format codes so that FM zero-suppression really works,Tom Lane2010-04-071-21/+21
| | | | | | | rather than only sort-of working as the previous attempt had left it. Clean up some unnecessary differences between the way these were coded and the way the YYYY case was coded. Update the regression test cases that proved that it wasn't working.
* Honor to_char() "FM" specification in YYY, YY, and Y; it was alreadyBruce Momjian2010-02-161-4/+4
| | | | | | honored by YYYY. Also document Oracle "toggle" FM behavior. Per report from Guy Rouillier
* Remove the datetime keywords ABSTIME and RELTIME, which we'd been treating asTom Lane2009-03-221-8/+4
| | | | | | | | noise words for the last twelve years, for compatibility with Berkeley-era output formatting of the special INVALID values for those datatypes. Considering that the datatypes themselves have been deprecated for awhile, this is taking backwards compatibility a little far. Per gripe from Josh Berkus.
* Clean up the ancient decision to show only two fractional-seconds digitsTom Lane2008-11-121-196/+196
| | | | | | | | | | | | | in "postgres_verbose" intervalstyle, and the equally arbitrary decision to show at least two fractional-seconds digits in most other datetime display styles. This results in some minor changes in the expected regression test outputs. Also, coalesce a lot of repetitive code in datetime.c into subroutines, for clarity and ease of maintenance. In particular this roughly halves the number of #ifdef HAVE_INT64_TIMESTAMP segments. Ron Mayer, with some additional kibitzing from Tom Lane
* Tighten up to_date/to_timestamp so that they are more likely to rejectTom Lane2008-09-111-128/+0
| | | | | | | erroneous input, rather than silently producing bizarre results as formerly happened. Brendan Jurd
* Add a bunch of new error location reports to parse-analysis error messages.Tom Lane2008-09-011-0/+16
| | | | | There are still some weak spots around JOIN USING and relation alias lists, but most errors reported within backend/parser/ now have locations.
* Adjust timestamp regression tests to prevent two low-probability failureTom Lane2008-05-251-8/+37
| | | | | | | | | | | | | | | | | | cases. Recent buildfarm experience shows that it is sometimes possible to execute several SQL commands in less time than the granularity of Windows' not-very-high-resolution gettimeofday(), leading to a failure because the tests expect the value of now() to change and it doesn't. Also, it was recognized some time ago that the same area of the tests could fail if local midnight passes between the insertion and the checking of the values for 'yesterday', 'tomorrow', etc. Clean all this up per ideas from myself and Greg Stark. There remains a window for failure if the transaction block is entered exactly at local midnight (so that 'now' and 'today' have the same value), but that seems low-probability enough to live with. Since the point of this change is mostly to eliminate buildfarm noise, back-patch to all versions we are still actively testing.
* Update timezone code to track the upstream changes since 2003. In particularTom Lane2008-02-161-0/+25
| | | | | | | | this adds support for 64-bit tzdata files, which is needed to support DST calculations beyond 2038. Add a regression test case to give some minimal confidence that that really works. Heikki Linnakangas
View Lorry Controller Status