Casting timestamp with time zone to varchar automatically

[Jim C. Nasby]

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 ji*@nasby.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?"

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Jim C. Nasby]

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 ji*@nasby.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?"

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

"Jim C. Nasby" <de*****@decibel.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

---------------------------(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

[Pierre-Frédéric Caillaud]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

"Jim C. Nasby" <de*****@decibel.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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Jim C. Nasby]

On Wed, Aug 04, 2004 at 12:48:57AM -0400, Tom Lane wrote:

"Jim C. Nasby" <de*****@decibel.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 de*****@decibel.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?"

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Jim C. Nasby]

On Wed, Aug 04, 2004 at 01:57:12PM -0400, Tom Lane wrote:

"Jim C. Nasby" <de*****@decibel.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 de*****@decibel.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?"

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)