Auto Type conversion

Can I assume that this as well as the text->varchar will be fixed in
6.4?

text->varchar is likely to be addressed (as well as other string issues
such as ensuring correct maximum length in target columns).

adserver=> select NOW()::DATETIME::TIMESTAMP;
ERROR: function datetime_stamp(datetime) does not exist

Hmm. I wrote most of the routines you might want to go _to_ datetime,
but did not fully populate the functions to go _away_ from datetime. For
timestamp in particular, I didn't want to spend the time, since I was
planning on replacing timestamp with datetime sometime soon.

However, I haven't taken that step yet because:

1) I think that the current datetime implementation makes more sense
than the SQL92 specification for timestamp (of course, I wrote it so I'm
a bit biased :)

2) imho implementing _full_ SQL92 timestamp behavior is a waste of time
(damaged functionality wrt datetime and bizarre syntax, usage, and
behavior, among other reasons).

3) others may have a strong opinion that a _full_ SQL92 timestamp is
important (I would hope that they have a real need for it, rather than
it being a "well, it should" argument because afaik no one actually uses
the most arcane SQL92 features of timestamp, since they make little
sense).

4) I'm not likely to be willing to support a damaged form of
datetime/timestamp at the expense of a full-featured datetime, but the
project might decide to head that direction.

My feeling is that the SQL92 form of timestamp is a mish-mash of
requirements and features to accomodate existing database products.
Starting from scratch, no one would have come close to the SQL92
standard for this. The datetime type is more in keeping with how date
and time actually behave, and is what timestamp should be.

Anyway, a discussion of this may be in order. Anyone??

- Tom

Can I assume that this as well as the text->varchar will be fixed in
6.4?

text->varchar is likely to be addressed (as well as other string
issues
such as ensuring correct maximum length in target columns).

adserver=> select NOW()::DATETIME::TIMESTAMP;
ERROR: function datetime_stamp(datetime) does not exist

Hmm. I wrote most of the routines you might want to go _to_ datetime,
but did not fully populate the functions to go _away_ from datetime.
For
timestamp in particular, I didn't want to spend the time, since I was
planning on replacing timestamp with datetime sometime soon.

Is there an easy way to format the output for the DATETIME datatype on a
per query basis. I really like the DATETIME functionality, but there
are times when the TIMESTAMP output format would be more convenient.

However, I haven't taken that step yet because:

1) I think that the current datetime implementation makes more sense
than the SQL92 specification for timestamp (of course, I wrote it so
I'm
a bit biased :)

2) imho implementing _full_ SQL92 timestamp behavior is a waste of
time
(damaged functionality wrt datetime and bizarre syntax, usage, and
behavior, among other reasons).

What is the bizarre/archaic functionality? I would vote for
compatibility unless it introduces some huge programming concerns.

3) others may have a strong opinion that a _full_ SQL92 timestamp is
important (I would hope that they have a real need for it, rather than
it being a "well, it should" argument because afaik no one actually
uses
the most arcane SQL92 features of timestamp, since they make little
sense).

Well my argument is a 'well, it should'. But I'm willing to help, if at
all possible, to reach the idea I express.

4) I'm not likely to be willing to support a damaged form of
datetime/timestamp at the expense of a full-featured datetime, but the
project might decide to head that direction.

Why not implement timestamp as a different type interface to the same
implementation? Unless the SQL92 standard conflicts directly with the
current implementation of DATETIME, in which case I suggest leaving them
separate and implement good CAST between them.

My feeling is that the SQL92 form of timestamp is a mish-mash of
requirements and features to accomodate existing database products.
Starting from scratch, no one would have come close to the SQL92
standard for this. The datetime type is more in keeping with how date
and time actually behave, and is what timestamp should be.

Well, if we implement the standard as it stands as TIMESTAMP and leave
the current DATETIME functionality and implement auto-typecasting
between the two then we get the best of both worlds, as long as it
doesn't take 500 man-hours to implement.

Anyway, a discussion of this may be in order. Anyone??

Ooh, Ooh, me, me.

- Tom

-DEJ

I fully support Tom's opinion on the SQL92 timestamp specification.
I know that working with datetime with commercial DBMS's is a major pain,
because the syntax varies with context.
e.g. select versus create table default value syntax.
I therefore support a consistent datetime implementation that might
not conform to SQL92 100%, even though I traditionally tend to say
"stick to the standard".

Andreas

Can I assume that this as well as the text->varchar will be fixed in
6.4?

text->varchar is likely to be addressed (as well as other string issues
such as ensuring correct maximum length in target columns).

adserver=> select NOW()::DATETIME::TIMESTAMP;
ERROR: function datetime_stamp(datetime) does not exist

Hmm. I wrote most of the routines you might want to go _to_ datetime,
but did not fully populate the functions to go _away_ from datetime. For
timestamp in particular, I didn't want to spend the time, since I was
planning on replacing timestamp with datetime sometime soon.

However, I haven't taken that step yet because:

1) I think that the current datetime implementation makes more sense
than the SQL92 specification for timestamp (of course, I wrote it so I'm
a bit biased :)

2) imho implementing _full_ SQL92 timestamp behavior is a waste of time
(damaged functionality wrt datetime and bizarre syntax, usage, and
behavior, among other reasons).

3) others may have a strong opinion that a _full_ SQL92 timestamp is
important (I would hope that they have a real need for it, rather than
it being a "well, it should" argument because afaik no one actually uses
the most arcane SQL92 features of timestamp, since they make little
sense).

4) I'm not likely to be willing to support a damaged form of
datetime/timestamp at the expense of a full-featured datetime, but the
project might decide to head that direction.

My feeling is that the SQL92 form of timestamp is a mish-mash of
requirements and features to accomodate existing database products.
Starting from scratch, no one would have come close to the SQL92
standard for this. The datetime type is more in keeping with how date
and time actually behave, and is what timestamp should be.

Anyway, a discussion of this may be in order. Anyone??

- Tom