Re: Casting timestamp with time zone to varchar automatically

Is there any reason why there isn't a predefined cast to go from a
timestamp to a varchar? Is there a reason not to add one?
--
Jim C. Nasby, Database Consultant jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Aug 3, 2004, at 3:12 PM, Jim C. Nasby wrote:

> Is there any reason why there isn't a predefined cast to go from a
> timestamp to a varchar? Is there a reason not to add one?

to_char should do what you need. People often need a specific form of
timestamp if they need it to be a text value (rather than a native
timestamp data type). to_char provides lots of flexibility

Michael Glaesemann
grzm myrealbox com

Unfortunatly, that still doesn't really answer my question. I have a
generic function that accepts varchars, and I'd like to be able to feed
it timestamps without explicitly converting. That's why I'd like to know
if there is a specific reason there's no default timestamp -> varchar
cast.

On Tue, Aug 03, 2004 at 03:57:36PM +0900, Michael Glaesemann wrote:
>
> On Aug 3, 2004, at 3:12 PM, Jim C. Nasby wrote:
>
> >Is there any reason why there isn't a predefined cast to go from a
> >timestamp to a varchar? Is there a reason not to add one?
>
> to_char should do what you need. People often need a specific form of
> timestamp if they need it to be a text value (rather than a native
> timestamp data type). to_char provides lots of flexibility
>
>
> Michael Glaesemann
> grzm myrealbox com
>

--
Jim C. Nasby, Database Consultant jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> ... I'd like to know
> if there is a specific reason there's no default timestamp -> varchar
> cast.

There is an explicit cast from timestamp to varchar, at least in recent
releases:

regression=# select 'now'::timestamp::varchar;
varchar
---------------------------
2004-08-04 00:42:05.34875
(1 row)

Whether this should be invokable implicitly is somewhat of a theological
issue, but personally I'm agin it. My experience is that implicit
cross-type-category casts are Bad News All Around because they tend to
happen when you weren't expecting it, resulting in quite surprising
behavior. (An implicit cast from, say, timestamp to date is far less
dangerous.) You can find lots of discussion about related issues in
the list archives.

regards, tom lane

Idea :

Create a function with the same name as your function, but which takes a
timestamp as an argument, converts it to a string according to your
specifications, then calls your function which needs a string.

Postgresql will decide which function to call according to the types of
the arguments.

> Unfortunatly, that still doesn't really answer my question. I have a
> generic function that accepts varchars, and I'd like to be able to feed
> it timestamps without explicitly converting. That's why I'd like to know
> if there is a specific reason there's no default timestamp -> varchar
> cast.
>
> On Tue, Aug 03, 2004 at 03:57:36PM +0900, Michael Glaesemann wrote:
>>
>> On Aug 3, 2004, at 3:12 PM, Jim C. Nasby wrote:
>>
>> >Is there any reason why there isn't a predefined cast to go from a
>> >timestamp to a varchar? Is there a reason not to add one?
>>
>> to_char should do what you need. People often need a specific form of
>> timestamp if they need it to be a text value (rather than a native
>> timestamp data type). to_char provides lots of flexibility
>>
>>
>> Michael Glaesemann
>> grzm myrealbox com
>>
>

On Wed, Aug 04, 2004 at 12:48:57AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > ... I'd like to know
> > if there is a specific reason there's no default timestamp -> varchar
> > cast.
>
> There is an explicit cast from timestamp to varchar, at least in recent
> releases:
>
> regression=# select 'now'::timestamp::varchar;
> varchar
> ---------------------------
> 2004-08-04 00:42:05.34875
> (1 row)
>
> Whether this should be invokable implicitly is somewhat of a theological
> issue, but personally I'm agin it. My experience is that implicit
> cross-type-category casts are Bad News All Around because they tend to
> happen when you weren't expecting it, resulting in quite surprising
> behavior. (An implicit cast from, say, timestamp to date is far less
> dangerous.) You can find lots of discussion about related issues in
> the list archives.

