>> We have date, timestamp, and time collations...

> Collations for date, timestamp and time?!?!? Now you have my
> mind "swimming"...
>
> How can a collation have "intelligence" to parse the time
> zone part (i.e. +10:00) of the string and use this to apply
> logic to the other values/parts of the string?
>
> My understanding of collations where that they defined a
> mapping/sequencing of characters... not to build/apply a
> "formula".

Firebird collations are capable of so much more, I did not
understand how much until I started implementing this. One
of the oldest examples I found (for Interbase) was a "title
collation" that makes strings like this:

"The Wind in the Willows" and "A Tale of Two Cities"
sort/index like:
"Wind in the Willows, The" and "Tale of Two Cities, A"

What I discovered was that the issues of character mappings and
so on are internal to the collation, they are NOT part of the
interface from Firebird. Firebird just asks for keys values
relevant to some input. If you wanted you could accept English
input and return keys based on a translation Klingon. ;-)


With the timestamp collation installed I can create a table like:

CREATE TABLE DTZ_STR_DT_TEST_TBL (
IDENT VARCHAR(20) CHARACTER SET ASCII,
DTVALUE VARCHAR(100) CHARACTER SET ASCII COLLATE DATETIME_STRING
);

Fill it with values like:
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT1002', '2009-04-05T12:34+01:00');
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT1003', '2009-04-05T11:34+00:00');
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT1004', '2009-04-05T11:34+00');
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT1005', '2009-04-05T24:00Z');
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT1006', '2009-04-05T01:02:60Z');
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT1009', '2009-04-05T11:00Z');
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT2000', '9999-12-31T23:59:60.999999Z');
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT2010', '-9999-12-31T23:59:60.999999Z');
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT2030', '-0001-01-01T00:00:00.000000Z');
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT2096', '0001-02-01T00:00:00.000000Z');
INSERT INTO DTZ_STR_DT_TEST_TBL (IDENT, DTVALUE) VALUES ('DT2097', '0002-01-01T00:00:00.000000Z');

(The above are copied from my testing code, so they really are
acceptable to the collation.)

Firebird will automatically sort and index those input strings
as if they were timestamp values normalised to UTC. I can
search for values BETWEEN and > and < and so on. (But to do
date manipulations, adding days or months etc, would require
UDFs - the collation is not involved in those calculations.)


I am able to send you the collation (source and test code)
privately if you want, I previously sent the decimal collation
to Adriano for comment.

--
Geoff Worboys
Telesis Computing