Re: to_timestamp() and timestamp without time zone - Mailing list pgsql-general

>
> Possibly:
> test=> select (to_date('30/12/2007','DD/MM/YYYY') +
> '00:30'::time)::timestamp;
>        timestamp
> ---------------------
>   2007-12-30 00:30:00
> (1 row)
>
>

Great, so now I have to capture the date and time portion of the string
separately AND I need to use two parameters instead of one.

Contemplating and enumerating alternative solutions to the problem doesn't
change the fact that the "to_timestamp(text, text)" function exists even
though all of these alternatives work equally well for timestamptz.

A naïve (or busy) user likely will attempt to use "to_timestamp(text, text)"
when their input can be in either MDY or DMY format - even if they
ultimately want to only deal with "timestamp".  It looks like it does the
right thing 100% of the time (yes, they should use boundary values in test
regressions but...) and thus they give it no further thought; they most
certainly will not go looking for "SET dateformat" or contemplate breaking
their tidy String into two parts and dealing with each part separately.

They also haven't fully contemplated how PostgreSQL handles TimeZones or
that the input will be SILENTLY COERCED if it is invalid for the current
TimeZone (you used to do this with type-casting as well...).  They just say:
"It works, I'll use it".  The only thing, aside from a big/bold "WARNING"
right next to the function/description, that they will likely see is another
function that seems to do the same thing.  They will probably look through
and compare both functions closely in order to figure out why two functions
are being provided that seem to do the same thing.

Also, is this coercion noted in the documentation anywhere?  I looked in the
obvious locations (Data Type, Function, Appendix B).  There should probably
be something obvious, in the Data Type section, like:

"When a Time Stamp with time zone is created the 'effective' time zone is
determined and the input value is evaluated according to that time zone.
If, due to Daylight Savings Time changes, the indicated point-in-time does
not exist the time component is interpreted as if it were Standard Time and
then converted to DST (commonly +1 hours)  For example: '2007-12-30 00:30:00
ART' does not exist because '2007-12-30' is the day of the change to DST;
the attempt to create a timestamptz with this value will result in
'2007-12-30 01:30:00 ART' which then is stored as '2007-12-29 10:30:00 GMT'
(ART = GMT - 3).  Be aware that during DST-to-STD changeover there are no
'missing' times but there is no way to reliably specify whether you are
dealing with the first or the second occurrence of the time on that
particular day.  The TimeZone specification does not allow one to
specifically state '1:30AM during DST (1)' or '1:30AM during STD (2)'."

David J.