Actually, my experience has been that the real issue isn't cross-type,
it's loss of information. For example, automatically casting a timestamp
to a date means you lose information; if this happens automatically you
can be in for a very unpleasant surprise (I was recently bit by this
when doing division of a double or a numeric and having it get converted
to an int because I was dividing by an int). I would argue that any
conversion where you won't lose information (ie: timestamp to text) is
OK.

In any case, turned out that wasn't my problem anyway...

> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> On Wed, Aug 04, 2004 at 12:48:57AM -0400, Tom Lane wrote:
>> Whether this should be invokable implicitly is somewhat of a theological
>> issue, but personally I'm agin it. My experience is that implicit
>> cross-type-category casts are Bad News All Around because they tend to
>> happen when you weren't expecting it, resulting in quite surprising
>> behavior. (An implicit cast from, say, timestamp to date is far less
>> dangerous.) You can find lots of discussion about related issues in
>> the list archives.

> Actually, my experience has been that the real issue isn't cross-type,
> it's loss of information. For example, automatically casting a timestamp
> to a date means you lose information; if this happens automatically you
> can be in for a very unpleasant surprise (I was recently bit by this
> when doing division of a double or a numeric and having it get converted
> to an int because I was dividing by an int).

Yeah, that is certainly bad, but cross-category is bad news for different
reasons. The sort of example I've seen come up again and again is that
someone compares a foo to a bar and files a bug report because the
comparison is behaving in a wacko fashion. On investigation it turns
out that there is no foo-to-bar comparison operator, but the system
decided it could implicitly cast both of them to text and do a textual
comparison. The behavior is perfectly sensible when seen as a text
comparison but made no sense in terms of the original datatypes'
semantics. Type casts within a category tend not to have such problems
because, for example, timestamps and dates sort compatibly in any case.

So I do not like implicit casts to text from non-textual datatypes, and
would like to get rid of the ones we have rather than introduce more.

I think we have already cleaned up all the cases where
information-losing casts were marked implicit, but we still have some
implicit casts to text :-(

regards, tom lane

On Wed, Aug 04, 2004 at 01:57:12PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> > On Wed, Aug 04, 2004 at 12:48:57AM -0400, Tom Lane wrote:
> >> Whether this should be invokable implicitly is somewhat of a theological
> >> issue, but personally I'm agin it. My experience is that implicit
> >> cross-type-category casts are Bad News All Around because they tend to
> >> happen when you weren't expecting it, resulting in quite surprising
> >> behavior. (An implicit cast from, say, timestamp to date is far less
> >> dangerous.) You can find lots of discussion about related issues in
> >> the list archives.
>
> > Actually, my experience has been that the real issue isn't cross-type,
> > it's loss of information. For example, automatically casting a timestamp
> > to a date means you lose information; if this happens automatically you
> > can be in for a very unpleasant surprise (I was recently bit by this
> > when doing division of a double or a numeric and having it get converted
> > to an int because I was dividing by an int).
>
> Yeah, that is certainly bad, but cross-category is bad news for different
> reasons. The sort of example I've seen come up again and again is that
> someone compares a foo to a bar and files a bug report because the
> comparison is behaving in a wacko fashion. On investigation it turns
> out that there is no foo-to-bar comparison operator, but the system
> decided it could implicitly cast both of them to text and do a textual
> comparison. The behavior is perfectly sensible when seen as a text
> comparison but made no sense in terms of the original datatypes'
> semantics. Type casts within a category tend not to have such problems
> because, for example, timestamps and dates sort compatibly in any case.
>
> So I do not like implicit casts to text from non-textual datatypes, and
> would like to get rid of the ones we have rather than introduce more.
>
> I think we have already cleaned up all the cases where
> information-losing casts were marked implicit, but we still have some
> implicit casts to text :-(

Would it maybe make more sense to change things so that implicit casts
aren't used for comparisons? Maybe instead of the simple 3-tier system
of what casts can do there should be a bitmap that specifies if a cast
can happen implitily for function calls, column assignments, and/or
comparisons.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"