On 02/27/2017 07:03 AM, Sven R. Kunze wrote:
On 27.02.2017 12:10, Geoff Winkless wrote:
On 27 February 2017 at 10:52, Sven R. Kunze <srkunze@xxxxxxx
<mailto:srkunze@xxxxxxx>>wrote:
So, what can I do to parse texts to date(times) in a safe manner?
You know best the format of your data; if you know that your date
field is always in a particular style and timezone, you can write a
function that can be considered safe to set IMMUTABLE, where a more
generic system todate function cannot.
It might be sensible to call the function something that describes it
exactly, rather than my_to_date you could call it utc_yyyymmdd_todate
or something, just in case someone comes along later and sees an
immutable todate function and thinks they can use it for something else.
Geoff
Thanks, Geoff. It's not the most convenient way to define an index to
define a function first and that all future queries need to use that
very function in order to utilize the index. Though, it's the most
safest and best documenting way.
So, I got two additional questions:
Why is this relevant for dates? I cannot see that dates are
timezone-influenced.
Per Tom's post, see points 2 & 3:
"* some of them depend on the current timezone (but I don't believe
date_in does);
* all of them depend on the current datestyle setting, eg to resolve
'02/03/2017';
* all of them accept strings with time-varying values, such as 'now'
or 'today'.
You could get around the second and third points with to_timestamp(),
but since the only variant of that is one that yields timestamptz and
hence is affected by the timezone setting, it's still not immutable.
"
I still feel that a function is overkill for a simple text to date
conversion. Couldn't there be an IMMUTABLE modifier for an expression to
mark it as immutable?
"SELECT '2007-02-02'::date;" just works. It would be great if one could
define an index with the same ease. I already can see how our
application developers need constant reminders that "in case of dates,
use 'magic_function' first". If they don't, the application will suffer
from bad performance.
Thanks in advance for your replies.
Regards,
Sven
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
