> -----Original Message----- > > > > Every feature and function in PostgreSQL is "potentially dangerous" > > - understanding them and using them correctly is the responsibility > > of the programmer. Time handling has lots of subtleties that take > > time to digest > > > > > > Thanks for the advice. But it's precisely in the role of a programmer > > who has digested a good deal about date-time data and its subtleties, > > and who is trying to use in a consistent an robust way date-time data > > that I'm asking this question. Or rather, reporting this issue. > > > > . It appears that you would like a timestamp of 2011-12-30 00:30:00 > > which you can get. But even so, there are places in the world where > > that time exists and other places in the world that it does not. > > > > If you try to force that timestamp into a zone where it doesn't > > exist, PostgreSQL makes a reasonable interpretation of the intended > > point in time. > > > > > > I strongly disagree. I'm not trying "to force that timestamp into a > > zone" at all. I'm just telling postgresl to parse the string > > '30/12/2011 00:30:00' as a TIMESTAMP (without time zone), that is, to > > parse/understand/store it as the abstract/civil (wall calendar+clock) > > local datetime "30 dec 2011, 00 30 00 am" with NO association with a > > timezone. OK, let us try a different approach (summary located at the end for convenience) If you ignore TimeZone then you are saying "EVERY (well-formed) TIME STRING IS VALID". It is only within a TimeZone that specific times can be considered "Invalid" according to the rules for that TimeZone. Or rather, in the absence of TimeZone rules all times are valid. However, to_timestamp(text,text) is a "TimeZone" dependent function and so you cannot pass in a time that is invalid in the "current" TimeZone. The alternative to using "to_timestamp(text,text)" is to "SET datestyle = ..." and using casting; which while valid, is yet another hoop to jump through by the programmer. Since the to_timestamp(text, text) function exists (which could also be emulated by using "SET datestyle") apparently the practice of requiring "SET datestyle" was determined undue hardship and thus the function was written. Fine. If you DO pass in a time that is invalid you can either throw an error or coerce the value into a valid time. Neither decision is best and it would be nice to give the user the ability to choose the desired behavior. Also, the option to "warn" instead of error would be nice. But, given the existing decision to coerce (without warning) instead of throwing an exception the question comes down to: Is there a TimeZone in which ALL TIMES ARE VALID? If there is not then any input into "to_timestamp(text, text)" is potentially invalid and thus at risk for SILENT COERCION to an invalid value. If that is the case; what is the proper and fool-proof method to get the "invalid" timestamp back? If there is you still require the user to both know which TimeZone that is and to issue a "SET TIMEZONE" prior to any call of "to_timestamp(text, text)" if they do indeed want to treat all "well-formed time strings" as being valid. In the example, "December 30, 2007 12:30:00 AM" is not a valid DateTime in "Buenos_Aires" and so the coercion occurs and "1:30:00 AM" is returned instead. The same time in 2006 and 2008 ARE valid and so the issue does not appear if you use those data points. This itself seems strange but as I am not from Argentina whether this is correct or buggy behavior I cannot say. Mind you I am running 9.0.4 on Windows 7 64bit. PostgreSQL currently does not offer a to_timestamp(text, text) like-function that evaluates/returns a simple "Timestamp" as opposed to a "TimestampTZ" - using the supplied function always evaluates the input time relative to the in-session TimeZone and thus could cause the time to be coerced if the input time is invalid in that TimeZone. Even if you are afraid to change the behavior of the existing to_timestamp(text, text) function having TimeZone agnostic functions, that always evaluate relative to the "Safe" TimeZone, and output a "Timestamp Without Time Zone" seems reasonable to avoid having people code a custom function just so they can "SET TIMEZONE = 'UTC'" prior to calling the existing to_timestamp(text, text) function. I do think throwing a warning during coercion would be nice so at least those programs relying on to_timestamp(text, text) will know WHEN it happens and can react accordingly. As for "Time handling has lots of subtleties that take time to digest"; a good programmer and API do their best to minimize the number of hidden subtleties to be learned. Even if the SQL standard doesn't properly address the issue doesn't mean the PostgreSQL implementation shouldn't strive to do things better and make life easier for the programmer. Reading paragraphs of text to learn how something works (and how to work around its limitations) is not as good as seeing multiple groups of functions that are all similar but in which each group provides unique abilities and restrictions. In this case seeing "to_timestamp_local(text, text)" and "to_timestamp(text, text)" would make it much more obvious to the user that special considerations are present when dealing with "timestamptz" (not all times are valid) compared to "timestamp" (all times are considered valid) and that the necessary code to implement the specific behavior is hidden behind each function - whatever that code is. I would maybe even add a "to_timestamp_strict(text, text)" function which handles timestamptz values but errors instead of coercing. The "to_timestamp(text, text)" would coerce but would RAISE NOTICE when it happens so those who care (or who are oblivious) will know about it. I cannot imagine that so many invalid timestamptz values are input as to "overflow the logs" even if no-one is looking - and since it is a runtime data issue there really isn't an external tool that can look at the static database and evaluate things "out-of-transaction". I know exploding the API can be just as bad as having too minimal of one but just from the fact that the defenders of the status-quo feel that "Time handling has lots of subtleties..." means that efforts to make it less subtle are warranted. Yes, I do sit in the Peanut Gallery but, like the OP, am not a novice when it comes to programming; and for much of that last paragraph I am liberally regurgitating from reading/learning. Workarounds: 1) SET datestyle = 'DMY'; + use "CAST ( '30/12/2007 00:30:00' AS timestamp )"; at this moment this is the most direct (and possibly only) way to do this [not positive all formats can be represented however...]; Casting is fairly smart (I think) so you really just need to get the date ORDER correct as opposed to explicitly specifying the format. Time should be fairly straight forward as order does not normally come into play. 2) SET TIMEZONE = 'Some Safe TimeZone Where All Times Are Valid' + to_timestamp(text, text)::timestamp; if you are going to use "SET" you might as well just change "datestyle" if your desired format can be represented in that form 2a) If no "Safe" TimeZone exists then either a "correction algorithm" is required or the only option is to "SET datestyle" and CAST 3) Consider other "SET" possibilities (lc_time, intervalstyle) for more precision [not tested] Solution: 1) Add "to_timestamp_local(text, text) -> timestamp" function which does the same thing as "to_timestamp(text, text) -> timestamptz" but in a TimeZone agnostic way Modifications: 1) Add "to_timestamp_strict(text, text) -> timestamptz" function that errors instead of coercing 2) Modify "to_timestamp(text, text) -> timestamptz" to emit a warning whenever a coercion occurs David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
